VBA - Open workbook and import data to next empty row

[ddoublev]

Hi,

I am relatively new to the VBA world and would much appreciate some assistance with the following.

A project currently being worked on requires a macro to do the following:

1. Open excel file from "Open file" dialog box
2. Find data in specific ranges
3. Copy data to next empty row of original worksheet
4. Close workbook

I have developed the following:

Sub Import()
Dim OpenFileName As String
 Dim wb As Workbook
 'Select and Open workbook
 OpenFileName = Application.GetOpenFilename()
 If OpenFileName = "False" Then Exit Sub
 Set wb = Workbooks.Open(OpenFileName)
 'Get data EXAMPLE
    ThisWorkbook.Sheets(1).Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = wb.Sheets(3).Range("A2").Value
    ThisWorkbook.Sheets(1).Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 1).Value = wb.Sheets(3).Range("B2").Value
    ThisWorkbook.Sheets(1).Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 6).Value = wb.Sheets(3).Range("G2").Value
 MsgBox ("Done")
 wb.Close
End Sub

The actual code is much larger as there is a lot more data to import however I trimmed to show the core code.
The issue I am having with this is the initial run works on the first column however the code doesn't work properly for columns B & G

Is someone able to help with a code change?

Much appreciated

Dylan

 

[JLGWhiz]

See if this does what you want

Sub Import()
Dim OpenFileName As String
 Dim wb As Workbook
 'Select and Open workbook
 OpenFileName = Application.GetOpenFilename()
 If OpenFileName = "False" Then Exit Sub
 Set wb = Workbooks.Open(OpenFileName)
 'Get data EXAMPLE
    ThisWorkbook.Sheets(1).Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = wb.Sheets(3).Range("A2").Value
    ThisWorkbook.Sheets(1).Cells(Cells.Rows.Count, 1).End(xlUp).Offset(, 1).Value = wb.Sheets(3).Range("B2").Value
    ThisWorkbook.Sheets(1).Cells(Cells.Rows.Count, 1).End(xlUp).Offset(, 6).Value = wb.Sheets(3).Range("G2").Value
 MsgBox ("Done")
 wb.Close
End Sub

after you have made an entry into column A, If you offset the row for the other two it will offset from the entry just made, not the same row that was used for column A.