switch between open workbooks within macro

in this code a target file is is opened and saved 3 times which is unnnecessary but I dont know how to change it. After the first instance of "paste" in workbook opened by the macro, rather than close it I would just like to instruct the macro to activate the previous worksheet before continuing.

This system was OK with a small test file but unusable where it is needed

Any help appreciated

I tried with this method at line 21 Windows("c:\users\nigel.MACROOMHAULAGE\My Documents\vehicle costing.xlsm").Activate but no success

Please Login or Register  to view this content.

I have tried to use the ThisWorkbook.Activate command to refer to the source file (this is where the code is)

and I tried to set the target file by using

Please Login or Register  to view this content.

but i get an error at this line

I thought that once the code activated the worksheet in the "target file" it would stay there and I could call back to the source file using the Thisworkbook.activate command

In reality the code completes but after the first paste and return to source file no other data is transferred and the wrong file (the source file) closes. So the first instance of the target file is not remaing active.

Any ideas because code would be ideal to use in this format

Try this, it should provide some ideas to help you.

I've taken your code and modified it.
I'm still learning myself so i'm sure someone else could refine it much more than me.

I haven't tested it but this method has worked for me on other projects.

Please Login or Register  to view this content.

Although I just tested the code below on two seperate sheets and was able to set values of cells in the active sheet from cells on a sheet in another workbook.

Please Login or Register  to view this content.

You could substitute in

Please Login or Register  to view this content.

and use a For Next loop to interate through the range and copy the values from the selected range to the destination.

Forgot to add that the "With" part of the code in your first post is not necessary. The "With" statement allows you to access the methods and properties of the object included in the "With" statement by starting the code line with just a period, saving having to tye the objects name over and over again.

The "With" statement allows you to put this sort of code together.

Please Login or Register  to view this content.

Which is the same as

Please Login or Register  to view this content.