CODEWITHSUNDEEP
excelvba
Radosław Sokolowski
Radosław Sokolowski

Reputation: 45

How to get my code to run at any worksheet

I am new to VBA so please be patient as this all is new to me :) Below code works great. It will unmerge cells if merged through column A of active worksheet and fill with duplicates. It's placed in module 17 for worksheet "Paste DATA". I would like to run this code on any worksheet if need. For example if for data inside in worksheet " Paste Additional Data " need to use this code i could use it by changing target worksheet name inside code. Same with range - change range by adjusting manually inside code.

Sub Unmerge_Cells_test()

Application.ScreenUpdating = False
    Lr = Cells(Rows.Count, 1).End(xlUp).Row
        Cells(Lr, 1).Select
            Lr = Lr + Selection.Cells.Count - 1
            
Range("A2:A" & Lr).UnMerge
    For R = 3 To Lr
        If Cells(R, 1) = "" Then Cells(R, 1) = Cells(R - 1, 1)
Next R

End Sub

Upvotes: 0

Views: 49

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

Try the next code, please. Copy it in a standard module:

Sub Unmerge_Cells_test()
 'Please, cultivate the habit to declare all used variables:
 Dim LR As Long, r As Long, sh As Worksheet

 'here you set the "any sheet" you need:
 Set sh = Worksheets("TheSheetYouNeed") 'or ActiveSheet to test what it does now...

 Application.ScreenUpdating = False
    LR = sh.cells(rows.count, 1).End(xlUp).row
            
    sh.Range("A2:A" & LR).UnMerge
    For r = 3 To LR
        If sh.cells(r, 1) = "" Then sh.cells(r, 1) = sh.cells(r - 1, 1)
    Next r
 Application.ScreenUpdating = True
End Sub

Upvotes: 1

Related Questions