Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Group worksheets
Hi
I'm trying to group worksheets, and not having much luck. I have tried this with and without arrays. I am using the sheet code names, not the tab names, and this is when I started having problems. The tab names worked until different tab names occured. Where am I going wrong? With MyBook Sheets(array("Sheet13", "Sheet14", "Sheet15", "Sheet16", "Sheet17", "Sheet18", "Sheet19", "Sheet20", "Sheet21", "Sheet22", "Sheet23", "Sheet24")).Select Sheets("sheet13").Activate Call UpdateVlookup Sheets("Sheet1").Activate Sheets("Sheet16").Activate ActiveWorkbook.Save ActiveWorkbook.Close End With Thanks for your help Fred |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Group worksheets
I'm not too sure what you are trying to do with this code but perhaps this???
With MyBook Sheets(array(Sheet13.name, Sheet14.name, Sheet15.name, Sheet16.name, _ Sheet17.name, Sheet18.name, Sheet19.name, Sheet20.name, Sheet21.name, _ Sheet22.name, Sheet23.name, Sheet24.name)).Select sheet13.Activate Call UpdateVlookup Sheet1.Activate Sheet16.Activate ActiveWorkbook.Save ActiveWorkbook.Close End With -- HTH... Jim Thomlinson "Fred" wrote: Hi I'm trying to group worksheets, and not having much luck. I have tried this with and without arrays. I am using the sheet code names, not the tab names, and this is when I started having problems. The tab names worked until different tab names occured. Where am I going wrong? With MyBook Sheets(array("Sheet13", "Sheet14", "Sheet15", "Sheet16", "Sheet17", "Sheet18", "Sheet19", "Sheet20", "Sheet21", "Sheet22", "Sheet23", "Sheet24")).Select Sheets("sheet13").Activate Call UpdateVlookup Sheets("Sheet1").Activate Sheets("Sheet16").Activate ActiveWorkbook.Save ActiveWorkbook.Close End With Thanks for your help Fred |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Group worksheets
Thanks Jim, but the code didn't work.
I'm trying to group 12 worksheets together, then select the first of the 12, run some code to fix formulas (which works great), then select the 4th sheet of the 12, save and close the workbook, and then repeat with all the workbooks in the folder (code not shown, but also works). Does this help? Fred "Jim Thomlinson" wrote: I'm not too sure what you are trying to do with this code but perhaps this??? With MyBook Sheets(array(Sheet13.name, Sheet14.name, Sheet15.name, Sheet16.name, _ Sheet17.name, Sheet18.name, Sheet19.name, Sheet20.name, Sheet21.name, _ Sheet22.name, Sheet23.name, Sheet24.name)).Select sheet13.Activate Call UpdateVlookup Sheet1.Activate Sheet16.Activate ActiveWorkbook.Save ActiveWorkbook.Close End With -- HTH... Jim Thomlinson "Fred" wrote: Hi I'm trying to group worksheets, and not having much luck. I have tried this with and without arrays. I am using the sheet code names, not the tab names, and this is when I started having problems. The tab names worked until different tab names occured. Where am I going wrong? With MyBook Sheets(array("Sheet13", "Sheet14", "Sheet15", "Sheet16", "Sheet17", "Sheet18", "Sheet19", "Sheet20", "Sheet21", "Sheet22", "Sheet23", "Sheet24")).Select Sheets("sheet13").Activate Call UpdateVlookup Sheets("Sheet1").Activate Sheets("Sheet16").Activate ActiveWorkbook.Save ActiveWorkbook.Close End With Thanks for your help Fred |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Group worksheets
Grouped worksheets may not be dong what you think it is. In VBA code can only
operate on one sheet at a time. Even if the sheets are grouped you will only be modifying one sheet. If you want to update formulas on multiple sheets then you need to pass each sheet to a procedure and run the updates. -- HTH... Jim Thomlinson "Fred" wrote: Thanks Jim, but the code didn't work. I'm trying to group 12 worksheets together, then select the first of the 12, run some code to fix formulas (which works great), then select the 4th sheet of the 12, save and close the workbook, and then repeat with all the workbooks in the folder (code not shown, but also works). Does this help? Fred "Jim Thomlinson" wrote: I'm not too sure what you are trying to do with this code but perhaps this??? With MyBook Sheets(array(Sheet13.name, Sheet14.name, Sheet15.name, Sheet16.name, _ Sheet17.name, Sheet18.name, Sheet19.name, Sheet20.name, Sheet21.name, _ Sheet22.name, Sheet23.name, Sheet24.name)).Select sheet13.Activate Call UpdateVlookup Sheet1.Activate Sheet16.Activate ActiveWorkbook.Save ActiveWorkbook.Close End With -- HTH... Jim Thomlinson "Fred" wrote: Hi I'm trying to group worksheets, and not having much luck. I have tried this with and without arrays. I am using the sheet code names, not the tab names, and this is when I started having problems. The tab names worked until different tab names occured. Where am I going wrong? With MyBook Sheets(array("Sheet13", "Sheet14", "Sheet15", "Sheet16", "Sheet17", "Sheet18", "Sheet19", "Sheet20", "Sheet21", "Sheet22", "Sheet23", "Sheet24")).Select Sheets("sheet13").Activate Call UpdateVlookup Sheets("Sheet1").Activate Sheets("Sheet16").Activate ActiveWorkbook.Save ActiveWorkbook.Close End With Thanks for your help Fred |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Group worksheets
Interesting. I did have it working and updating all grouped worksheets when
I was using the sheet tab names. So, I should be able to use a loop statement with the sheet code names, however, this didn't work. Any suggestions? For x = 13 to 24 'where x is the sheet code names Sheet(x).activate run other code.... Next x Fred "Jim Thomlinson" wrote: Grouped worksheets may not be dong what you think it is. In VBA code can only operate on one sheet at a time. Even if the sheets are grouped you will only be modifying one sheet. If you want to update formulas on multiple sheets then you need to pass each sheet to a procedure and run the updates. -- HTH... Jim Thomlinson "Fred" wrote: Thanks Jim, but the code didn't work. I'm trying to group 12 worksheets together, then select the first of the 12, run some code to fix formulas (which works great), then select the 4th sheet of the 12, save and close the workbook, and then repeat with all the workbooks in the folder (code not shown, but also works). Does this help? Fred "Jim Thomlinson" wrote: I'm not too sure what you are trying to do with this code but perhaps this??? With MyBook Sheets(array(Sheet13.name, Sheet14.name, Sheet15.name, Sheet16.name, _ Sheet17.name, Sheet18.name, Sheet19.name, Sheet20.name, Sheet21.name, _ Sheet22.name, Sheet23.name, Sheet24.name)).Select sheet13.Activate Call UpdateVlookup Sheet1.Activate Sheet16.Activate ActiveWorkbook.Save ActiveWorkbook.Close End With -- HTH... Jim Thomlinson "Fred" wrote: Hi I'm trying to group worksheets, and not having much luck. I have tried this with and without arrays. I am using the sheet code names, not the tab names, and this is when I started having problems. The tab names worked until different tab names occured. Where am I going wrong? With MyBook Sheets(array("Sheet13", "Sheet14", "Sheet15", "Sheet16", "Sheet17", "Sheet18", "Sheet19", "Sheet20", "Sheet21", "Sheet22", "Sheet23", "Sheet24")).Select Sheets("sheet13").Activate Call UpdateVlookup Sheets("Sheet1").Activate Sheets("Sheet16").Activate ActiveWorkbook.Save ActiveWorkbook.Close End With Thanks for your help Fred |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Group worksheets
Since you have posted none of your other code it is hard to comment. The one
thing I notice is that you are relying on active sheet to run your code. When you are accessing multiple workbooks this kind of thing can be combersome and dangerous. Also note that what you are refering to as code anme has nothing to do with the sheets code name. The loop you have is using the index property of the sheet. Once again that is a very dangerous way to proceed. Perhaps post your UpdateVlookup code and we will be able to point you in the right direction... Additionally are all of the sheets that need to be modified identical or not? -- HTH... Jim Thomlinson "Fred" wrote: Interesting. I did have it working and updating all grouped worksheets when I was using the sheet tab names. So, I should be able to use a loop statement with the sheet code names, however, this didn't work. Any suggestions? For x = 13 to 24 'where x is the sheet code names Sheet(x).activate run other code.... Next x Fred "Jim Thomlinson" wrote: Grouped worksheets may not be dong what you think it is. In VBA code can only operate on one sheet at a time. Even if the sheets are grouped you will only be modifying one sheet. If you want to update formulas on multiple sheets then you need to pass each sheet to a procedure and run the updates. -- HTH... Jim Thomlinson "Fred" wrote: Thanks Jim, but the code didn't work. I'm trying to group 12 worksheets together, then select the first of the 12, run some code to fix formulas (which works great), then select the 4th sheet of the 12, save and close the workbook, and then repeat with all the workbooks in the folder (code not shown, but also works). Does this help? Fred "Jim Thomlinson" wrote: I'm not too sure what you are trying to do with this code but perhaps this??? With MyBook Sheets(array(Sheet13.name, Sheet14.name, Sheet15.name, Sheet16.name, _ Sheet17.name, Sheet18.name, Sheet19.name, Sheet20.name, Sheet21.name, _ Sheet22.name, Sheet23.name, Sheet24.name)).Select sheet13.Activate Call UpdateVlookup Sheet1.Activate Sheet16.Activate ActiveWorkbook.Save ActiveWorkbook.Close End With -- HTH... Jim Thomlinson "Fred" wrote: Hi I'm trying to group worksheets, and not having much luck. I have tried this with and without arrays. I am using the sheet code names, not the tab names, and this is when I started having problems. The tab names worked until different tab names occured. Where am I going wrong? With MyBook Sheets(array("Sheet13", "Sheet14", "Sheet15", "Sheet16", "Sheet17", "Sheet18", "Sheet19", "Sheet20", "Sheet21", "Sheet22", "Sheet23", "Sheet24")).Select Sheets("sheet13").Activate Call UpdateVlookup Sheets("Sheet1").Activate Sheets("Sheet16").Activate ActiveWorkbook.Save ActiveWorkbook.Close End With Thanks for your help Fred |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Group worksheets
Like Jim I am not really too sure what you are trying to do but I can offer a
slightly more elegant way to select the sheets. You can adapt as required. Hope useful. Dim ShArr() As String 'Create an Array 'of all Sheets Dim sh As Long With ThisWorkbook.Worksheets ReDim ShArr(1 To .Count) For sh = 1 To .Count ShArr(sh) = .Item(sh).Name Next sh End With Worksheets(ShArr).Select -- jb "Fred" wrote: Hi I'm trying to group worksheets, and not having much luck. I have tried this with and without arrays. I am using the sheet code names, not the tab names, and this is when I started having problems. The tab names worked until different tab names occured. Where am I going wrong? With MyBook Sheets(array("Sheet13", "Sheet14", "Sheet15", "Sheet16", "Sheet17", "Sheet18", "Sheet19", "Sheet20", "Sheet21", "Sheet22", "Sheet23", "Sheet24")).Select Sheets("sheet13").Activate Call UpdateVlookup Sheets("Sheet1").Activate Sheets("Sheet16").Activate ActiveWorkbook.Save ActiveWorkbook.Close End With Thanks for your help Fred |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I wish to group my worksheets under group tabs | Excel Worksheet Functions | |||
I wish to group my worksheets under group tabs | Excel Worksheet Functions | |||
How do I group worksheets (Lotus 123 function is "Sheet>Group Shee | Excel Worksheet Functions | |||
Group Worksheets | Excel Programming | |||
Cannot Group Seven Worksheets, only Six | Excel Discussion (Misc queries) |