vba to import data from another spreadsheet

[steve2115]

Looking for help with the following. Need to modify the below vba code so that when it imports the data it only imports all the rows with data on them. Currently when it imports the zzmr9820o.xlsx file it also is bringing in empty rows below the data. Need it to find the lastrow on zzmr9820o.xlsx and then import into my excel table.

Sub Import_Click()

Dim OpenFileName As String
 Dim wb As Workbook
   
   'Select and Open workbook
 OpenFileName = Application.GetOpenFilename("zzmr9820o,*.xlsx")
 If OpenFileName = "False" Then Exit Sub
 Set wb = Workbooks.Open(OpenFileName)
 
 'Get data EXAMPLE
 ThisWorkbook.Sheets("Send Emails").Range("G:V").Value = wb.Sheets(1).Range("A:P").Value
  
 MsgBox ("Import Complete")
wb.Close
    
End Sub

 

[johnnyL]

I haven't tested this, but see if the following works for you:

Sub Import_Click()
'
    Dim LastRowInwbSheet    As Long
    Dim OpenFileName        As String
    Dim wb                  As Workbook
'
    OpenFileName = Application.GetOpenFilename("zzmr9820o,*.xlsx")                              ' Select and Open workbook
'
    If OpenFileName = "False" Then Exit Sub
'
    Set wb = Workbooks.Open(OpenFileName)
'
    LastRowInwbSheet = wb.Sheets(1).Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row                      ' Returns a Row Number
    
    ThisWorkbook.Sheets("Send Emails").Range("G:V").Value = wb.Sheets(1).Range("A:P" & LastRowInwbSheet).Value     ' Get data EXAMPLE
'
    MsgBox ("Import Complete")
'
    wb.Close
End Sub

 

[steve2115]

Just tested - code is erroring at

ThisWorkbook.Sheets("Send Emails").Range("G:V").Value = wb.Sheets(1).Range("A:P" & LastRowInwbSheet).Value

 

[johnnyL]

Apologies!

How about:

Sub Import_ClickV2()
'
    Dim LastRowInwbSheet    As Long
    Dim OpenFileName        As String
    Dim wb                  As Workbook
'
    OpenFileName = Application.GetOpenFilename("zzmr9820o,*.xlsx")                                                              ' Select workbook
'
    If OpenFileName = "False" Then Exit Sub                                                                                     ' Exit if user cancels
'
    Set wb = Workbooks.Open(OpenFileName)                                                                                       ' Open workbook selected
'
    LastRowInwbSheet = wb.Sheets(1).Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row                                   ' Returns a Row Number
    
    ThisWorkbook.Sheets("Send Emails").Range("G1:V" & LastRowInwbSheet) = wb.Sheets(1).Range("A1:P" & LastRowInwbSheet).Value   ' Get data EXAMPLE
'
    MsgBox ("Import Complete")                                                                                                  ' Let user know import is complete
'
    wb.Close                                                                                                                    ' Close user selected workbook
End Sub

 

[steve2115]

Just tested - Works perfect!. Thanks for the help and quick turnaround