Solved: VBA to copy general spreadsheets from one workbook to another

[yerromnitsuj]

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:


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?

[Bob Phillips]

This should get you started



Sub CopySheets()
Dim wb As Variant
Dim idx As Long
Dim i As Long

For Each wb In Array("Book3", "Book4") '<---- change to list your 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

[yerromnitsuj]

Thanks! This was great. I used your code and edited it a bit. This is what I have:



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:

With Workbooks(wb)
Any ideas of what might be the problem?

[yerromnitsuj]

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?

[yerromnitsuj]

I figured it out. I needed to put .value after each reference

[yerromnitsuj]

My code is supposed to open four workbooks, each of which have a worksheet entitled "Template", loop through each, and for each workbook copy and paste all worksheets after "Template" into a new workbook. The macro is working great until it gets to the final workbook and it stops part of the way through and I get an error saying I can't name a worksheet the same name as an existing worksheet. When I try to move ahead with the macro, it seems that instead of pasting into the new workbook it is trying to paste into the original workbook. I can't figure out why. Here is the macro:


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").Value, Worksheets(SheetA).Range("N48").Value, Worksheets(SheetA).Range("N49").Value, Worksheets(SheetA).Range("N50").Value) '<---- 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

Windows(Worksheets(SheetA).Range("N47").Value).Activate
ActiveWorkbook.Close savechanges:=False

Windows(Worksheets(SheetA).Range("N48").Value).Activate
ActiveWorkbook.Close savechanges:=False

Windows(Worksheets(SheetA).Range("N49").Value).Activate
ActiveWorkbook.Close savechanges:=False

Windows(Worksheets(SheetA).Range("N50").Value).Activate
ActiveWorkbook.Close savechanges:=False
End Sub

And here is where the error occurs after it's looped through a few worksheets on the final workbook in the array:


.Worksheets(i).Copy after:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)


Why might Excel out of nowhere try to paste into the workbook from where it's being copied when it's done what I want it to do up until this seemingly random point? Thanks for the help!

[GTO]

Hi there,

Did you try the suggestions Here (http://www.mrexcel.com/forum/showthread.php?t=583317)?

I realize that neither AlphaFrog or I realized that you wanted the workbooks opened, but the examples show how to accomplish the copying without depending on what window is active and so on.

[Bob Phillips]

See if this works (I tidied the code up as well)



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").Value

Workbooks.Open (Worksheets(SheetA).Range("L47")), UpdateLinks:=0
Workbooks.Open (Worksheets(SheetA).Range("L48")), UpdateLinks:=0
Workbooks.Open (Worksheets(SheetA).Range("L49")), UpdateLinks:=0
Workbooks.Open (Worksheets(SheetA).Range("L50")), UpdateLinks:=0

With Workbooks(Template_file).Worksheets(SheetA)

For Each wb In Array(.Range("N47").Value, .Range("N48").Value, .Range("N49").Value, .Range("N50").Value)

With Workbooks(wb)

idx = .Worksheets("Template").Index
For i = idx + 1 To .Worksheets.Count

.Worksheets(i).Copy after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
Next i
End With
Next wb

Workbooks(.Range("N47").Value).Close savechanges:=False
Workbooks(.Range("N48").Value).Close savechanges:=False
Workbooks(.Range("N49").Value).Close savechanges:=False
Workbooks(.Range("N50").Value).Close savechanges:=False
End With
End Sub

[yerromnitsuj]

Hi there,

Did you try the suggestions Here (http://www.mrexcel.com/forum/showthread.php?t=583317)?

I realize that neither AlphaFrog or I realized that you wanted the workbooks opened, but the examples show how to accomplish the copying without depending on what window is active and so on.

To be honest, I didn't really understand some of your code and didn't try it because this other code came up that I understood a lot more. I added a few lines to open the appropriate workbooks and to close them when the macro is done and your code on this link works great, GTO! Thanks!

The code I was using was working, but no matter what it always stopped copying into the file I wanted after it had copied around 36 sheets. No idea why. Thanks again, GTO.