PDA

View Full Version : Solved: VBA to copy general spreadsheets from one workbook to another



yerromnitsuj
10-04-2011, 04:34 PM
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
10-05-2011, 01:22 AM
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
10-05-2011, 12:05 PM
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
10-05-2011, 04:50 PM
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
10-05-2011, 05:03 PM
I figured it out. I needed to put .value after each reference

yerromnitsuj
10-05-2011, 07:26 PM
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
10-05-2011, 09:27 PM
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
10-06-2011, 12:40 AM
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
10-06-2011, 11:03 AM
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.