Announcement

Collapse
No announcement yet.

Select all sheets in a workbook

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Select all sheets in a workbook

    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)

  • #2
    Re: Select all sheets in a workbook

    You can cycle through all the sheets in a workbook like this.
    Code:
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        ' do whatever with the worksheet ws here
    Next ws
    Boo!

    Comment


    • #3
      Re: Select all sheets in a workbook

      To select all WorkSheets use

      Sub SelectAll()
      Worksheets.Select
      End Sub

      To cycle through and edit as needs be try:

      Public Sub mySelectAllSheets()
      Dim i As Long
      For i = 1 To myCount Step 1
      ' do something here
      Next i
      Exit Sub
      End Sub

      Private Function myCount()
      Dim x As Long
      x = ThisWorkbook.Worksheets.Count
      myCount = x
      Exit Function
      End Function

      The code needs the function to count the sheets and work from there as You say never sure how many this

      Another way to count the sheets:

      Sub jj()
      Debug.Print ThisWorkbook.Worksheets.Count
      End Sub

      I just use functions this way often these can be added into the code so my above work would be: (You choose)

      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

      jiuk
      Last edited by Jack in the UK; January 15th, 2006, 07:35.

      Comment

      Working...
      X