VBA paste value to last blank cell

[hajiali]

Sub PullFinalBidData()
  Dim wb As Workbook, fullpath As String
  Dim wt As Worksheet
  Set wt = Sheets("RA Build")
  With application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Filters.Add "Excel Files", "*.xlsx", 1
    If .Show <> -1 Then Exit Sub
    fullpath = .SelectedItems.item(1)
    Set wb = Workbooks.Open(fullpath)
    wb.Sheets(1).Range("B2:H500").Copy ThisWorkbook.Sheets("RA Build").Range("IX5")
    wb.Close False
  End With
End Sub

With the above code I am able to paste the value of B2:H500 of the selected file into IX5. IX5 at first is always blank cell with new file. If I want to use this macro multiple times and for each file that i open it Copies B2:H500 of the select file onto the next blank cell in column IX how would I redo this code.

any help is greatly appreciated.

 

[Caleeco]

Hey Hajiali

I modified one line of your code to find the last row using on the RA Build Sheet and then paste 1 row down.

I have not tested it so please let me know if it works

Sub PullFinalBidData()
  Dim wb As Workbook, fullpath As String
  Dim wt As Worksheet
  Set wt = Sheets("RA Build")
  With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Filters.Add "Excel Files", "*.xlsx", 1
    If .Show <> -1 Then Exit Sub
    fullpath = .SelectedItems.Item(1)
    Set wb = Workbooks.Open(fullpath)
    wb.Sheets(1).Range("B2:H500").Copy ThisWorkbook.Sheets("RA Build").Range("IX" & ThisWorkbook.Sheets("RA Build").Range("IX" & Rows.Count).End(xlUp).Row + 1)
    wb.Close False
  End With
End Sub

Cheers
Caleeco

 

[hajiali]

Works like magic thanks Caleeco

 

[Caleeco]

Works like magic thanks Caleeco

Awesome, thanks for letting me know ✌