How to Copy CSV Files to Current Workbook

I'm trying to loop through some CSV files that I'll be putting in a folder (usually around 20), and then copying them to different tabs in a master workbook. I've searched the hell out of Google trying to get the code I need to do this and putting things together from my own (apparently small amount of) knowledge. Eventually I'll have the macro do call another sub and do more, but right now I'm stuck on this simple/basic task.

One piece of code I found is below. One problem is that "CurrentRegion" doesn't copy the whole worksheet, as there is a row of separation between some of the data. I wrote the commented out line of code below that to copy the worksheet, but pasting seemed to cause issues no matter which way I did it. With the current code as is, I get an error saying "Method 'Rows' of object '_Worksheet' failed." I've been banging my head against the wall trying different things to make this work. Please tell me where I went wrong either in this code or the other one that I've also tried.

Sub ABC()
Dim sPath As String, sName As String
    Dim bk As Workbook, r As Range
    Dim r1 As Range, sh As Worksheet

    Set sh = ActiveSheet  ' this is the summary sheet
    sPath = "H:\Macros\OR Reports\Macro OR Reports\"
    sName = Dir(sPath & "*.csv")
    Do While sName <> ""
       Set bk = Workbooks.Open(sPath & sName)
       Set r = bk.Worksheets(1).Range("A1").CurrentRegion
       'bk.Worksheets(1).Cells.Copy
       Set r1 = sh.Cells(sh.Rows.Count, 1).End(xlUp)(2)
       r.Copy
       r1.PasteSpecial xlValues
       r1.PasteSpecial xlFormats
       sh.Activate
       ActiveSheet.Paste
       bk.Close SaveChanges:=False
       sName = Dir()
    Loop
End Sub

Here's the first one that I tried. This one doesn't give me an error, but instead it pastes the entire contents of the Macro into a workbook named Book1, and there's also a Book2 left open at the end of the macro running with no data at all in it.

Sub ORMaster()
    Dim lCount As Long
    Dim wbResults As Workbook
    Dim wbCodeBook As Workbook


    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False


    Set masterBook = ActiveWorkbook
    
    On Error Resume Next
        Set wbCodeBook = ThisWorkbook
            With Application.FileSearch
                .NewSearch
                'Change path to suit
                .LookIn = "H:\Macros\OR Reports\Macro OR Reports\"
                .FileType = msoFileTypeAllFiles
                'Optional filter with wildcard
                '.Filename = "Book*.xls"
                    If .Execute > 0 Then 'Workbooks in folder
                        For lCount = 1 To .FoundFiles.Count 'Loop through all
                            'Open Workbook x and Set a Workbook variable to it
                            Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                            
                            wbResults.Activate
                            ActiveSheet.Copy
                            masterBook.Activate
                            Set currentSheet = Worksheets.Add
                            currentSheet.Paste
                            
                            'DO YOUR CODE HERE
                            'ORMacro

                            wbResults.Close SaveChanges:=False
                        Next lCount
                    End If
            End With
    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
End Sub

I would be extremely appreciative of someone being able to set me straight and point me in the right direction on this.

Originally Posted by protonLeah

ForSet r1 = sh.Cells(sh.Rows.Count, 1).End(xlUp)(2)
Try:

Set r1=sh.cells(Rows.Count,1).End(xlUp)

When I try this, it gives me an error that says "Automation Error" and highlights that line of code in the debugger.

Originally Posted by JBeaucaire

As for the CurrentRegion issue, you might get better with this:

Set r = bk.Worksheets(1).UsedRange

Thanks! I believe this one will help once I can get the code to run.