RE: [ExcelVBA] Swap Active Workbooks

 

Since you will only have 2 workbooks open you can probably use

Workbooks(2) and Workbooks(3) to refer to them. Workbooks(1) is normally
the hidden Personal.xlsb

So you could use

Workbooks(2).Activate.

However ... don't!

Set a variable for each workbook

Dim t as Workbook (the target workbook)
Dim s as Workbook (the source workbook)

Then set each to the book concerned. Even if you use the above strategy to
identify the workbooks in the first place

Assume that the Target workbook will always be active when the macro is run.

Set t = ActiveWorkbook
Set s = Workbooks(5-t.index)

Now just use t and s to address each workbook.
Do not activate them or select ranges on them. Work direct with the ranges,
eg

t.worksheets("Sheet1").range("A3:F49")=s.
worksheets("Sheet1").range("A3:F49")

hth

Best Regards
David Grugeon
Excel VBA Group Moderator

-----Original Message-----
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On Behalf
Of David
Sent: Thursday, 9 August 2012 7:25 AM
To: ExcelVBA@yahoogroups.com
Subject: [ExcelVBA] Swap Active Workbooks

Hi

I am working on a macro to copy data from one spreadsheet to another. All
file names are variable, but the structure of the data is not.

My goal is to have the macro run and simply jump back and forth between the
two files to process the data. These will be the only workbooks opened.

Is there a line of code to swap focus between active workbooks (almost a
relative reference) without using a filename?

Thanks
David

------------------------------------

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
here: http://www.mousetrax.com/techtrax to enter the ezine, then search the
ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------Yahoo! Groups Links

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (2)

Recent Activity:

• New Members 4

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___