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?
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
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
RE: Macro for A New Workbook to collect data from several otherWorkbooks
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
http://www.excel-easy.com/examples/external-refere...
RE: Macro for A New Workbook to collect data from several otherWorkbooks
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
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
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
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.