Running Macros on multiple worksheets

**shivrklfc** · 10-30-2012, 10:06 AM

Hi,

i have a macro that is currently performing actions on one worksheet. I have copied this worksheet and named it something slightly different. I now want to edit this macro so that it runs it also performs the same actions on the copied sheet. There are many other worksheets in my workbook but only want this macro to be performed on sheets "LS04" and "LS05" (the names i have assigned the worksheets)

The code i have running on the the original worksheet is


Sub Addworkstage()
'
' Addworkstage Macro
'ALLOWS USER TO ADD ADDITIONAL WORK STAGE TO BUILD UP COST ESTIMATE

    Application.ScreenUpdating = False
    LS04.Unprotect Password:=LogCode
    Rows("32:49").Select
    Selection.EntireRow.Hidden = False
    Rows("33:48").Select
    Selection.Copy
    Range("A65536").End(xlUp).Offset(0, 0).Select
    Selection.Insert Shift:=xlDown
    ActiveWindow.SmallScroll Down:=18
    Application.CutCopyMode = False
    Rows("33:48").Select
    Selection.EntireRow.Hidden = True
    Range("A50").Select
    
    If AdminAccess = True Then
    LS04.Unprotect Password:=LogCode
    Else
        
    LS04.Protect Password:=LogCode
    Application.ScreenUpdating = True
    
    End If
End Sub

Thanks in advance for your help

**JBeaucaire** · 10-30-2012, 10:24 AM

You could reset the macro to do it's magic on the "activesheet" instead of any specific named sheet. Assuming your macros are getting called in a controlled manner, that should work, no? If you make the macros PRIVATE they won't show in the Macro list so that should suppress accidental manual activation, then you can put buttons on the sheets where you want the macro to work and call it that way.

Private Sub AddWorkstage()
'
' Addworkstage Macro
'ALLOWS USER TO ADD ADDITIONAL WORK STAGE TO BUILD UP COST ESTIMATE

Application.ScreenUpdating = False
With ActiveSheet
    .Unprotect Password:=LogCode
    .Rows("32:49").EntireRow.Hidden = False
    .Rows("33:48").Copy
    With .Range("A" & .Rows.Count).End(xlUp)
        .Insert Shift:=xlDown
        .Select
    End With
    .Rows("33:48").EntireRow.Hidden = True

    If AdminAccess = False Then .Protect Password:=LogCode
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

**shivrklfc** · 10-30-2012, 10:38 AM

hi JBeaucaire

Many thanks for your quick response, well the sheet "LS04" is currently in view for the user to input information, "LS05" is a copy of LS04 and is hidden from view, therefore when the user inputs information it is linked with LS05 and copies information across and is not meant to be seen by the user. The issue is that the user can add a set of cells (using the above macro) in LS04 and input extra information and therefore this will not be captured into the copied sheet (LS05). This is why i want the same actions performed on both sheets. Essentially i want any action on LS04 mirrored on LS05.

Thanks