Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Selecting Multiple Sheets in VBA
Hi folks,
Is there a way of selecting a variable number of worksheets in VB so that I can apply a simultaneous fix. I can't do the sheets 1 at a time as the fix includes inserting some columns and the references will go screwy. It is possible to do manually so should be possible in VB. The equivalent of holding the control key and clicking on the tabs you want. I would be looking at 'selecting' all the tabs between "START" and "END" (these will have user defined names so cannot be specified in the macro) and then 2 additional pre-defined tabs. Any ideas anyone? Giz |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Selecting Multiple Sheets in VBA
If you can do it manually, why can't you record a macro while you do
it? Then you will be able to see the syntax and should be able to merge the recorded macro (or parts of it) into your other macro. You could insert two sheets "first_temp" and "last_temp", position them at the appropriate places, then make use of these. Once you have finished, "first_temp" and "last_temp" could be deleted. Hope this helps. Pete |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Selecting Multiple Sheets in VBA
Sub JustTheOnesIWant()
'Selects all sheets between two designated sheets. 'Jim Cone - San Francisco, USA - May 2006 Dim objShts As Excel.Sheets Dim varArry As Variant Dim lngStart As Long Dim lngEnd As Long Dim N As Long lngStart = Worksheets("SheetStart").Index lngEnd = Worksheets("SheetFinish").Index If lngEnd < lngStart Then MsgBox "Please reorder sheets. " Exit Sub End If ReDim varArry(lngStart To lngEnd) For N = lngStart To lngEnd varArry(N) = Worksheets(N).Name Next Set objShts = Sheets(varArry) objShts.Select Set objShts = Nothing End Sub ------------ Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Gizmo63" wrote in message Hi folks, Is there a way of selecting a variable number of worksheets in VB so that I can apply a simultaneous fix. I can't do the sheets 1 at a time as the fix includes inserting some columns and the references will go screwy. It is possible to do manually so should be possible in VB. The equivalent of holding the control key and clicking on the tabs you want. I would be looking at 'selecting' all the tabs between "START" and "END" (these will have user defined names so cannot be specified in the macro) and then 2 additional pre-defined tabs. Any ideas anyone? Giz |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Selecting Multiple Sheets in VBA
Hi Pete,
Won't work for this. The macro builds an array statement that picks up the tab names as you click then and use the defined names in the macro. There is no variabilty in it. Giz "Pete_UK" wrote: If you can do it manually, why can't you record a macro while you do it? Then you will be able to see the syntax and should be able to merge the recorded macro (or parts of it) into your other macro. You could insert two sheets "first_temp" and "last_temp", position them at the appropriate places, then make use of these. Once you have finished, "first_temp" and "last_temp" could be deleted. Hope this helps. Pete |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Selecting Multiple Sheets in VBA
Thanks Jim.
I was pretty close to the solution, heading on the right lines of cycling and saving the names but didn't know the "objShts" stuff to make it work. A few tweaks to bypass the tab indexes and sorted. Cheers Giz "Jim Cone" wrote: Sub JustTheOnesIWant() 'Selects all sheets between two designated sheets. 'Jim Cone - San Francisco, USA - May 2006 Dim objShts As Excel.Sheets Dim varArry As Variant Dim lngStart As Long Dim lngEnd As Long Dim N As Long lngStart = Worksheets("SheetStart").Index lngEnd = Worksheets("SheetFinish").Index If lngEnd < lngStart Then MsgBox "Please reorder sheets. " Exit Sub End If ReDim varArry(lngStart To lngEnd) For N = lngStart To lngEnd varArry(N) = Worksheets(N).Name Next Set objShts = Sheets(varArry) objShts.Select Set objShts = Nothing End Sub ------------ Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Gizmo63" wrote in message Hi folks, Is there a way of selecting a variable number of worksheets in VB so that I can apply a simultaneous fix. I can't do the sheets 1 at a time as the fix includes inserting some columns and the references will go screwy. It is possible to do manually so should be possible in VB. The equivalent of holding the control key and clicking on the tabs you want. I would be looking at 'selecting' all the tabs between "START" and "END" (these will have user defined names so cannot be specified in the macro) and then 2 additional pre-defined tabs. Any ideas anyone? Giz |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
selecting multiple sheet tabs and open another workbook | Excel Discussion (Misc queries) | |||
Autofilter Lists across Multiple Sheets, Maintain Correct Referenc | Excel Worksheet Functions | |||
Select Multiple sheets | Excel Discussion (Misc queries) | |||
Calculations crossing multiple sheets | Excel Discussion (Misc queries) | |||
linking multiple sheets to a summary sheet | Excel Discussion (Misc queries) |