Do Until Loop - Transferring data from several workbooks to a Master workbook

[GalsenPAP]

Hello all,

I am trying to transfer data from several workbooks (8 at the moment) to a single Master Workbook.

All the headers are the same in all workbooks, the only difference are the number of rows, thus the use of Do Until to identify the last populated row

I have tried using Do Until Loop but I always end up copying and pasting in the same row in my Master workbook.

Here is the code, I see where the problem is but I cannot correct it. (my guess is Range C9 in the loop, it should be dynamic instead but i cant figure out the code to do this)

PS: I'm a beginner in VBA and have always struggled with it

Please Login or Register  to view this content.

[tehneXus]

This will not loop each row but get all data into an array at once and paste it into your master sheet at once starting at the next free row

Please Login or Register  to view this content.

[GalsenPAP]

Thank you

it works but the problem is that it just copies and pastes values whereas I have formulas in each workbook and they are the same formulas i need to use in the Master workbook

to make it clearer, each workbook is a portfolio of Financial Securities (bonds)

the Master workbook is a composite that's made up of 7 portfolios (workbooks) so i need to calculate various weighted averages in the Master workbook

that's the reason i was doing the transfer with Do Until loop, because I could keep the formulas and make the calculations in the Master workbook

thnaks for your help

[ankitparate]

you need to create the template of that worksheet and call that worksheet and then import the values.

[tehneXus]

Hi,

in the code form post #2 replace ".Value" with ".FormulaR1C1", you have to do this in 2 places. This will keep the formulas and adjust them to the place they are copied.

[arlu1201]

GalsenPap,

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.

[GalsenPAP]

hello,

it is working just fine, thank you very much

as this was done in a testing mode I have tried to implement it at work but now I get an error msg, mainly that the macro can't find the workbook

trying to replace this

Please Login or Register  to view this content.

this is the one that was submitted above and works

with this

Please Login or Register  to view this content.

basically any other file on my computer

if I just change the address why won't the macro just recognize the new path and apply the same process ?

thanks in advance

[tehneXus]

Hi,

when the error is saying that it cannot find the workbook you specified in the path it's not due to the macro but the file does not exist or cannot be accessed due to restrictions on windows. e.g. if you are not logged in as admin on your machine but you try to view a desktop of another user.

If it works with SOME files but not with all, there is some rights issue I suppose, or sth with the files, or the path is not correct