Copy data from another excel file

[Summerso1]

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?

 

[JLGWhiz]

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.

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]

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]

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.

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]

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

 

[Summerso1]

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.

 

[Summerso1]

Close this pointless!!

 

[JLGWhiz]

There was a typo in the code. See if it will work now.

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