Copy data from another excel file

Summerso1

New Member
Joined
Jul 24, 2014
Messages
45
Hi I’m wanting to build a VBA for opening another excel file location (c:\excelfiles\timetable.xls) and then copying data C:H from “Sheet1” and paste in to the VBA excel file in sheet “DATA”
Is this possible to get data from another file?
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,711
Office Version
  1. 2013
Platform
  1. Windows
To install this code, press Alt + F11. When the VB Editor window appears, copy the procedure into the large pane and close the editor. If your Excel version is 2007 or later, save the file as a macro enabled workbook with extension .xlsm. To run the procedure from the Excel screen, press Alt + F8 and double click on the procedure name.
Code:
Sub copyStuff()
Dim sh1 As Worksheet, sh2 As Worksheet, wb As Workbook
Set sh1 = Sheets("DATA") 'Edit sheet name
On Error Resume Next
    If Workbooks("timetable.xls") Is Nothing Then
        Set wb = Workbook.Open("C\excelfiles\timetable.xls")
    End If
On Error GoTo 0
Set sh2 = wb.Sheets(1) 'Edit sheet name
sh2.Range("C1", sh2.Cells(Rows.Count, "H").End(xlUp0)).Copy sh1.Range("C1")
wb.Close False
End Sub
 

Summerso1

New Member
Joined
Jul 24, 2014
Messages
45
Its not working i have updated the exact links



Dim sh1 As Worksheet, sh2 As Worksheet, wb As Workbook


Set sh1 = Sheets("DATA") 'Edit sheet name
On Error Resume Next
If Workbooks("DC Storm Email Report.xls") Is Nothing Then
Set wb = Workbook.Open("H:\Online\Email\Reporting\DC Storm Email Report.xls")
End If
On Error GoTo 0




Set sh2 = wb.email_report 'Edit sheet name
sh2.Range("C1", sh2.Cells(Rows.Count, "H").End(xlUp0)).Copy sh1.Range("C1")
wb.Close False
End Sub
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,711
Office Version
  1. 2013
Platform
  1. Windows
Its not working i have updated the exact links
That does not give me much of a clue as to what the problem might be, but try it with the sheet name for the second workbook using the syntax as shown in the code below. If you get an error message, please post the content of the error message and specify the line that was highlighted in the code when you click the debug button. If the code appears to do nothing, then state it that way. Be sure you install the code into the standard code module 1.


Code:
Sub copyStuff2()
Dim sh1 As Worksheet, sh2 As Worksheet, wb As Workbook
Set sh1 = Sheets("DATA") 'Edit sheet name
On Error Resume Next
    If Workbooks("DC Storm Email Report.xls") Is Nothing Then
        Set wb = Workbook.Open("H:Online\Email\Reporting\DC Storm Email Report.xls")
    End If
On Error GoTo 0
Set sh2 = wb.Sheets("email_report") 'Edit sheet name
sh2.Range("C1", sh2.Cells(Rows.Count, "H").End(xlUp0)).Copy sh1.Range("C1")
wb.Close False
End Sub
 

Summerso1

New Member
Joined
Jul 24, 2014
Messages
45

ADVERTISEMENT

Hi sorry, done all that its still highlighting the 9th row which contains the email_report
 

Summerso1

New Member
Joined
Jul 24, 2014
Messages
45
Anyone any ideas? I want to open another workbook copy the data and paste into the first workbook, so there are two files the open file and destination file from which I'm wanting the data to be copied from.
 
Last edited:

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,711
Office Version
  1. 2013
Platform
  1. Windows
There was a typo in the code. See if it will work now.
Code:
Sub copyStuff3()
Dim sh1 As Worksheet, sh2 As Worksheet, wb As Workbook
Set sh1 = Sheets("DATA") 'Edit sheet name
On Error Resume Next
    If Workbooks("DC Storm Email Report.xls") Is Nothing Then
        Set wb = Workbook.Open("H:Online\Email\Reporting\DC Storm Email Report.xls")
    End If
On Error GoTo 0
Set sh2 = wb.Sheets("email_report") 'Edit sheet name
sh2.Range("C1", sh2.Cells(Rows.Count, "H").End(xlUp)).Copy sh1.Range("C1")
wb.Close False
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,478
Messages
5,572,354
Members
412,459
Latest member
asmi_1758
Top