How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Richard Your Own Question
Richard, Software Specialist
Category: Microsoft Office
Satisfied Customers: 36526
Experience:  Over 15 year experience resolving Microsoft Office Issues
Type Your Microsoft Office Question Here...
Richard is online now
A new question is answered every 9 seconds

Hi there, You helped me with an excel question a few weeks

Customer Question

Hi there,
You helped me with an excel question a few weeks ago.
Can I ask a new question?
I have a macro (not really working yet) that I want to do the following:
Open all the files in a folder (one by one) and copy the data from one sheet to the macro file. The files have a reference in a cell on the sheet that matches a tab in the macro file.
Could you give me an example script that copies data from a file to a tab with the same name as the cell refered to?
Submitted: 4 years ago.
Category: Microsoft Office
Expert:  Richard replied 4 years ago.
Thank you for your question, my name is Richard.

To confirm, you want to open all Excel files in a folder, and copy ALL data from it to the main sheet (that is running the Macro)?

Or specific data?

Customer: replied 4 years ago.



an example would be if we had the macro file and 4 other files in the folder.

the files are East, West, North and South

Each one of the files has a sheet(tab) called "Data"

on the "Data" sheet is the word "North" (or south etc) in cell C2

The macro file has 4 sheets (tabs) - Norht, South, East & West.


Hopefully, the macro will open the 1st file, match the cell reference to a tab and copy the data!


Expert:  Richard replied 4 years ago.
Hi Ike

Here you go. I have the 5 files in a Zip file HERE

Make a directory under C drive called JA and put them there.

Ja1.xlsm is the one with the macro in it (sheet1)

Let me know your thoughts please

Customer: replied 4 years ago.

Hi There,


My mistake. I don't think I explained properly.

Please see the zip file


It needs to match the name in cell C2 to the sheet name. Thats the only way it knows which sheet to paste the data to


Expert:  Richard replied 4 years ago.
No problem Ike

This version reads cell C2 on each of the sheets for the file location.

And Cell C3 for the Sheet Name

If you have any difficulties at all, please do not hesitate to let me know so I can assist you further

Thank you
Customer: replied 4 years ago.

Hi, the macro does not work "Object Defined Error"

Also, I cannot see in the macro script how it knows which tab to paste the data

Am I missing something?

Expert:  Richard replied 4 years ago.
You need to make sure each sheet (North, South, East, West) has a valid File Path and Sheet Name to use, else you get this error.

Also, I cannot see in the macro script how it knows which tab to paste the data - You can define this. It is in the variable strNorth eg: for north.

Just say Range("A1") = strNorth

(replace A1 with the cell you want the data in)

Let me know please if you have any difficulties doing this.

Thank you

Customer: replied 4 years ago.

Thanks so much for your patience on this.

I have corrected the path and the ,macro runs now

Could you help me do this? I just need to see some data copy over from the north spreadsheet to the north tab, but am not very good at this.



Expert:  Richard replied 4 years ago.
It is no problem!

Can you check this version. you will see my comments in the code

Customer: replied 4 years ago.

Not yet giving me what I need. It needs to ref the cell to get the sheet name. Reason is because there will be loads of files. You have it hardcoded in the macro.

I could not see where to put the file path name either

Expert:  Richard replied 4 years ago.
Cell C2 you put in the File Path and Name

Are you able to do this?

The Cell is different. What exactly are you trying to copy?
Customer: replied 4 years ago.

Hi there,

The aim is to copy the data from many files to one main file.

For each file, there is a corresponding tab in the main file

In the file called North, there is the word "North" in cell C2. The macro needs to reference that cell and only that cell to know that all the data from the North file should be copied to the tab called North in the macro file.

I know this is painfull but we will get there!

Expert:  Richard replied 4 years ago.
And how does it not do this in my file Ike?

That helps to understand what it is not doing.
Customer: replied 4 years ago.

I can see what your file is doing now. But there are a couple of things I'm not sure about.

It only seems to copy the word North (I tried to get it to copy more than the word, like the whole page but it would not work)

It has "North" hard coded in the script. This would mean copying the statements for each file. I thought a loop would be better, hence why it should read the name from the "North" file

Expert:  Richard replied 4 years ago.
ok, I am not really sure what you exactly require.

It will be best if I opt out and allow another expert to take over that can better grasp it.

I am sorry about this Ike,

Customer: replied 4 years ago.


Thanks Richard.

I hope I can get it sorted

Expert:  Lindie-mod replied 4 years ago.
Hi, I’m a moderator for this topic. It seems the Professional has left this conversation. This happens occasionally, and it's usually because the Professional thinks that someone else might be a better match for your question. I've been working hard to find a new Professional to assist you right away with your Excel issue, but sometimes finding the right Professional can take a little longer than expected.

I wonder whether you're OK with continuing to wait for an answer. If you are, please let me know and I will continue my search. If not, feel free to let me know and I will cancel this question for you. Thank you!


Related Microsoft Office Questions