• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Recorded Macro - Need help in modification

Rajesh S

Member
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.
 

Attachments

  • Testing.xlsm
    302.2 KB · Views: 5
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.
 
Hi Rajesh S,
try this code:
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
 
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
 
Hi Rajesh S,
Thank you for your feedback, here's the code to loop through all worksheets:
Code:
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
 
Back
Top