You can cycle through all the sheets in a workbook like this.
VB:Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ' do whatever with the worksheet ws here Next ws
I am creating an excel template where a user will insert varying sheets everytime the template is used ( i have that set up in VBA). Then I need to insert a list for the column headings to be placed on all of the sheets. I can insert the list on the first sheet but don't know how to select the other sheets because every time the template is used, the number of sheets could be different. Is there a way for a macro to select the sheets and fill across sheets. I am thinking perhaps an array, but I don't know how to set it up. Any help would be a great help and thank you. (I did a search already and couldn't find my answer)
To select all WorkSheets use
To cycle through and edit as needs be try:VB:Sub SelectAll() Worksheets.Select End Sub
VB:Public Sub mySelectAllSheets() Dim i As Long For i = 1 To myCount Step 1 ' do something here Next i Exit Sub End SubThe code needs the function to count the sheets and work from there as You say never sure how many thisVB:Private Function myCount() Dim x As Long x = ThisWorkbook.Worksheets.Count myCount = x Exit Function End Function
Another way to count the sheets:
I just use functions this way often these can be added into the code so my above work would be: (You choose)VB:Sub jj() Debug.Print ThisWorkbook.Worksheets.Count End Sub
jiukVB:Public Sub mySelectAllSheets2() Dim i As Long For i = 1 To ThisWorkbook.Worksheets.Count Step 1 ' do something here Next i Exit Sub End Sub
Last edited by Jack in the UK; January 15th, 2006 at 06:35.
There are currently 1 users browsing this thread. (0 members and 1 guests)