Can you group worksheets together, and call upon them all at once within a VBA code?

[sy898661]

Hi again!

Okay so I was wondering if you amazing excel-guru's could help me out--bear with me while I try to word this properly.

I have a bunch of sheets in a workbook (Named A, B, C, ... etc) that all contain different data but in identical tables (one on each page)

I have a user form that you can choose which sheet you want data from, and the code copies data from chosen sheet into a "main table".
The combo box that you choose your sheet from is named 'txtName' and the options are merely "A, B, C, ... etc", same as the sheet names.

Please Login or Register  to view this content.

Okay, so now I want to dive deeper.
I want to have a combo box with 'group' choices. Say Group 1 is for Sheets A & B, and Group 2 is for Sheets C & D

How would I write the code so that if I choose Group 1, excel will go copy/paste the data from Sheets A AND B into the main table? Is there a way to define a worksheet 'object' as being sheet A AND sheet B so I can keep the code the same?

I don't have a mock-file created to show exactly what I mean but can try to make one if my explanation is too difficult to understand!!!

[6StringJazzer]

Is there a way to define a worksheet 'object' as being sheet A AND sheet B so I can keep the code the same?

No. The only way to do this is to change your code to know which sheets are in which groups, and do two copy operations.

ETA: You might be able to define a discontiguous range over two sheets and do one physical copy, but I've never tried it. Regardless, you still have to map groups to sheets in the code.

[CK76]

Try using Sheets(Array("Sheet1", "Sheet2")) then loop through worksheet object.

AFAIK, you can't copy from multiple sheets in one shot.

[sy898661]

No. The only way to do this is to change your code to know which sheets are in which groups, and do two copy operations.

Okay thank you so much!

[sy898661]

Try using Sheets(Array("Sheet1", "Sheet2")) then loop through worksheet object.

AFAIK, you can't copy from multiple sheets in one shot.

Yes okay perfect!! I will try that! Thank you!

[sintek]

Make use of sheet to store Groups...See attached...Loop through sheets in stored Group

[sy898661]

Make use of sheet to store Groups...See attached...Loop through sheets in stored Group

YES thank you so much this is exactly what I was trying to do!!!

[sy898661]

Make use of sheet to store Groups...See attached...Loop through sheets in stored Group

Okay so now I'm getting a script out of range error.. when I look at my other code that is the same (with different variable names) there is no error! I tried renaming the first String and it still wont run.

Please Login or Register  to view this content.

[CK76]

Why string? String can only be used to set single worksheet object. If it's delimited string, you can use split to generate array from string...

There are few ways to loop through specific worksheets, some examples below.

Please Login or Register  to view this content.

Pick whichever method that suites your taste and needs.

[sy898661]

Why string? String can only be used to set single worksheet object. If it's delimited string, you can use split to generate array from string...

There are few ways to loop through specific worksheets, some examples below.

Pick whichever method that suites your taste and needs.

I keep getting out of range error

[jeffreybrown]

Hi sy898661,

Please do not quote whole posts as it just cause clutter.

[sy898661]

This is the code I have, the error keeps popping up when it hits the line

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[CK76]

Couple of things...
Below part should reside outside For... loop.

Please Login or Register  to view this content.

"ws1" ... I don't see this variable being defined in your code. Should it not be "ws"?

[sy898661]

Ahh yes, I see that now *facepalm*

Fixed the ws and now it works

[sy898661]

question was basically a new question so I am moving it to a new thread I hope that's okay please let me know if it is not and I will never do that again!