[vba] Pull Data from a Specific Sheet in another Workbook

[ybr_15]

I get this code from this site: Excel Macro to Pull Data from another Workbook

Option Explicit

Private Sub CommandButton1_Click()
    ' Create and set the file dialog object.
    Dim fd As Office.FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    
    With fd
        .Filters.Clear
        .Title = "Select an Excel File"
        .Filters.Add "Excel Files", "*.xlsx?", 1
        .AllowMultiSelect = false
        
        Dim sFilePath As String
    
        If .Show = True Then
            sFilePath = .SelectedItems(1)
        End If
    End With
    
    If sFilePath <> "" Then
        readExcelData (sFilePath)
    End If
End Sub

Sub readExcelData(sTheSourceFile)
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False          ' Do not update the screen.

    Dim src As Workbook
    Set src = Workbooks.Open(sTheSourceFile, True, True)        ' Open the source file.

    Dim iRowsCount As Integer          ' Get the total Used Range rows in the source file.
    iRowsCount = src.Worksheets("Sheet1").UsedRange.Rows.Count

    Dim iColumnsCount As Integer     ' Get the total Columns in the source file.
    iColumnsCount = src.Worksheets("Sheet1").UsedRange.Columns.Count
    
    Dim iRows, iCols, iStartRow As Integer
    iStartRow = 0

    ' Now, read the source and copy data to the master file.
    For iRows = 1 To iRowsCount
        For iCols = 1 To iColumnsCount
            Cells(iRows + iStartRow, iCols) = src.Worksheets("Sheet1").Cells(iRows, iCols)
        Next iCols
    Next iRows
    
    iStartRow = iRows + 1
    iRows = 0

    ' Close the source file.
    src.Close False         ' False, so you don't save the source file.
    Set src = Nothing
ErrHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

The code above works fine if the data table starts from the first row. However, the data I have: empty data in the first row and merge cells. Here is a display of the excel data (from my company's server computer):

Can I help modify the code above so I can pull data from range A5:H16? Thank you

 

[Zot]

Since the data youare copying from is fix, then you can just do like below. I marked out most of the lines.

Sub readExcelData(sTheSourceFile)
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False          ' Do not update the screen.

    Dim src As Workbook
    Set src = Workbooks.Open(sTheSourceFile, True, True)        ' Open the source file.

'    Dim iRowsCount As Integer          ' Get the total Used Range rows in the source file.
'    iRowsCount = src.Worksheets("Sheet1").UsedRange.Rows.Count

'    Dim iColumnsCount As Integer     ' Get the total Columns in the source file.
'    iColumnsCount = src.Worksheets("Sheet1").UsedRange.Columns.Count
   
'    Dim iRows, iCols, iStartRow As Integer
'    iStartRow = 0

'    ' Now, read the source and copy data to the master file.
'    For iRows = 1 To iRowsCount
'        For iCols = 1 To iColumnsCount
'            Cells(iRows + iStartRow, iCols) = src.Worksheets("Sheet1").Cells(iRows, iCols)
'        Next iCols
'    Next iRows
'
'    iStartRow = iRows + 1
'    iRows = 0
    src.Worksheets("Sheet1").Range("A5:H16").Copy Sheets("Sheet1").Range("A1")

    ' Close the source file.
    src.Close False         ' False, so you don't save the source file.
    Set src = Nothing
ErrHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

From code above, the copy and paste is just this line.
src.Worksheets("Sheet1").Range("A5:H16").Copy Sheets("Sheet1").Range("A1")

Sheets("Sheet1").Range("A1") is your destination which is the workbook you are working on (macro in here too). Hope this works (not tested)