VBA syntax error when trying to copy data from another workbook

[Saab95]

Hi all,

I'm getting a syntax error when trying to run this. What I'm doing is:
I have workbook open, then when I run vba it asks me to open another workbook, from which it copies from B4 until E last row in worksheet called balances.
Then it pastes data in current workbook from A last row.

A bit of help would be appreciated as I'm a beginner in vba. Thanks

Sub Get_Data_From_File()
Dim FileToOpen As Variant
Dim OpenBook As Workbook
Dim ws As Worksheet
Dim lastRow As Long
Application.ScreenUpdating = False
FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
If FileToOpen <> False Then
Set OpenBook = Application.Workbooks.Open(FileToOpen)
lastRow = OpenBook.Balance.Range("E" & Rows.Count).End(xlUp).Row
OpenBook.Balance.Range("B4:E" & lastRow").Copy
lastRow = ThisWorkbook.Worksheets("Import Data").Range("A" & Rows.Count).End(xlUp).Row
ThisWorkbook.Worksheets("Import Data").Range("E" & Rows.Count").PasteSpecial xlPasteValues
OpenBook.Close False

Application.ScreenUpdating = True
End Sub

 

[NdNoviceHlp]

OpenBook.("Balance").Range("B4:E" & lastRow).Copy
lastRow = ThisWorkbook.Worksheets("Import Data").Range("A" & Rows.Count).End(xlUp).Row
ThisWorkbook.Worksheets("Import Data").Range("E" & Rows.Count).PasteSpecial xlPasteValues

HTH. Dave

 

[Fluff]

What is Balance? It has been declared or assigned to anything.

 

[Saab95]

What is Balance? It has been declared or assigned to anything.

Hi, it is the name of the worksheet.

 

[Fluff]

In that case you need to use

lastRow = OpenBook.Sheets("Balance").Range("E" & Rows.Count).End(xlUp).Row

 

[Saab95]

I have changed the code but now experiencing a different problem

 

[Saab95]

Solved.

Sub Get_Data_From_File()
Dim FileToOpen As Variant
Dim OpenBook As Workbook
Dim ws As Worksheet
Dim lastRow As Long
Application.ScreenUpdating = False
FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
If FileToOpen <> False Then
Set OpenBook = Application.Workbooks.Open(FileToOpen)
lastRow = OpenBook.Worksheets("Balance").Range("E" & Rows.Count).End(xlUp).Row
OpenBook.Worksheets("Balance").Range("B4:E" & lastRow).Copy
lastRow = ThisWorkbook.Worksheets("Input_Rec_Report").Range("A" & Rows.Count).End(xlUp).Row
ThisWorkbook.Worksheets("Input_Rec_Report").Range("A" & lastRow).PasteSpecial xlPasteValues
OpenBook.Close False

Application.ScreenUpdating = True
End If
End Sub

 

[Fluff]

Glad you sorted it & thanks for letting us know.