Macro for A New Workbook to collect data from several otherWorkbooks

[normm]

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?

 

[IRstuff]

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

TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529


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

 

[MortenA]

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.

 

[normm]

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.

 

[cowski]

IRStuff never mentioned copy & paste, but rather sheet references. Sheet references can be set to update when the source changes.

http://www.excel-easy.com/examples/external-references.html

 

[IRstuff]

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

Need help writing a question or understanding a reply? forum1529


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

 

[MortenA]

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

 

[normm]

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

 

[bernoullies123]

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.