PDA

View Full Version : [SOLVED] Linking macro in one sheet to anothwer sheet



blackie42
11-21-2016, 08:05 AM
Workbook is called 'Transfers' and has several tabs that need reformatting, example tab named 'Bank'

The code for reformatting Bank tab (called Reformat1) has been done and works, code is in another separate spread-sheet called
ReformatBank - i.e. as long as WB called 'Transfers' is open and the tab named 'Bank' is visible the code runs and
formats as needed.


I really need a button on the ReformatBank WB that checks to see if the WB Transfers is open and then uses the Reformat1 code to
sort the bank tab as needed.

Any ideas as to how to link the 2 books together.

thanks
Jon

blackie42
11-21-2016, 09:23 AM
So I open 'Transfers' WB and select tab 'Bank'

Open the other spread-sheet (ReformatBank) and run the following code from a button

With Workbooks("Transfers.xlsx")
Worksheets("Bank").Select - bugs out on this line
reformatting code (works ok on it's own (i.e. running it from the module itself without the above references to Transfers & Bank))

Hopefully someone understands what I'm trying to do.

Once I have it right I will then use this to reformat other WBs & sheets

many thanks
Jon

jolivanes
11-21-2016, 12:32 PM
To go between open workbooks.
Use Application.ScreenUpdating to prevent screen flickering.
Code in this example is run from Book11111.xlsm.

Sub Try_This()
Windows("Book22222.xlsm").Activate
'Do here what needs doing
Windows("Book11111.xlsm").Activate
End Sub

blackie42
11-22-2016, 05:26 AM
Thanks Jolivanes,

Works a treat

regards
Job