MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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 file’s 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.xls’s 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 file’s 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.xls’s 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!