Apply code the same code to multiple worksheets in the same workbook

Question

I'm pretty new to VBA and I need a piece of code to apply in the same way to some work sheets in my workbook.

The name of the worksheets I need the code to be applied are as follows:

Analysis Flow Racking % Refill

Analysis Flow Racking 1 Picks

Analysis Line Cupboards %Refill

Analysis Line Cupboards by Pick

Analysis PFB

Analysis Cupboards % Refill

Analysis Cupboards by Picks

Analysis Flow Racking 2 Picks

The code is found below: Any help that you can provide will be much appreciated. Many thanks

 Sub AddCheckBox()

 Application.ScreenUpdating = False

 Dim cell As Range

 DelCheckBox  'Do the delete macro
'or delete all checkboxes in the worksheet
' ActiveSheet.CheckBoxes.Delete
ActiveWindow.View = xlNormalView
lastRow = ActiveSheet.Range("B" & ActiveSheet.Rows.Count).End(xlUp).Row

For Each cell In Range("A5:A" & lastRow)
With ActiveSheet.CheckBoxes.Add(cell.Left, _
cell.Top, cell.Width, cell.Height)
.LinkedCell = cell.Offset(, 8).Address(External:=True)
'.Interior.ColorIndex = 37   'or  xlNone or xlAutomatic
.Caption = ""
'.Border.Weight = xlThin
End With
Next

 With Range("A5:A" & lastRow)
.Rows.RowHeight = 15
Worksheets("Analysis Flow Racking % Refill ").CheckBoxes.Select
Selection.ShapeRange.Align msoAlignCenters, msoFalse
Selection.ShapeRange.IncrementLeft 50
Range("A10000").Select


End With
ActiveWindow.View = xlPageLayoutView
Application.ScreenUpdating = True
End Sub

FreeMan · Accepted Answer · 2015-06-24 14:05:16Z

0

Give this a shot:

Sub Driver()
  Dim ws as Worksheet
  'Since your worksheet names are somewhat variable, 
  'I'd suggest passing an array of known names from a driver routine to your worker routine.
  For each ws in Worksheets(Array("Analysis Flow Racking % Refill", _
                                  "Analysis Flow Racking 1 Picks", _
                                  "Analysis Line Cupboards %Refill"))
                                  'continue with the rest of your worksheets here...
    AddCheckBox ws
  Next
  'If however, you're processing all the worksheets in the workbook, then this will be easier
  For each ws in ActiveWorkbook.Sheets
    AddCheckBox ws
  Next
End Sub

You will now need to modify your AddCheckBox() routine to accept the worksheet as a parameter:

Sub AddCheckBox(ByVal TheSheet as Worksheet)

  Application.ScreenUpdating = False
  DelCheckBox  'Do the delete macro
  'or delete all checkboxes in the worksheet
  ' ActiveSheet.CheckBoxes.Delete
  ActiveWindow.View = xlNormalView
  Dim LastRow as integer   'always declare your variables!
  lastRow = ActiveSheet.Range("B" & ActiveSheet.Rows.Count).End(xlUp).Row

  Dim cell As Range
  For Each cell In TheSheet.Range("A5:A" & lastRow)
    With TheSheet.CheckBoxes.Add(cell.Left, _
                                 cell.Top, cell.Width, cell.Height)
      .LinkedCell = cell.Offset(, 8).Address(External:=True)
      '.Interior.ColorIndex = 37   'or  xlNone or xlAutomatic
      .Caption = ""
      '.Border.Weight = xlThin
    End With
  Next

  'Note: removed WITH from here - it only effected 1 row and was confusing    
  TheSheet.Range("A5:A" & lastRow).Rows.RowHeight = 15
  ''''''''''''''''''''''''''''''
  TheSheet.CheckBoxes.Select
  Selection.ShapeRange.Align msoAlignCenters, msoFalse
  Selection.ShapeRange.IncrementLeft 50
  Range("A10000").Select
  '
  'I believe that this code can be replaced with this:
  TheSheet.Checkboxes.ShapeRange.Align msoAlignCenters msoFalse
  TheSheet.Checkboxes.ShapeRange.IncrementLeft 50
  ''''''''''''''''''''''''''''''
  ActiveWindow.View = xlPageLayoutView
  Application.ScreenUpdating = True
End Sub

edited Jun 24, 2015 at 14:05

answered Jun 24, 2015 at 13:47

FreeMan

5,7092 gold badges32 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Gabriel Over a year ago

Thank you very much... That seems to work fine to add the check boxes but I forgot to add the code to delete them. Please find below: Sub DelCheckBoxAnalysisCupboardsbyPicks() Dim cell As Range LastRow = ActiveSheet.Range("B" & ActiveSheet.Rows.Count).End(xlUp).Row For Each cell In Range("A5:A" & LastRow) Worksheets("Analysis Cupboards by Picks").CheckBoxes.Delete Next For Each cell In Range("I5:I" & LastRow) Range("I5:I" & LastRow).Clear Next End Sub

Gabriel Over a year ago

That one is applying the code to a "specific worksheet but I need that it also applies to the same worksheets that I have added the check boxes previously.

FreeMan Over a year ago

Make the same change to the signature of DelCheckBox() by adding the parameter, and work with the known worksheet, just like I did with the AddCheckBox() routine.

FreeMan Over a year ago

Glad to hear it. An upvote and clicking the check mark will help others find a solution if they have the same problem.

Collectives™ on Stack Overflow

Apply code the same code to multiple worksheets in the same workbook

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related