Copy Spreadsheets from one workbook to another

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:

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?
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Try something like this...

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Copy_Small_States()<br>    <br>    <SPAN style="color:#00007F">Dim</SPAN> wb <SPAN style="color:#00007F">As</SPAN> Workbook, wbD <SPAN style="color:#00007F">As</SPAN> Workbook<br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> wbD = Workbooks("2Q2011 Template Final Results.xls")    <SPAN style="color:#007F00">' Destination workbook</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> wb <SPAN style="color:#00007F">In</SPAN> Application.Workbooks                        <SPAN style="color:#007F00">' Loop through all open workbooks</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> wb.Name <> wbD.Name <SPAN style="color:#00007F">Then</SPAN>                             <SPAN style="color:#007F00">' Don't copy from destination workbook</SPAN><br>            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> wb.Worksheets                        <SPAN style="color:#007F00">' Loop through each worksheet in each workbook</SPAN><br>                <SPAN style="color:#00007F">If</SPAN> ws.Name <> "Template" <SPAN style="color:#00007F">Then</SPAN>                   <SPAN style="color:#007F00">' Ignore worksheets named "Template"</SPAN><br>                    ws.Copy After:=wbD.Sheets(wbD.Sheets.Count) <SPAN style="color:#007F00">' Copy all other worksheets</SPAN><br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN> ws<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> wb<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
Howdy All,

Just another take...
Rich (BB code):
Option Explicit
    
Sub exa7()
Dim wb          As Workbook
Dim wksHack     As Worksheet
Dim i           As Long
Dim wksCol()    As String
    
    For Each wb In Workbooks
        If Not wb.Name = ThisWorkbook.Name Then
            Set wksHack = Nothing
            '// attempt to set a reference to sheet named 'Template'.  If this fails, we//
            '// skip the wb.                                                            //
            On Error Resume Next
            Set wksHack = wb.Worksheets("Template")
            On Error GoTo 0
            If Not wksHack Is Nothing Then
                '// Ensure that 'Template' isn't the last sheet...                      //
                If Not wksHack.Index = wb.Worksheets.Count Then
                    '// ...resize array and add the sheet name(s) that we want to copy. //
                    ReDim wksCol(1 To wb.Worksheets.Count - wksHack.Index)
                    For i = 1 To wb.Worksheets.Count - wksHack.Index
                        wksCol(i) = wb.Worksheets(wksHack.Index + i).Name
                    Next
                    '// Copy the collection of sheets.                                  //
                    wb.Worksheets(wksCol).Copy _
                        After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
                End If
            End If
        End If
    Next
End Sub
 
Upvote 0
This is what I now have:


Code:
Private Template_file As String
 
Sub CopySheets()
    Dim wb As Variant
    Dim idx As Long
    Dim i As Long
    Dim YM As String
 
 
    Template_file = Worksheets(SheetA).Range("B5")
 
 
    Workbooks.Open (Worksheets(SheetA).Range("L47")), UpdateLinks:=0
 
    Windows(Template_file).Activate
 
        Sheets(SheetA).Select
 
    Workbooks.Open (Worksheets(SheetA).Range("L48")), UpdateLinks:=0
 
    Windows(Template_file).Activate
 
        Sheets(SheetA).Select
 
    Workbooks.Open (Worksheets(SheetA).Range("L49")), UpdateLinks:=0
 
    Windows(Template_file).Activate
 
        Sheets(SheetA).Select
 
    Workbooks.Open (Worksheets(SheetA).Range("L50")), UpdateLinks:=0
 
    Windows(Template_file).Activate
 
        Sheets(SheetA).Select
 
    ThisWorkbook.Activate
 
    For Each wb In Array(Worksheets(SheetA).Range("N47"), Worksheets(SheetA).Range("N48"), Worksheets(SheetA).Range("N49"), Worksheets(SheetA).Range("N50")) '<---- target workbooks
 
        With Workbooks(wb)
 
            idx = .Worksheets("Template").Index
            For i = idx + 1 To .Worksheets.Count
 
                .Worksheets(i).Copy after:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)
            Next i
        End With
    Next wb
End Sub
I am getting a type mismatch error here:

Code:
With Workbooks(wb)

Any ideas of what might be the problem?
 
Upvote 0
Still working on this. It seems that Worksheets(SheetA).Range("N47") isn't being recognized as a workbook. I was thinking of figuring out how to do some kind of cast command where I say if certain conditions are met to treat wb as a workbook, but I don't know if that's possible or if it's the best way to go about solving the problem. Any ideas?
 
Upvote 0
Howdy All,

Just another take...
Rich (BB code):
Option Explicit
    
Sub exa7()
Dim wb          As Workbook
Dim wksHack     As Worksheet
Dim i           As Long
Dim wksCol()    As String
    
    For Each wb In Workbooks
        If Not wb.Name = ThisWorkbook.Name Then
            Set wksHack = Nothing
            '// attempt to set a reference to sheet named 'Template'.  If this fails, we//
            '// skip the wb.                                                            //
            On Error Resume Next
            Set wksHack = wb.Worksheets("Template")
            On Error GoTo 0
            If Not wksHack Is Nothing Then
                '// Ensure that 'Template' isn't the last sheet...                      //
                If Not wksHack.Index = wb.Worksheets.Count Then
                    '// ...resize array and add the sheet name(s) that we want to copy. //
                    ReDim wksCol(1 To wb.Worksheets.Count - wksHack.Index)
                    For i = 1 To wb.Worksheets.Count - wksHack.Index
                        wksCol(i) = wb.Worksheets(wksHack.Index + i).Name
                    Next
                    '// Copy the collection of sheets.                                  //
                    wb.Worksheets(wksCol).Copy _
                        After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
                End If
            End If
        End If
    Next
End Sub


I added a few lines to open and close the workbooks and this worked well! Thank you!
 
Upvote 0

Forum statistics

Threads
1,224,616
Messages
6,179,911
Members
452,949
Latest member
beartooth91

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top