for each sheet in workbook

  • Thread starter Thread starter J.W. Aldridge
  • Start date Start date
J

J.W. Aldridge

how to perform macro on each sheet in workbook.
(except first worksheet = "master data")

I've tried the subs below but only worked on one sheet.

Public Sub DoToAll()
Dim ws As Worksheet
For Each ws In Worksheets
Application.Run "filldown"
Next
End Sub

Sub loopthroughanddo()
x = Sheets("master data").Index
For Each sh In ThisWorkbook.Sheets
If sh.Index > x Then
Application.Run "filldown"
End If
Next
End Sub
 
A lot depends on what filldown does....it needs to know what the current ws
object is, so pass it to it. This example code copies cell A1 of every
sheet and fills it down column A to match column B.


Public Sub DoToAll()
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name <> "master data" filldown ws
Next ws
End Sub

Sub filldown(shtWS As Worksheet)
shtWS.Range("A1").Copy shtWS.Range("A2:A" & _
shtWS.Cells(Rows.Count, 2).End(xlUp).Row)
End Sub


HTH,
Bernie
MS Excel MVP
 
Back
Top