Recorded Macro - Need help in modification

[Rajesh S]

Hi All, I have recorded a macro for inserting a line in A1 for updating the sheet name for all the rows. However, the constraint here is that the output is based on the final row selected (say 5000). Hence though the rows varies on the data in sheets, formula copies the information in a static place which results in deletion / addtion to the remaining fields. Also, format is not getting copied inspite of having recorded the format copier too.

Can anyone support me in this. Given below the macro which I use currently.

 

[AlanSidman]

I'm not understanding your explanation. Please restate specifically what you want to happen in simple business terms. If necessary show us an example of what the final out put should look like in your sample workbook.

 

[Keetoowah]

Hi Rajesh S,
try this code:

Sub test2()

    Dim LastRow As Integer
    Dim Ws     As Worksheet
    Dim MyRange As Range

    Set Ws = ActiveSheet
    LastRow = Ws.Range("A" & Rows.Count).End(xlUp).Row
        
    Ws.Range("A:A").Insert Shift:=xlToRight, Copyorigin:=xlFormatFromRightOrBelow
    
    Ws.Range("A1").Value = "Month"
    
    Set MyRange = Ws.Range("A2:A" & LastRow)
    
    With MyRange
        .Value = Ws.Name
    End With
    
End Sub

 

[Rajesh S]

Wow. That's works perfect on the selected sheet. Is it possible to repeat this action for all sheets using macro? If yes, please help me with that too

 

[Rajesh S]

Thanks for your response. Below query works perfect. Thanks anyway

 

[Keetoowah]

Hi Rajesh S,
Thank you for your feedback, here's the code to loop through all worksheets:

Sub test3()

    Dim LastRow As Integer
    Dim Ws     As Worksheet
    Dim MyRange As Range

    Application.ScreenUpdating = False
    
    For Each Ws In Worksheets
        
        'Set Ws = ActiveSheet
        LastRow = Ws.Range("A" & Rows.Count).End(xlUp).Row
        
        Ws.Range("A:A").Insert Shift:=xlToRight, Copyorigin:=xlFormatFromRightOrBelow
        
        Ws.Range("A1").Value = "Month"
        
        Set MyRange = Ws.Range("A2:A" & LastRow)
        
        With MyRange
            .Value = Ws.Name
        End With
        
    Next Ws
    
    Application.ScreenUpdating = True
        
End Sub

 

[Rajesh S]

This really works in all sheets..Happy about the output Kudos