Copy Workbooks and Paste into same spreadsheet
 

I have several Workbooks I need to copy from and paste into one
spreadsheet.

Say Template1, Template2, Template3

All with the same columns and all starting on the same row and just
pasted in the new spreadsheet on the next blank row. How can I do this
adding onto the macro below? Thanks.


Workbooks.Open Filename:= _
"C:\Documents and Settings\My Documents\template1.xls"
Range("A5:R61").Select
Selection.Copy
Windows("MyFile.xls").Activate
Range("A5").Select
ActiveSheet.Paste
End Sub


--
lcannon
------------------------------------------------------------------------
lcannon's Profile: http://www.excelforum.com/member.php...o&userid=24253
View this thread: http://www.excelforum.com/showthread...hreadid=378627

Copy Workbooks and Paste into same spreadsheet
 

Let's say that you open a workbook and transfer
Open the next workbook and transfer
and so on....
With this code in the master workbook...

than for each transfer - open a workbook

dim rw1 as Long, rw2 as Long

' find first open row in each workbook
rw1 = Thisworkbook.Sheets("MySheet").Cells(Rows.COUNT,
"A").End(xlUp).Offset(1, 0).Row
rw2 = Activeworkbook.ActiveSheet.Cells(Rows.COUNT, "A").End(xlUp).Offset(1,
0).Row

' transfer opened worksheet to master worksheet
Activeworkbook.ActiveSheet.Range(Cells(5,18),Cells (rw2,18).Copy _
Destination:= Thisworkbook.Sheets("MySheet").Cells(rw1,1)


You could set some conditions on rw1 like this:
If rw1<5 then
rw1 = 5
End If
--
steveB

Remove "AYN" from email to respond
"lcannon" wrote in
message ...

I have several Workbooks I need to copy from and paste into one
spreadsheet.

Say Template1, Template2, Template3

All with the same columns and all starting on the same row and just
pasted in the new spreadsheet on the next blank row. How can I do this
adding onto the macro below? Thanks.


Workbooks.Open Filename:= _
"C:\Documents and Settings\My Documents\template1.xls"
Range("A5:R61").Select
Selection.Copy
Windows("MyFile.xls").Activate
Range("A5").Select
ActiveSheet.Paste
End Sub


--
lcannon
------------------------------------------------------------------------
lcannon's Profile:
http://www.excelforum.com/member.php...o&userid=24253
View this thread: http://www.excelforum.com/showthread...hreadid=378627