Adjust existing macro to pick up specific named worksheet and copy to named range

You could try this.
It assumes that the "Master" has been saved in the same folder.
Not tested so make sure to try with copies of your file(s)

Please Login or Register  to view this content.

Hi jolivanes,

Thanks for your code. I'm trying my best to understand it. The macro as I currently have it is housed in the ThisWorkbook. It then prompts the user to select the Summary file which becomes Workbooks(2). The macro then prompts the user to identify where the actual external files are located. The results in these external files are supposed to be copied into Workbooks(2). I'm not certain I see how that works in your code? Can you clarify?

I thought I answered as per your explanation. I did not look at your code as it does not do what you want it to do.
I'll have a look later when I have some time again.
Sorry about that.

So here's what I have now. Its close but not quite doing what I need.

What its not doing right:

1. Its only pulling opening the first workbook in the folder rather than looking for the file indicated in myFile.
2. its also not looping through the list of cells in I
3. Its not renaming the copied worksheet to the i value

What it is doing right:

1. Copying the correct worksheet from the newly opened workbook to the correct position in Workbooks(2)

Please Login or Register  to view this content.

Any ideas?

Thanks!

I don't get your logic.
Why would you "run through each workbook in a folder" to see if it's name is the same as in one of the cells in a range. (See Post #1, #1 and #2)
Why not use the cells in the range to open that particular workbook?
I can't make head or tails out of the code you have in Post #5 either.

Do you want to open all the files in a range in your active (call it Master for now) workbook and copy the used range from a sheet named "ABC" in that opened workbook into your Master?
I am terrible reading/understanding someone's code that does not work.
Either explain properly what you want to achieve or maybe someone else understands the code.

I tried to get your code from Post #5 to work but wasn't able to.
Could you go through the motions and explain what should, or what you want to, happen at each line.

Try this. It might be a start of what you're after.
You have to change all the references like sheet names ans cells ranges as required.

Please Login or Register  to view this content.

Please Login or Register  to view this content.