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 Lindie-mod Your Own Question

Lindie-mod
Lindie-mod,
Category: Microsoft Office
Satisfied Customers: 5
6610965
Type Your Microsoft Office Question Here...
Lindie-mod 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: 1 year ago.
Category: Microsoft Office
Expert:  Richard replied 1 year 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 1 year ago.


Hi,


 


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 1 year 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 1 year ago.

Hi There,


 


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


Please see the zip file http://wikisend.com/download/273508/JA2.rar


 


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 1 year 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

http://wikisend.com/download/509958/JA.zip

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 1 year 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 1 year 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 1 year 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.


Thanks


 

Expert:  Richard replied 1 year ago.
It is no problem!

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

http://wikisend.com/download/294994/JA.zip

Customer: replied 1 year 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 1 year 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 1 year 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 1 year ago.
And how does it not do this in my file Ike?

That helps to understand what it is not doing.
Customer: replied 1 year 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 1 year 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,

Richard
Customer: replied 1 year ago.

 


Thanks Richard.


I hope I can get it sorted

Expert:  Lindie-mod replied 1 year 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!

Lindie

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional