Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Select all sheets in a workbook

  1. #1
    Join Date
    15th January 2006
    Posts
    1

    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)

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    14th July 2004
    Posts
    10,541

    Re: Select all sheets in a workbook

    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    26th January 2003
    Location
    UK
    Posts
    4,684

    Re: Select all sheets in a workbook

    To select all WorkSheets use
    VB:
    Sub SelectAll() 
        Worksheets.Select 
    End Sub 
    
    
    To cycle through and edit as needs be try:
    VB:
    Public Sub mySelectAllSheets() 
        Dim i As Long 
        For i = 1 To myCount Step 1 
             ' do something here
        Next i 
        Exit Sub 
    End Sub 
    
    
    VB:
    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:
    VB:
    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)
    VB:
    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 at 07:35.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Select All Sheets Except First
    By Timbo in forum EXCEL HELP
    Replies: 10
    Last Post: September 18th, 2006, 21:28
  2. Loop to select different sheets
    By georgebrough in forum EXCEL HELP
    Replies: 6
    Last Post: May 4th, 2006, 17:54
  3. Replies: 7
    Last Post: September 12th, 2005, 21:38
  4. Sheets Select??
    By labtrack in forum EXCEL HELP
    Replies: 10
    Last Post: July 13th, 2004, 05:22
  5. Replies: 4
    Last Post: August 15th, 2003, 10:41

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno