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

ybr_15

Board Regular
Joined
May 24, 2016
Messages
194
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
I get this code from this site: Excel Macro to Pull Data from another Workbook
VBA Code:
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):
Pict_8.jpg

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

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,161
Office Version
  1. 2016
Platform
  1. Windows
Since the data youare copying from is fix, then you can just do like below. I marked out most of the lines.
VBA Code:
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,682
Messages
5,637,764
Members
416,982
Latest member
lisam77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top