Microsoft Office

Microsoft Office Questions? Ask a IT Expert for Support ASAP

Ask a Computer Expert,
Get an Answer ASAP!

Microsoft Office

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

Customer Question
Hi there, You helped me...
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
Show More
Show Less
Ask Your Own Microsoft Office Question
Answered in 2 minutes by:
4/3/2013
Microsoft Office Technician: Richard, Software Specialist replied 4 years ago
Richard
Richard, Software Specialist
Category: Microsoft Office
Satisfied Customers: 41,027
Experience: Over 15 year experience resolving Microsoft Office Issues
Verified
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?

Ask Your Own Microsoft Office Question
Customer reply replied 4 years 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!


 

Microsoft Office Technician: Richard, Software Specialist 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

Ask Your Own Microsoft Office Question
Customer reply replied 4 years 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


 

Microsoft Office Technician: Richard, Software Specialist 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

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
Ask Your Own Microsoft Office Question
Customer reply 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?

Microsoft Office Technician: Richard, Software Specialist 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

Ask Your Own Microsoft Office Question
Customer reply 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.


Thanks


 

Microsoft Office Technician: Richard, Software Specialist replied 4 years 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

Ask Your Own Microsoft Office Question
Customer reply 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

Microsoft Office Technician: Richard, Software Specialist 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?
Ask Your Own Microsoft Office Question
Customer reply 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!

Microsoft Office Technician: Richard, Software Specialist replied 4 years ago
And how does it not do this in my file Ike?

That helps to understand what it is not doing.
Ask Your Own Microsoft Office Question
Customer reply 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

Microsoft Office Technician: Richard, Software Specialist 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,

Richard
Ask Your Own Microsoft Office Question
Customer reply replied 4 years ago

 


Thanks Richard.


I hope I can get it sorted

Microsoft Office Technician: Lindie-mod,
 replied 4 years ago
Lindie-mod
Category: Microsoft Office
Satisfied Customers: 5
Verified
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
Ask Your Own Microsoft Office Question
Ask Richard Your Own Question
Richard
Richard
Richard, Software Specialist
Category: Microsoft Office
Satisfied Customers: 41,027
41,027 Satisfied Customers
Experience: Over 15 year experience resolving Microsoft Office Issues

Richard is online now

A new question is answered every 9 seconds

How JustAnswer works:

  • Ask an ExpertExperts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional AnswerVia email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction GuaranteeRate the answer you receive.

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 CustomerNew 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!!!!

AlexLos 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.

GPHesperia, CA

I couldn't be more satisfied! This is the site I will always come to when I need a second opinion.

JustinKernersville, 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.

EstherWoodstock, 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.

RobinElkton, Maryland

< Previous | Next >

Meet the Experts:

jstinehelfer

jstinehelfer

Information Systems Manager

36 satisfied customers

A+ Comptia Certified computer repair

JasonJames122

JasonJames122

Computer Enthusiast

0 satisfied customers

I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business

Jess M.

Jess M.

Computer Support Specialist

701 satisfied customers

Computer Software Specialist for more than 10 years

The-PC-Guy

The-PC-Guy

Computer Manager

588 satisfied customers

20 years experience providing remote computer support

James K.

James K.

Consultant

260 satisfied customers

Technical Director of IT Company

IT Miro

IT Miro

Computer Scientist

157 satisfied customers

Bachelor's Degree in Information Technology, Microsoft Certified Professional

Chris L.

Chris L.

Support Specialist

147 satisfied customers

Certified Expert with over 10 years experience.

< Previous | Next >

Related Microsoft Office Questions
I can't recover a really important document that I was
I can't recover a really important document that I was working on … read more
bbao
bbao
Freelance IT Consultant
74 satisfied customers
I'm having trouble with creating an automatic table of
I'm having trouble with creating an automatic table of contents in Word. I think it has something to do with formatting in the document. … read more
Kris R
Kris R
IT Manager
Computing
2,648 satisfied customers
There was an update today and since I did it I can't open
There was an update today and since I did it I can't open Word. There was one for PowerPoint and Excel and they are all fine … read more
bbao
bbao
Freelance IT Consultant
74 satisfied customers
I am finishing work on a large textbook and I find I cannot
I am finishing work on a large textbook and I find I cannot open my word documents.It says I do not have access to them. This is really frightening!! I need help. … read more
RussellA
RussellA
Solutions Architect
Bachelor\u0027s Degree
123 satisfied customers
H, Pearl. I can't log in to my office 365 account. Office
H, Pearl. I can't log in to my office 365 account. … read more
bbao
bbao
Freelance IT Consultant
74 satisfied customers
When I type an e mail to a particular person, how do I
When I type an e mail to a particular person, how do I include multiple names recipients . … read more
Jess M.
Jess M.
Computer Support Specialist
Bachelor's Degree
701 satisfied customers
I have a document on one drive that wont open because the
I have a document on one drive that wont open because the file is corrupted. How can I open the document. It represents a weeks work. Would it help to use a different computer? … read more
Kris R
Kris R
IT Manager
Computing
2,648 satisfied customers
I have two cars a Hummer & Maserati I was able to access the
I have two cars a Hummer & Maserati I was able to access the Hummer, now I would like to use it on the Marz how do I do it? … read more
Kris R
Kris R
IT Manager
Computing
2,648 satisfied customers
I wrote a book and its now being published. There are many
I wrote a book and its now being published. There are many hard marks in the text which my publisher wants fixed. These occurred when i used space and enter when i shouldnt have. Iv corrected my inden… read more
George Sibiya
George Sibiya
PhD
216 satisfied customers
I have been trying to activiate or reactivate Windows 10 for
I have been trying to activiate or reactivate Windows 10 for this year. I messed up in the beginning by putting in the wrong account name. Have not been able to recover from this. I have gotten a new … read more
Bryan
Bryan
IT Consultant
8,751 satisfied customers
How do I move folders from MS Office 2013 on one PC to MS
How do I move folders from MS Office 2013 on one PC to MS 365 Personal on another PC … read more
PC_Wizzard
PC_Wizzard
IT Technician
38 satisfied customers
I downloaded Microsoft Office 365 on a different laptop. I
I downloaded Microsoft Office 365 on a different laptop. I was not able to activate it. I need to move it to a different laptop. Do I need to activate it on the other laptop 1st? … read more
John Lohr
John Lohr
Owner / Technology Specialist
Teaching Certification
504 satisfied customers
I was forced to reinstall office 2007 and now opening Excel,
I was forced to reinstall office 2007 and now opening Excel, Word, and PowerPoint I get the following: srdole32.tib with no comment as to what it is. … read more
bbao
bbao
Freelance IT Consultant
74 satisfied customers
I just downloaded outlook 2016 but cannot open it. I get the
I just downloaded outlook 2016 but cannot open it. I get the following message: "Cannot start Microsoft Outlook. Cannot open the outlook window. The set of folders cannot be opened. The outlook data f… read more
Viet - Computer Tech
Viet - Computer Tech
Computer Technican
Bachelor\u0027s Degree
953 satisfied customers
My laptop crashed with the last Windows 10 update. I had to
My laptop crashed with the last Windows 10 update. I had to buy a new laptop, but I need to recover my Microsoft 365 account. My contact information was on my old laptop. How do I recover my files? I … read more
Michelle
Michelle
Manager operations
Bachelor\u0027s Degree
1,275 satisfied customers
I had issues with slow indexing in my Outlook 2013. Went for
Hi there, I had issues with slow indexing in my Outlook 2013. Went for repair on line (after trying quick repair) - got a error message (crash) and all my Office 2013 package disappeared from my lapto… read more
Richard
Richard
Software Specialist
41,027 satisfied customers
My spreadsheet appears to be locked. It won' T let me input
My spreadsheet appears to be locked. It won' JA: What application are you using? Customer: t let me input numbers or alph JA: Have you installed any updates recently? Customer: Excel, no JA: Anything … read more
bbao
bbao
Freelance IT Consultant
74 satisfied customers
I need to retrieve my contacts and have forgotten my
I need to retrieve my contacts and have forgotten my password. … read more
Isaac Franklin
Isaac Franklin
Associate of Science in Social Sciences
971 satisfied customers
Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Show MoreShow Less

Ask Your Question

x