Help with Compiling Excel Spreadsheet Data into One Table

Hi everyone, having trouble with one line of code. I think I'm pretty close to figuring out this macro. I'm getting the following error:
"Run-time error '438':
Object doesn't support this property or method"
on this line of code: wbMstr.wsMstr.Range("c1").End(xlDown).Offset(1, 0).Select

How can I better define my method? Thanks!

Sub ImportCSVsWithReference()

'Summary:   Import all CSV files from a folder into a single sheet
'           adding a field in column A listing the CSV filenames

Dim wbCSV   As Workbook
Dim wbMstr  As Workbook:    Set wbMstr = ThisWorkbook
Dim wsMstr  As Worksheet:   Set wsMstr = ThisWorkbook.Sheets("Sheet1")
Dim fPath   As String:      fPath = "C:\Users\Eddie\Dropbox\Admob pull Data\Admob pull Data(eddie)\8.17\"    'path to CSV files, include the final \
Dim fCSV    As String

'If MsgBox("Clear the existing MasterCSV sheet before importing?", vbYesNo, "Clear?") _
    '= vbYes Then wsMstr.UsedRange.Clear

Application.ScreenUpdating = True  'speed up macro

fCSV = Dir(fPath & "*.csv")         'start the CSV file listing

    Do While Len(fCSV) > 0
      'open a CSV file
        Set wbCSV = Workbooks.Open(fPath & fCSV)
        wbCSV.ActiveSheet.Range(Range("a3:h3"), Range("a3").End(xlDown)).Copy
        wbMstr.wsMstr.Range("c1").End(xlDown).Offset(1, 0).Select
        
        wbCSV.Close False
      'ready next CSV
        fCSV = Dir
    Loop
 
Application.ScreenUpdating = True
End Sub

Hi excelatlife, are you looking to copy/paste/transfer values from the csv to the master? If so, try:

Option Explicit

Sub ImportCSVsWithReference()

'Summary: Import all CSV files from a folder into a single sheet
' adding a field in column A listing the CSV filenames

    Dim wbCSV As Workbook
    Dim wbMstr As Workbook: Set wbMstr = ThisWorkbook
    Dim wsMstr As Worksheet: Set wsMstr = ThisWorkbook.Sheets("Sheet1")
    Dim fPath As String: fPath = "C:\Users\Eddie\Dropbox\Admob pull Data\Admob pull Data(eddie)\8.17\" 'path to CSV files, include the final \"
    Dim fCSV As String
    Dim cSel As Range
    Dim pstSel As Range, cpySel As Range
    'If MsgBox("Clear the existing MasterCSV sheet before importing?", vbYesNo, "Clear?") _
     '= vbYes Then wsMstr.UsedRange.Clear
    Dim rCnt As Long, cCnt As Long
    Application.ScreenUpdating = False    'Set to false to speed up macro

    fCSV = Dir(fPath & "*.csv")    'start the CSV file listing

    Do While Len(fCSV) > 0
        'open a CSV file
        Set wbCSV = Workbooks.Open(fPath & fCSV)
        Set cpySel = wbCSV.ActiveSheet.Range(Range("a3:h3"), Range("a3").End(xlDown))
        rCnt = cpySel.Rows.Count
        cCnt = cpySel.Columns.Count
        Set pstSel = wsMstr.Range(wsMstr.Cells(wsMstr.Rows.Count, 3).End(xlUp).Offset(1, 0), wsMstr.Cells(wsMstr.Rows.Count, cCnt).End(xlUp).Offset(rCnt + 1, 0))
        pstSel.Cells.Value = cpySel.Cells.Value
        Debug.Print pstSel.Address
        wbCSV.Close False
        'ready next CSV
        fCSV = Dir
    Loop

    Application.ScreenUpdating = True
End Sub

By the way, please note my usage of code tags around my code. See the forum rules regarding how to apply them. Otherwise, one of the moderators will get you!

Originally Posted by excelatlife

Hi everyone, having trouble with one line of code. I think I'm pretty close to figuring out this macro. I'm getting the following error:
"Run-time error '438':
Object doesn't support this property or method"
on this line of code: wbMstr.wsMstr.Range("c1").End(xlDown).Offset(1, 0).Select

How can I better define my method? Thanks!

Hi excelatlife,

The cause of the error is that your C column is empty and instruction xlDown gets you to the very last row of the sheet and afterwards you want another row beyond the sheet limit - Excel starts screaming.