Insert rows with defined text for all months thru VBA

[RENU1973]

Good afternoon my Eileen Loungers,

I am posting my first query and hope if any mistake please don't mind and excuse me for that.

Pl.refer my attach file and want the solution thru VBA codes steps.Can anybody help me about this amazing aspects?

In my file,first sheet is 'Datas' and desire to view the sheet as 'Display'.

When I put my text in cell no.'A1',then,it should automate insert in col.B and according to ascending order in all the months.

[HansV]

Welcome to Eileen's Lounge!

You could use the following code in the worksheet module:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Long
    Dim f As Boolean
    On Error GoTo ErrHandler
    If Not Intersect(Cells(1, 1), Target) Is Nothing Then
        If Cells(1, 1).Value <> "" Then
            Application.ScreenUpdating = False
            Application.EnableEvents = False
            r = 4
            f = True
            Do
                If Cells(r, 1).Value <> Cells(r + 1, 1).Value Then
                    Rows(r).Copy
                    Rows(r).Insert
                    If f Then
                        Cells(r, 3).Value = 0
                        f = False
                    End If
                    Range(Cells(r, 4), Cells(r, 7)).Value = 0
                    Range(Cells(r, 10), Cells(r, 16)).Value = 0
                    Cells(r, 2).Value = Cells(1, 1).Value
                    r = r + 1
                End If
                r = r + 1
            Loop Until Cells(r, 1).Value = ""
            Range(Cells(3, 1), Cells(r - 1, 16)).Sort _
                Key1:=Cells(3, 1), Key2:=Cells(3, 2), Header:=xlYes
        End If
    End If
ExitHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub

See the attached version - note that it is now a .xlsm workbook, for .xlsx workbooks can't contain VBA code.
If you change the value of A1 in the Datas sheet, rows should be inserted automatically.
Please test carefully - I'm not sure that I understood your requirements correctly.

Datas.xlsm

[Rudi]

Here is another version:

On the View Ribbon, click on the Macros button and choose View Macros.
Select FormatData and Run

The code should sort the list and format the letters in the B column based on the letter in Cell A1

Datas.xlsm

[RENU1973]

Hans sir,there is no macro is appearing in your attach file.Please look into the matter.Perhaps,I may be wrong but request you to please have a look attach file.
I want insert the rows in col.B of month wise,according to ascending order, when I put a text in cell A1 of the datas sheet.

Thanks and Regards

Renu

[RENU1973]

Rudi sir,this is not the solution.Actually,I want to insert the rows when,I put a text in A1 cell of Datas sheet and not the highlight cell.Request you to please see the macros codes in this regards.I have shown my desired result in my 'Display 'sheet.

[Rudi]

Hans sir,there is no macro is appearing in your attach file.Please look into the matter.Perhaps,I may be wrong but request you to please have a look attach file.
I want insert the rows in col.B of month wise,according to ascending order, when I put a text in cell A1 of the datas sheet.

Thanks and Regards

Renu

The macro is under a special module. (If you want to see the macro, right click on the sheet tab called Datas, and choose View Code).
You don't need to run the macro in Hans example. All you need to do is type the letter in cell A1 and the macro runs automatically.

[Rudi]

Rudi sir,this is not the solution.Actually,I want to insert the rows when,I put a text in A1 cell of Datas sheet and not the highlight cell.Request you to please see the macros codes in this regards.I have shown my desired result in my 'Display 'sheet.

It seems I completely misunderstood your request. Sorry.

[RENU1973]

Hans sir,I am really very sorry and could not understand proper your macro.Now,I have understand with the help of Rudi's explanation.
Perfet macro as my dream required.Thanks for your efforts and guidance.

Also thanks to Rudi's sir for your explanation to Hans;s Sir macro.