sheetselection.
Posted by Luke on November 07, 2001 8:42 AM
When copying sheets from one workbook to another in VBA The only suggested way I can find is by using "sheets(array("..","..","..")). This is somewhat irritating because I have to define each worksheet. I don't previously know how many sheets there are in the new workbook nor always the exact names of the sheets. What I really want to do is select all sheets except one (the name of which I do know) but it could be anything from 2 through 15 sheets. Can anyone help me. Of course I could write a routine to check for the existance of each sheet but this if time consuming and I am sure there must be a simpler way. Also, I could select all sheet and deselect the any I don't want, If I knew how to deselect.
Luke
Posted by Hodge on November 07, 2001 10:32 AM
This code is crude, but should give you a start. It will copy each sheet individually, skipping the one you choose. Let me know if it helps.
SheetName = InputBox("What Sheet would you like to exclude?", "Sheet Name")
LastSheet = Sheets.Count
Do Until SheetNumber > LastSheet
If Not Worksheets(SheetNumber).Name = SheetName Then
Worksheets(SheetName).Select
' Code To Copy And Paste Sheet
End If
SheetNumber = SheetNumber + 1
Loop
End
Posted by Luke on November 07, 2001 12:59 PM
I am sure this would work, but this routine still does one sheet at a time. It just amazes me that, while cell and range like functions allow for specified amounds of cells to be selected, the sheets function can only select all sheets ('sheets.select') or individual sheets, if wanted bundled by an array but not something like 'sheets(3:8).select'
Luke
