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.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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