We help IT Professionals succeed at work.

More Help with Macro to copy data from multiple tabs in multiple workbooks to one spreadsheet

DMKetcher
DMKetcher asked
on
359 Views
Last Modified: 2012-03-26
Dave (dlmille) helped me with this so maybe it would be quickest if he responded.

The solution you provided worked with a piece of what I am attempting to do. In reality I have seven tabs of data I need to move into the spreadsheet. When I originally created the macro it was too large to handle all seven tabs so I broke it into three macros and called 2 from 1, 3 from 2 etc. This worked great when working one line at a time. After I got your solution for opening multiple workbooks I applied the logic to the three macros I developed executing each one with a different control key (ctl/r, ctl/s, ctl/t). Ideally I would like to be able to execute using ctl/r and loop through all of the tabs but I thought it would take me too long to build and took the easy way out.

The problem: The Macro 1 works fine. Macro 2 and 3 are not incrementing the destination row (X = rDest.Row) consistently. I have done extensive testing and cannot find a pattern. I am attaching the code and workbook folder.

Thanks again for your expert help!
Debra-Wicker1.xlsx
MCU-File-Review-Summary-Detail-M.xlsm
Comment
Watch Question

DMKetcherConsultant

Author

Commented:
If you have suggestions for making it more efficient (1 macro) instead of three just let me know. To give you and idea of the business problem -- I am working with a university that has to reconstruct title IV (Federal student aid) and the years covered are from 2006 - 2012 but most of the students were involved in one or two of those years. So there will be tabs that do not have any data. At least one tab in each workbook has data but it will vary by student. Hope this helps
Most Valuable Expert 2012
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
DMKetcherConsultant

Author

Commented:
Works beautifully. Thank you!
Most Valuable Expert 2012
Top Expert 2012

Commented:
Great!  Let me know if you need further assistance.

Dave
DMKetcherConsultant

Author

Commented:
Problem -- I got it working by copying your code and using my files and then I tried to apply the changes to another version of the spreadsheet that has slightly different formatting but same logic as the one you worked on and am getting the following error message:

"Wrong number of arguments or invalid property assignment" on the code

Call DataTransfer(srcWkb,rDest)

Do you know what could be causing this?
Most Valuable Expert 2012
Top Expert 2012

Commented:
You need to change the declarations in each of the dataTransfer macros, I believe.

Ensure you copy all the macros over, and you should be ok - from all the modules.

Dave
DMKetcherConsultant

Author

Commented:
Not understanding... I need to have uniquely named declarations for each dataTransfer macro - dataTransfer, dataTransfer2, dataTransfer3? Is that correct?
Most Valuable Expert 2012
Top Expert 2012

Commented:
No - you need to copy all the macro modules from what I uploaded to your new workbook, eliminating any that they replaced.

If you only had data transfer macros, just delete those modules and copy mine over.

Dave
DMKetcherConsultant

Author

Commented:
Excellent Plus!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.