Copying data from one workbook to another?
Posted by Dan on September 18, 2001 12:55 PM
I have a question on how to copy data from one excel file to another with VBA. I know how to do sheet-to-sheet within the same workbook, but I can't figure out how to go from sheet1 in workbookA to sheet2 in workbookB.
I have a range in one file, Data.xls, sheet1, B5:B10. I want this range to go to another file, Template.xls, sheet1, C10:C15. Can anyone let me know how to do this with VBA?
Thanks
Posted by Tom Urtis on September 18, 2001 1:33 PM
Here's one way to do it:
'COPY DATA FROM DATA FILE INTO TEMPLATE FILE
'Prepare Excel:
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Assuming you are currently working in the Data.xls file
and assuming the Template files path is C:\My Documents, if not then modify.
'First, see if you have the Template file open; if not then open it:
Note that this takes the worry away from remembering
to have the Template file open or closed at the point of copying
On Error GoTo b:
Windows("Template.xls").Activate
'If you do not have the file open there will be an error and it will goto b:
GoTo c:
b:
ChDir "C:\My Documents"
Workbooks.Open Filename:="C:\My Documents\Template.xls"
'This will open the Template file
c:
'Now when we get to c: the file is open either way so we re-activate the Data.xls:
Windows("Data.xls").Activate
'Then select Sheet1...
Sheets("Sheet1").Select
'...then select the range to be copied...
Range(B5:B10).Select
'...and then copy the entire sheet's range onto the clipboard.
Selection.Copy
'Next, re-activate the Template file:
Windows("WarehouseTemplate.xls").Activate
'Then select the destination worksheet...
Sheets("Sheet1").Select
'...then select the sheet's range where the data will be copied...
Range(C10).Select
'...and PSV from Data.xls's Sheet1 into Template.xlss Sheet1,
assuming you have pre-formatted your destination range.
Selection.PasteSpecial Paste:=xlValues
Go to A1, clear the clipboard, and save.
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
OPTIONAL:
'Close Template.xls if you don't need it anymore
Windows("Template.xls").Activate
ActiveWindow.Close
Hope this helps.
Tom Urtis
Posted by Tom Urtis on September 18, 2001 1:35 PM
Sorry, left out the last part of the code, here's the entire code
COPY DATA FROM DATA FILE INTO TEMPLATE FILE
'Prepare Excel:
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Assuming you are currently working in the Data.xls file
and assuming the Template files path is C:\My Documents, if not then modify.
'First, see if you have the Template file open; if not then open it:
Note that this takes the worry away from remembering
to have the Template file open or closed at the point of copying
On Error GoTo b:
Windows("Template.xls").Activate
'If you do not have the file open there will be an error and it will goto b:
GoTo c:
b:
ChDir "C:\My Documents"
Workbooks.Open Filename:="C:\My Documents\Template.xls"
'This will open the Template file
c:
'Now when we get to c: the file is open either way so we re-activate the Data.xls:
Windows("Data.xls").Activate
'Then select Sheet1...
Sheets("Sheet1").Select
'...then select the range to be copied...
Range(B5:B10).Select
'...and then copy the entire sheet's range onto the clipboard.
Selection.Copy
'Next, re-activate the Template file:
Windows("WarehouseTemplate.xls").Activate
'Then select the destination worksheet...
Sheets("Sheet1").Select
'...then select the sheet's range where the data will be copied...
Range(C10).Select
'...and PSV from Data.xls's Sheet1 into Template.xlss Sheet1,
assuming you have pre-formatted your destination range.
Selection.PasteSpecial Paste:=xlValues
Go to A1, clear the clipboard, and save.
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
OPTIONAL:
'Close Template.xls if you don't need it anymore
Windows("Template.xls").Activate
ActiveWindow.Close
NON OPTIONAL if you will:
Windows("Data.xls").Activate
Re-set Excel
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Posted by Dan on September 18, 2001 1:56 PM
Wow! Thanks a lot.
Thanks Tom! That is exactly what I was looking for, I just wasn't familar with the right commands. Thanks a lot and thanks for including comments so I know what each part does!
