Create selection of worksheets

[Rick_Stanich]

I am stumbling on how I can create a selection of worksheets based on a value in a specified cell.
Example:
If sheet1 cell D4 value is "10" and sheet2 cell D4 value is "10" I then need to select these and copy the sheets as a group, pasting at the end of the last sheet with the cell D4 value of "10" (sheet2). Of course I wont know what sheets will have the value.

I can get the values of the cells for each sheet, I do not know how to put this in some array(?) to select and copy sheets.
(assume option explicit)

Please Login or Register  to view this content.

As always, any input is appreciated.

[Leith Ross]

Hello Rick_Stanich,

This macro runs in Excel 2003. Since I don't have Excel 2007, I can't test it for that platform. If if doesn't work on 2007 then hopefully it will getting you going in the right direction. Place this code in a Standard VBA Module by itself. The reason is the macro sets Option Base to 1. This affects the lower bound subscript of all arrays. Keeping this code separated can avoid indexing headaches later on.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[Rick_Stanich]

Leith, thanks for your input, unfortunately I am "Array" challenged thus I am not fully (or is that fooly ) following the code you supplied.

I get the following error when attempting to run in Excel 2007.

Please Login or Register  to view this content.

[Leith Ross]

Hello Rick,

The Array function creates an array whose first element starts at 1. Other arrays' firsts elements start at 0 (zero). Setting the Option Base statement to 1 forces all arrays that are created in the module to start at 1, and not zero.

Sincerely,
Leith Ross

[Rick_Stanich]

Leith, thanks for getting me started. I had to enlist help from others and found this simple change to your code.

Changed:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[Leith Ross]

Hello Rick,

Thanks for that post. I'll make a note of that for other Excel 2007 users who may have that question.

Thanks,
Leith Ross