Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home

Copy,paste macro from multiple sheets

Posted by Mar on September 27, 2001 11:55 AM
How would you go about making a macro that opens a list of workbooks and then copies the same range from all the workbooks and paste them in a new workbook?
thanx

Check out our Excel VBA Resources

Re: Copy,paste macro from multiple sheets

Posted by Barrie Davidson on September 27, 2001 12:53 PM

Try this macro.

Sub Open_and_Copy()
' Written by Barrie Davidson
Dim receivingFile As String
Dim filestoOpen, openFile
filestoOpen = Application.GetOpenFilename(, , , , True)
Workbooks.Add
receivingFile = ActiveWorkbook.Name
For count = 1 To UBound(filestoOpen)
Workbooks.Open FileName:=filestoOpen(count)
openFile = ActiveWorkbook.Name
Range("Your_Data_Range_Name").Copy
Workbooks(receivingFile).Activate
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.End(xlDown).Offset(1, 0).Select
Workbooks(openFile).Close
Next count
End Sub


Regards,
BarrieBarrie Davidson


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.