yerromnitsuj
New Member
- Joined
- Sep 12, 2011
- Messages
- 32
I have a few workbooks (let's call them A, B, and C), each with a tab named "template". After the template tab I have several worksheets that are unique to each workbook. I have a fourth workbook (workbook D) into which I want to copy the worksheets (after the template worksheet) from each workbook. I want the worksheets to be copied into workbook D after all existing worksheets. The worksheets will be changing from time to time in the future so I don't want to name each individual worksheet to copy them over.
For each workbook, I was thinking I could tell Excel to select the worksheet after "template" through the last worksheet then to copy those worksheets and paste them into workbook D after the last worksheet. When I record myself selecting each worksheet, right clicking, selecting "Copy or Move", selecting the new workbook to copy into, and specifying to copy after the existing worksheets this is what I get:
This will copy the existing specific worksheets, but I want to make the code more general to start at the worksheet after "template", whatever it may be, and to select, copy, and paste all subsequent worksheets. Any ideas?
For each workbook, I was thinking I could tell Excel to select the worksheet after "template" through the last worksheet then to copy those worksheets and paste them into workbook D after the last worksheet. When I record myself selecting each worksheet, right clicking, selecting "Copy or Move", selecting the new workbook to copy into, and specifying to copy after the existing worksheets this is what I get:
Code:
Sub Copy_Small_States()
'
' Copy_Small_States Macro
' Macro recorded 10/4/2011 '
'
Windows("2Q2011 Template (S).xls").Activate
Sheets(Array("AZ 4", "FL 4", "GA 4", "IL 4", "LA 3", "MD 4", "MO 4", "MS 3", "OH 4", _
"OK 3", "PA 4", "TN 4", "UT 3", "VA 4")).Select
Sheets(Array("AZ 4", "FL 4", "GA 4", "IL 4", "LA 3", "MD 4", "MO 4", "MS 3", "OH 4", _
"OK 3", "PA 4", "TN 4", "UT 3", "VA 4")).Copy Before:=Workbooks( _
"2Q2011 Template Final Results.xls").Sheets(33)
End Sub
This will copy the existing specific worksheets, but I want to make the code more general to start at the worksheet after "template", whatever it may be, and to select, copy, and paste all subsequent worksheets. Any ideas?