Auto transfer sheet from one workbook to another

[jimpatel1993]

Hi,

I have below code for auto transfering "Sheet1" from workbook1(WB1) to "Sheet1" in workbook2(WB2). Everytime i run this macro i wanted to transfer data from sheet1 (WB1) to sheet1(WB2) by deleting previous data and replacing with new one. Further after copying and pasting data in WB2 i would like to run the macro in that WB2 automatically please. Can i get any support in this macro please?

Thanks,
Jim

[frabulator]

As outlined here, you can use the code below to run a macro inside of another workbook. That workbook needs to be opened, however.

Please Login or Register  to view this content.

[frabulator]

As far as clearing all data in your WB2 before transferring a sheet from WB1 in with the same name, you can either:

A) Create a new sheet in WB2, delete the Sheet1 page out of WB2, transfer Sheet1 from WB1 to WB2, delete the new sheet you created before from WB2. We do this so you will always have at least one sheet in the workbook.
B) Change the name of Sheet1 in WB2, transfer the page from WB1 into WB2.
C) Clear out the data in WB2 Sheet1 [ Sheets("Sheet1").Cells.Clear ], copy your range from WB1 and paste into WB2.

There is probably a more elegant way of doing this, but all of these are pretty simple to accomplish with a quick google search.

[jimpatel1993]

Thanks for your reply about running macro for different workbook. I am having issue in running below macro as its not working properly. May i know where i am going wrong please? As i have mentioned in my first comment i am trying to transfer data from WB1 to WB2

Thanks a lot for your help

[frabulator]

Thanks for your reply about running macro for different workbook. I am having issue in running below macro as its not working properly. May i know where i am going wrong please? As i have mentioned in my first comment i am trying to transfer data from WB1 to WB2

Thanks a lot for your help

If the workbooks are opened, it should be as easy as declaring which book you want to access when.

Example:

Please Login or Register  to view this content.

If the workbook is not opened, then you will need to first open the workbook. You can do this in the background (ie: not visible for the user) by using Application.ScreenUpdating = False. Just make sure your set it back to true when you are done.

[jimpatel1993]

Thanks a lot for your kind help