×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Macro for A New Workbook to collect data from several otherWorkbooks

Macro for A New Workbook to collect data from several otherWorkbooks

Macro for A New Workbook to collect data from several otherWorkbooks

(OP)
I am fairly new to VBA and have a simple problem:

I have a Excel file(Workbook 1) where in Worksheet 1 I have data stored in Range A1:K20. The summation of the data is stored in Row 30.
ie Rows 21 to 29 are blank.

I have another Excel File (Workbook 2) where the data is stored in Range A1:K38. The summation of the data in stored in Row 55.
ie Rows 39 to 54 are blank.

I want to create a new Workbook 6 and write a macro such that in this new Workbook 6 ( Worksheet 1) the 1st row will contain the data of Row 30 Workbook 1. The 2nd Row will contain the data in Row 55 of Workbook 2 and so on.

Can you help please?

And also if I am looking for a reference book on VBA that deals with lots of simple example problems like this, can you reccomend one?

RE: Macro for A New Workbook to collect data from several otherWorkbooks

Why do you need VBA? You can do an intrasheet reference directly.

TTFN
FAQ731-376: Eng-Tips.com Forum Policies

Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers


Of course I can. I can do anything. I can do absolutely anything. I'm an expert!
There is a homework forum hosted by engineering.com: http://www.engineering.com/AskForum/aff/32.aspx

RE: Macro for A New Workbook to collect data from several otherWorkbooks

You should try the macro recorder function and then analyse the code that it generates. From this you could change the code to make a more generic macro that would operate at any worksbook. The recorded macro would you the specific file name, you have to change that to something that references e.g. a number or just changes from one open to the next.

RE: Macro for A New Workbook to collect data from several otherWorkbooks

(OP)

Thanks both of you for your response.

IR
My main aim is to collect information from several source Workbooks and deposit the information in specific rows in a target file. As you mention this can easily be done by Excel Copy and Paste. But I thought if done by a Macro, then even if the source file changes in future, the target file will be automatically updated. I will not need to do Copy and Paste continuously to update the information as would be case if I simply do it by Excel.


MortenA

I will try by recording the way you suggest. I want the macro to extract the information from the last row of the source files. But the last row number may change in future because of new data. I will try your way and see how to describe the last row, even if the row number changes in future.

RE: Macro for A New Workbook to collect data from several otherWorkbooks

That was precisely what I was referring to; it's the opposite of the coin where I keep getting these annoying messages about missing links that can't be updated. The external links require no programming and will update, hopefully, every time you open the sheet.

TTFN
FAQ731-376: Eng-Tips.com Forum Policies

Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers


Of course I can. I can do anything. I can do absolutely anything. I'm an expert!
There is a homework forum hosted by engineering.com: http://www.engineering.com/AskForum/aff/32.aspx

RE: Macro for A New Workbook to collect data from several otherWorkbooks

if you ow changes from run to run then its more complicated.

In that case i could still use a recorded macro as the starting point but then you would have to add a do repeat loop that would test if the cell was empty and then move right until you get to the first cell with a content. Once there the perform the copy part.

Best regards, Morten

RE: Macro for A New Workbook to collect data from several otherWorkbooks

(OP)

Thanks guys for all your helpful responses.
The Excel website in cowski's response seem amazing and it is all for free.
Best regards


RE: Macro for A New Workbook to collect data from several otherWorkbooks

Another more elegant solution is to use Power Query in Excel. It is an free add-on from Microsoft. It would have to be done in a few simple steps:
1) Query the workbook
2) Keep the row you want
3) Query and keep the row you want on the other files
4) Append the queries
5) Load to Table

All steps done by clicking with the mouse. No code to write.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources