Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
tabs are missing even though 'tools-options-view-sheet tabs' ok? | Excel Worksheet Functions | |||
hide tabs from view then lock tabs? | Excel Discussion (Misc queries) | |||
Excel - option to select the same header for mutliple tabs | Setting up and Configuration of Excel | |||
Need help with using VBA to select tabs in Excel | Excel Programming | |||
Select sheet tabs in workbook & save to separate workbook files | Excel Worksheet Functions |