Select Various tabs using VBA
 

Thanks for all of your suggestions. Essentially would like to perform the
operation on all tabs except basic. The reason I am not including the tab
name in the code ist aht the tab names may not be the wame from worksheet to
worksheet.

How would I like this to perform? Honestly, I think taht Tab 2 would be
executed, then tab 3 would be selected and executed and then tab 4 etc. I do
not know that since the data will be different in each tab that all of the
tabs could be executed all at once.

I would exepect the exec to be done automatically. Essentially once I get
over this hurdle, I will write this code so that when the VBA is started,
this process will be done on every sorkbook within a directory. Generally
there are 40-50 files for this to be done on so as you can see I am hoping to
be able to execute this macro on all 50 workbooks with one click. I can
handle that part. I am just having trouble with the dynamic tabs portion.

thanks.

"Stopher" wrote:




Tab 1 = "Data" (This tab should not be touched)
Tab 2 = "Basic"
Tab 3 = "2-15"
Tab 4 = "4-15"
Tab 5 = "7-15"


So:

Sub PerforCalc()

Sheets("Basic").select

TheFormatCalcThing

Sheets("2-15").select

TheFormatCalcThing

Sheets("4-15").select

TheFormatCalcThing

Sheets("7-15").select

TheFormatCalcThing

End Sub

Sub TheFormatCalcThing()
Range("C5:EK5").Select
Application.CutCopyMode = False
Selection.Copy
Range("C6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("C11:EK11").Select
Application.CutCopyMode = False
Selection.Copy
Range("C12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("C27:EK27").Select
Application.CutCopyMode = False
Selection.Copy
Range("C28").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

End Sub

That way if somehow you get the sheet orders changed somehow, the calcs
will only ever ne on those specifc sheets.

Stopher

Select Various tabs using VBA
 

If you don't want to process Basic or Date, then

Dim sh as Worksheet
for each sh in Activeworkbook.Worksheets
if sh.name < "Basic" and sh.Name < "Data" then
sh.Activate
Range("C5:EK5").Select
Application.CutCopyMode = False
Selection.Copy
Range("C6").Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C11:EK11").Select
Application.CutCopyMode = False
Selection.Copy
Range("C12").Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C27:EK27").Select
Application.CutCopyMode = False
Selection.Copy
Range("C28").Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End if
Next
End sub

--
Regards,
Tom Ogilvy


"Chris" wrote in message
...
Thanks for all of your suggestions. Essentially would like to perform the
operation on all tabs except basic. The reason I am not including the tab
name in the code ist aht the tab names may not be the wame from worksheet
to
worksheet.

How would I like this to perform? Honestly, I think taht Tab 2 would be
executed, then tab 3 would be selected and executed and then tab 4 etc. I
do
not know that since the data will be different in each tab that all of the
tabs could be executed all at once.

I would exepect the exec to be done automatically. Essentially once I get
over this hurdle, I will write this code so that when the VBA is started,
this process will be done on every sorkbook within a directory. Generally
there are 40-50 files for this to be done on so as you can see I am hoping
to
be able to execute this macro on all 50 workbooks with one click. I can
handle that part. I am just having trouble with the dynamic tabs portion.

thanks.

"Stopher" wrote:




Tab 1 = "Data" (This tab should not be touched)
Tab 2 = "Basic"
Tab 3 = "2-15"
Tab 4 = "4-15"
Tab 5 = "7-15"


So:

Sub PerforCalc()

Sheets("Basic").select

TheFormatCalcThing

Sheets("2-15").select

TheFormatCalcThing

Sheets("4-15").select

TheFormatCalcThing

Sheets("7-15").select

TheFormatCalcThing

End Sub

Sub TheFormatCalcThing()
Range("C5:EK5").Select
Application.CutCopyMode = False
Selection.Copy
Range("C6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("C11:EK11").Select
Application.CutCopyMode = False
Selection.Copy
Range("C12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("C27:EK27").Select
Application.CutCopyMode = False
Selection.Copy
Range("C28").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

End Sub

That way if somehow you get the sheet orders changed somehow, the calcs
will only ever ne on those specifc sheets.

Stopher