Copy ranges between different worksheets

[a.mack123]

Hi All,

I have a spreasheet i have created to calculate asset allocation and another spreadsheet which all the calculations need to flow into.

I have written some code which you can see in the uploaded file, which is a part of the task that I want the macro to acheive.

The first part of the task I would like it to complete is open the desired workbook(i have written the code for this, however i would like to make the workbook not visible if possible) copy the cells in the range E15:L15 from the calculator.xls and paste them into the range AC:AJ in the builder.xls

The row in which it pastes to this range needs to be determined by the client which the calculator is performing for.

I have included a VLookup function in the calculator xls in cell G1 which returns the row in which to paste for the curren client. I am not sure as to how to work this into the range pasting macro.

Something else to note, is that the calculator file name will change so there may be one saved for Johm smith, which will be called Investment Policy Statement Calculator - Smith and another for the next client. All will be in that naming format. the builder.xls name or location will never change.

If anyone can help me I would be greatly appreciative.

thanks

[prefix]

for opening a workbook without it being visible, at the start of the macro do a;

Please Login or Register  to view this content.

And then at the end;

Please Login or Register  to view this content.

as for opening a workbook and writing to it and saving it you probably know this but its;

Please Login or Register  to view this content.

If you are pointing to the same workbook every time it might be smart to create a Dim, like this;

Please Login or Register  to view this content.

That way you can refer to that workbook using just the simple wbk. like this;

Please Login or Register  to view this content.

Then after the copy is complete you issue the command;

Please Login or Register  to view this content.

or False, depending on if you want to save or not. That way you will open the workbook without seeing the screen update, you'll just see 2 excelwindows open for a short time, depending on the amount of data the macro is transferring, then the workbook will close and save depending on what you told it to.

=)

[a.mack123]

Thanks for the help, any Idea on how to solve the pasting issue?