Macro to repeat the same tasks over 180 tabs.

[Art MacNeil]

Hello all,

I have a spreadsheet with about 180 tabs. I've done a Macro to complete a
few tasks on the 1st Tab. I now need to complete those tasks on most of the
remaining tabs. Is there a way to do this without copying the same Macro
and then changing the tab name? There are about 5 tabs that I don't want to
Macro to run on, so ideally, the Macro will ignore those. If that can't be
done, I can move them to another spreadsheet.


Thank you for your help,

Art.

 

[Dave Peterson]

You could loop through the worksheets with something like:

Option Explicit
Sub DoThemAll()

dim Wks as worksheet

for each wks in activeworkbook.worksheets
with wks
select case lcase(.name)
case is = "sheet1", "sheet99", "another sheet"
'do nothing
case else
'in case you just used the activesheet in your existing code
.select
call YourExistingMacroNameHere
end select
end with
next wks

end sub

Try it against a copy of your workbook--just in case. (Or don't save it if it
screws up!)

 

[Art MacNeil]

Hello all,

I have a spreadsheet with about 180 tabs. I've done a Macro to complete
a few tasks on the 1st Tab. I now need to complete those tasks on most of
the remaining tabs. Is there a way to do this without copying the same
Macro and then changing the tab name? There are about 5 tabs that I don't
want to Macro to run on, so ideally, the Macro will ignore those. If that
can't be done, I can move them to another spreadsheet.


Thank you for your help,

Art.

Got it:


I found the following solution at http://support.microsoft.com/?kbid=213621



Sub WorksheetLoop()

Dim WS_Count As Integer
Dim I As Integer

' Set WS_Count equal to the number of worksheets in the active
' workbook.
WS_Count = ActiveWorkbook.Worksheets.Count

' Begin the loop.
For I = 1 To WS_Count

' Insert your code here.
' The following line shows how to reference a sheet within
' the loop by displaying the worksheet name in a dialog box.
MsgBox ActiveWorkbook.Worksheets(I).Name

Next I

End Sub


Thanks,

Art.

 

[Art MacNeil]

Got it:


I found the following solution at
http://support.microsoft.com/?kbid=213621



Sub WorksheetLoop()

Dim WS_Count As Integer
Dim I As Integer

' Set WS_Count equal to the number of worksheets in the active
' workbook.
WS_Count = ActiveWorkbook.Worksheets.Count

' Begin the loop.
For I = 1 To WS_Count

' Insert your code here.
' The following line shows how to reference a sheet within
' the loop by displaying the worksheet name in a dialog box.
MsgBox ActiveWorkbook.Worksheets(I).Name

Next I

End Sub


Thanks,

Art.

Turns out I was wrong. The code above looked like it was doing what I
wanted, but it only ran the code on the active tab.

I'll give Dave Peterson's solution a try.

Art.

 

[Art MacNeil]

Dave,

It worked like a charm!

Thank you.

Art.

 

[Dave Peterson]

If your code works on the activesheet, you can add a line after this:

For I = 1 To WS_Count
ActiveWorkbook.Worksheets(I).select
...rest of code here.

But you'd still want to avoid the worksheets that you want to, er, avoid.

 

[Art MacNeil]

I'll give it a try, thanks.

Art.

If your code works on the activesheet, you can add a line after this:

For I = 1 To WS_Count
ActiveWorkbook.Worksheets(I).select
...rest of code here.

But you'd still want to avoid the worksheets that you want to, er, avoid.

 

[Henry Weber]

Macro to repeat the same tasks over 180 tabs.
Art MacNeil posted on Friday, September 22, 2006 8:52 PM

Hello all,

I have a spreadsheet with about 180 tabs. I've done a Macro to complete a
few tasks on the 1st Tab. I now need to complete those tasks on most of the
remaining tabs. Is there a way to do this without copying the same Macro
and then changing the tab name? There are about 5 tabs that I don't want to
Macro to run on, so ideally, the Macro will ignore those. If that can't be
done, I can move them to another spreadsheet.


Thank you for your help,

Art.



Art MacNeil wrote:

Macro to repeat the same tasks over 180 tabs.
22-Sep-06

Hello all

I have a spreadsheet with about 180 tabs. I've done a Macro to complete a
few tasks on the 1st Tab. I now need to complete those tasks on most of the
remaining tabs. Is there a way to do this without copying the same Macro
and then changing the tab name? There are about 5 tabs that I don't want to
Macro to run on, so ideally, the Macro will ignore those. If that can't be
done, I can move them to another spreadsheet

Thank you for your help

Art.

Previous Posts In This Thread:

Macro to repeat the same tasks over 180 tabs.
Hello all

I have a spreadsheet with about 180 tabs. I've done a Macro to complete a
few tasks on the 1st Tab. I now need to complete those tasks on most of the
remaining tabs. Is there a way to do this without copying the same Macro
and then changing the tab name? There are about 5 tabs that I don't want to
Macro to run on, so ideally, the Macro will ignore those. If that can't be
done, I can move them to another spreadsheet

Thank you for your help

Art.

Re: Macro to repeat the same tasks over 180 tabs.
You could loop through the worksheets with something like

Option Explici
Sub DoThemAll(

dim Wks as workshee

for each wks in activeworkbook.worksheet
with wk
select case lcase(.name
case is = "sheet1", "sheet99", "another sheet
'do nothin
case els
'in case you just used the activesheet in your existing cod
.selec
call YourExistingMacroNameHer
end selec
end wit
next wk

end su

Try it against a copy of your workbook--just in case. (Or don't save it if i
screws up!

Art MacNeil wrote

--

Dave Peterson

Re: Macro to repeat the same tasks over 180 tabs.

Got it

I found the following solution at http://support.microsoft.com/?kbid=21362


Sub WorksheetLoop(

Dim WS_Count As Intege
Dim I As Intege

' Set WS_Count equal to the number of worksheets in the activ
' workbook
WS_Count = ActiveWorkbook.Worksheets.Coun

' Begin the loop
For I = 1 To WS_Coun

' Insert your code here
' The following line shows how to reference a sheet withi
' the loop by displaying the worksheet name in a dialog box
MsgBox ActiveWorkbook.Worksheets(I).Nam

Next

End Su

Thanks

Art.

Re: Macro to repeat the same tasks over 180 tabs.
Turns out I was wrong. The code above looked like it was doing what
wanted, but it only ran the code on the active tab

I will give Dave Peterson's solution a try

Art.

Dave,It worked like a charm!
Dave

It worked like a charm

Thank you

Art.

Re: Macro to repeat the same tasks over 180 tabs.
If your code works on the activesheet, you can add a line after this

For I = 1 To WS_Coun
ActiveWorkbook.Worksheets(I).selec
...rest of code here

But you'd still want to avoid the worksheets that you want to, er, avoid

Art MacNeil wrote:

--

Dave Peterson

Re: Macro to repeat the same tasks over 180 tabs.
I will give it a try, thanks.

Art.


Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF Report Engine, Part 1
http://www.eggheadcafe.com/tutorial...74-4eba5c821311/wpf-report-engine-part-1.aspx

 

[ozgrid.com]

Sub AllButXSheets()
Dim ws As Worksheet

For Each ws In Worksheets
Select Case UCase(ws.Name)
Case "SHEET1", "SHEET2", "SHEET3"
'DO NOTHING
Case Else
With ws
'.With code here

End With
End Select
Next ws
End Sub