Copy sheets with content into same workbook

[A. Karatas]

Hi,

I want to build a macro in which 6 particular sheets are copied (with
content, formulas and pivottables) and put at the end of the workbook.
The problem with recording is that the original sheets can differ from
sheet name on each update of the database because of a macro that
looks at a certain cell of that particular sheet and than renames
accordingly (after updating the pivot). Today the sheet name can be
6a- CELL- SRMS-AD and the next time 6a- NUT -SRMS-AD.
At recording it looks like this, but this is not working for me. Any
help???


Sub multiplysheets()
' multiplysheets Macro
' Macro recorded 27-3-2007 by Karata01

Sheets(Array("6a- Cell-SRMS-AD YTD", "6b- Cell-SRMS-AD-CUS YTD", _
"6c- Cell-SRMS-AD-CUS YTD", "6d- Cell-SRMS-A-P-C YTD", "6e-
Cell-Enduse YTD", _
"6f- Cell-SRMS-cntry YTD")).Select
Sheets("6f- Cell-SRMS-cntry YTD").Activate
Sheets(Array("6a- Cell-SRMS-AD YTD", "6b- Cell-SRMS-AD-CUS YTD", _
"6c- Cell-SRMS-AD-CUS YTD", "6d- Cell-SRMS-A-P-C YTD", "6e-
Cell-Enduse YTD", _
"6f- Cell-SRMS-cntry YTD")).Copy Before:=Sheets(53)
End Sub

Thanks in advance

 

[Jim Rech]

A macro can only copy particular sheets if they can be identified in some
way - by name, position, content, selection, etc. Once you can lay out the
rules for identifying the sheets a macro can be written to use it.

--
Jim
| Hi,
|
| I want to build a macro in which 6 particular sheets are copied (with
| content, formulas and pivottables) and put at the end of the workbook.
| The problem with recording is that the original sheets can differ from
| sheet name on each update of the database because of a macro that
| looks at a certain cell of that particular sheet and than renames
| accordingly (after updating the pivot). Today the sheet name can be
| 6a- CELL- SRMS-AD and the next time 6a- NUT -SRMS-AD.
| At recording it looks like this, but this is not working for me. Any
| help???
|
|
| Sub multiplysheets()
| ' multiplysheets Macro
| ' Macro recorded 27-3-2007 by Karata01
|
| Sheets(Array("6a- Cell-SRMS-AD YTD", "6b- Cell-SRMS-AD-CUS YTD", _
| "6c- Cell-SRMS-AD-CUS YTD", "6d- Cell-SRMS-A-P-C YTD", "6e-
| Cell-Enduse YTD", _
| "6f- Cell-SRMS-cntry YTD")).Select
| Sheets("6f- Cell-SRMS-cntry YTD").Activate
| Sheets(Array("6a- Cell-SRMS-AD YTD", "6b- Cell-SRMS-AD-CUS YTD", _
| "6c- Cell-SRMS-AD-CUS YTD", "6d- Cell-SRMS-A-P-C YTD", "6e-
| Cell-Enduse YTD", _
| "6f- Cell-SRMS-cntry YTD")).Copy Before:=Sheets(53)
| End Sub
|
| Thanks in advance
|

 

[A. Karatas]

So, for example, can I not use the sheetnumbering as seen in VBA.
sheet 15(6a-CELLblablabla). Sheet 15 doesn't change (the name does).
Or is it possible to give a particular cell in a sheet a value and let
a macro search the workbook and each time it finds the value copy the
complete sheet???