How to Copy CSV Files to Current Workbook

[sweetnumb]

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.

[protonLeah]

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

Please Login or Register  to view this content.

[JBeaucaire]

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

Please Login or Register  to view this content.

[sweetnumb]

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.

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.

[arlu1201]

Do you have a sample file that you can upload?

[JBeaucaire]

This is how I would write this, does it work any better for you?

Please Login or Register  to view this content.