Hi.
I've got the concept of what I want to do in my head, but making it happen isn't working...
I have a sheet with records from 45 different places. Each place could have anywhere from 1 to 1000 records associated with it.
e.g.
Building A10000 has 45 records
Building A20000 has 900 records
(Building name is always a letter followed by 5 numbers. i.e. A10000 or Z99999)
I have a data sheet and 16 individual pivot sheets.
I have managed to split the data sheet by building name. (using code off of here)
I would like to select the separate building sheet plus the 16 individual pivot sheets and move these to a new workbook, which gets saved at location path name.
That will be sheets A1 + 16 to A45 + 16 as new workbooks in a new location.
I thought it would be something like
For each ws in wb with name like "******"
select ws and array
save as new work book with path
I'm looking around trying to cobble things together and getting nowhere.
Any help on this would be great.
I have zero code to do this.
I think there are a number of elements I'm after help and advice on:
1) I can define a group of worksheets as an array, but can I select this array plus a ws with a name like and then copy to a new work book?
2) How does the "choose sheet name with name like" functionality work? I would guess that "Y00000" and "Y00001" are all "******". How do i say "letter at position 1, 0-9 at positions 2-6"?
2) how can I then loop through all ws with name like "Y0000", selecting that sheet plus the array and saving to a new workbook at location with name like the individual sheet name ("Y00000")?
4) The pivot tables in the array will be based on the original (all data) table. When the pivots are selected and moved with the individual sheet, they will have to be updated to look at the data in the individual sheet. I have a macro to update all the pivots separately. Can the pivots be updated in the copy and create new workbook procedure?
I appreciate this may be fairly simple but also fairly complicated to explain / provide advice on and am grateful for any response.
thanks!
I've got the concept of what I want to do in my head, but making it happen isn't working...
I have a sheet with records from 45 different places. Each place could have anywhere from 1 to 1000 records associated with it.
e.g.
Building A10000 has 45 records
Building A20000 has 900 records
(Building name is always a letter followed by 5 numbers. i.e. A10000 or Z99999)
I have a data sheet and 16 individual pivot sheets.
I have managed to split the data sheet by building name. (using code off of here)
I would like to select the separate building sheet plus the 16 individual pivot sheets and move these to a new workbook, which gets saved at location path name.
That will be sheets A1 + 16 to A45 + 16 as new workbooks in a new location.
I thought it would be something like
For each ws in wb with name like "******"
select ws and array
save as new work book with path
I'm looking around trying to cobble things together and getting nowhere.
Any help on this would be great.
I have zero code to do this.
I think there are a number of elements I'm after help and advice on:
1) I can define a group of worksheets as an array, but can I select this array plus a ws with a name like and then copy to a new work book?
2) How does the "choose sheet name with name like" functionality work? I would guess that "Y00000" and "Y00001" are all "******". How do i say "letter at position 1, 0-9 at positions 2-6"?
2) how can I then loop through all ws with name like "Y0000", selecting that sheet plus the array and saving to a new workbook at location with name like the individual sheet name ("Y00000")?
4) The pivot tables in the array will be based on the original (all data) table. When the pivots are selected and moved with the individual sheet, they will have to be updated to look at the data in the individual sheet. I have a macro to update all the pivots separately. Can the pivots be updated in the copy and create new workbook procedure?
I appreciate this may be fairly simple but also fairly complicated to explain / provide advice on and am grateful for any response.
thanks!
