I know how to name an active workbook with the following command:
Set Form7 = ActiveWorkbook

In this case I named the active workbook "Form7" which permits me to
activate and interact with the workbook.

Is it possible to name more than one active workbook (with different
names) at the same time and be able to activate and interact with them?
Apparently the way I use the command, the most recent workbook is the
active workbook and previous active workbooks that were given names are
no longer valid.


--
grantj
------------------------------------------------------------------------
grantj's Profile: http://www.excelforum.com/member.php...o&userid=35021
View this thread: http://www.excelforum.com/showthread...hreadid=548972

set Form1=WorkBooks("Something.xls")

HTH
--
AP

"grantj" a écrit dans
le message de news: ...

I know how to name an active workbook with the following command:
Set Form7 = ActiveWorkbook

In this case I named the active workbook "Form7" which permits me to
activate and interact with the workbook.

Is it possible to name more than one active workbook (with different
names) at the same time and be able to activate and interact with them?
Apparently the way I use the command, the most recent workbook is the
active workbook and previous active workbooks that were given names are
no longer valid.


--
grantj
------------------------------------------------------------------------
grantj's Profile:
http://www.excelforum.com/member.php...o&userid=35021
View this thread: http://www.excelforum.com/showthread...hreadid=548972

Will this command work even if I don't know the name of the active
workbook (something.xls)? The user will have the ability to rename
the workbook prior to using the macro.


--
grantj
------------------------------------------------------------------------
grantj's Profile: http://www.excelforum.com/member.php...o&userid=35021
View this thread: http://www.excelforum.com/showthread...hreadid=548972

That's not the case though. Once you have set an object variable it works
until that object no longer exists. It doens't even have to be the active
workbook - as long as it is a reference to a workbook. Making another
workbook active should not stop the variable from referring to the original
workbook unless you reset it to the new one.

Jeff

"grantj" wrote in
message ...

I know how to name an active workbook with the following command:
Set Form7 = ActiveWorkbook

In this case I named the active workbook "Form7" which permits me to
activate and interact with the workbook.

Is it possible to name more than one active workbook (with different
names) at the same time and be able to activate and interact with them?
Apparently the way I use the command, the most recent workbook is the
active workbook and previous active workbooks that were given names are
no longer valid.


--
grantj
------------------------------------------------------------------------
grantj's Profile:
http://www.excelforum.com/member.php...o&userid=35021
View this thread: http://www.excelforum.com/showthread...hreadid=548972

The Workbooks collection represents the whole of currently opened worbooks.

If you want to reference the 1st opened workbook, you may write:
set wb1 = Workbooks(1)

That does not make much sense to me

HTH
--
AP

"grantj" a écrit dans
le message de news: ...

Will this command work even if I don't know the name of the active
workbook (something.xls)? The user will have the ability to rename
the workbook prior to using the macro.


--
grantj
------------------------------------------------------------------------
grantj's Profile:
http://www.excelforum.com/member.php...o&userid=35021
View this thread: http://www.excelforum.com/showthread...hreadid=548972

This is how I have it set-up (and doesn't work):

(The user begins in a workbook which is named something by the user.
do not know the name. I want to name it "Compass3")
Set Compass3 = ActiveWorkbook

(next the user willl open a new file of which I name it "Form7". Thi
works fine)
Application.FindFile
Set Form7 = ActiveWorkbook

(After performing some operations, the "Form7" workbook is closed.
This works fine)
Form7.Activate
Application.CutCopyMode = False
Form7.Close True

(now when I activate "Compass3". I get an error: Object Required)
Compass3.Activate

Any suggestions??

Jeff Standen Wrote:
That's not the case though. Once you have set an object variable i
works
until that object no longer exists. It doens't even have to be th
active
workbook - as long as it is a reference to a workbook. Making another
workbook active should not stop the variable from referring to th
original
workbook unless you reset it to the new one.

Jeff

"grantj" wrot
in
message ...

I know how to name an active workbook with the following command:
Set Form7 = ActiveWorkbook

In this case I named the active workbook "Form7" which permits me to
activate and interact with the workbook.

Is it possible to name more than one active workbook (with different
names) at the same time and be able to activate and interact wit
them?
Apparently the way I use the command, the most recent workbook i
the
active workbook and previous active workbooks that were given name
are
no longer valid.


--
grantj

------------------------------------------------------------------------
grantj's Profile:
http://www.excelforum.com/member.php...o&userid=35021
View this thread
http://www.excelforum.com/showthread...hreadid=548972


--
grant
-----------------------------------------------------------------------
grantj's Profile: http://www.excelforum.com/member.php...fo&userid=3502
View this thread: http://www.excelforum.com/showthread.php?threadid=54897

Is this all within the same procedure? If the first one is set then the code
stops, it will lose the reference as it isn't running anymore. Can you post
the relevant code as this should most definitely work. Try this to
demonstrate:

Sub test()
Dim a As Workbook
Dim b As Workbook

Set a = Workbooks.Add
Set b = Workbooks.Add
a.Activate
Call MsgBox("First one")
b.Activate
Call MsgBox("Second one")
a.Activate
Call MsgBox("First one again")
b.Activate
Call MsgBox("And so on")
a.close false
b.close false
End Sub

"grantj" wrote in
message ...

This is how I have it set-up (and doesn't work):

(The user begins in a workbook which is named something by the user. I
do not know the name. I want to name it "Compass3")
Set Compass3 = ActiveWorkbook

(next the user willl open a new file of which I name it "Form7". This
works fine)
Application.FindFile
Set Form7 = ActiveWorkbook

(After performing some operations, the "Form7" workbook is closed.
This works fine)
Form7.Activate
Application.CutCopyMode = False
Form7.Close True

(now when I activate "Compass3". I get an error: Object Required)
Compass3.Activate

Any suggestions??

Jeff Standen Wrote:
That's not the case though. Once you have set an object variable it
works
until that object no longer exists. It doens't even have to be the
active
workbook - as long as it is a reference to a workbook. Making another
workbook active should not stop the variable from referring to the
original
workbook unless you reset it to the new one.

Jeff

"grantj" wrote
in
message ...

I know how to name an active workbook with the following command:
Set Form7 = ActiveWorkbook

In this case I named the active workbook "Form7" which permits me to
activate and interact with the workbook.

Is it possible to name more than one active workbook (with different
names) at the same time and be able to activate and interact with
them?
Apparently the way I use the command, the most recent workbook is
the
active workbook and previous active workbooks that were given names
are
no longer valid.


--
grantj

------------------------------------------------------------------------
grantj's Profile:
http://www.excelforum.com/member.php...o&userid=35021
View this thread:
http://www.excelforum.com/showthread...hreadid=548972



--
grantj
------------------------------------------------------------------------
grantj's Profile:
http://www.excelforum.com/member.php...o&userid=35021
View this thread: http://www.excelforum.com/showthread...hreadid=548972

Almost but not quite. The user begins by running a macro in an existin
workbook. I do not know the name of this workbook, but I want to assig
a name "Compass3" and activate this workbook after the macro open
another workbook, assigns a name of "Form7" and then closes "Form7".

Jeff Standen Wrote:
Is this all within the same procedure? If the first one is set then th
code
stops, it will lose the reference as it isn't running anymore. Can yo
post
the relevant code as this should most definitely work. Try this to
demonstrate:

Sub test()
Dim a As Workbook
Dim b As Workbook

Set a = Workbooks.Add
Set b = Workbooks.Add
a.Activate
Call MsgBox("First one")
b.Activate
Call MsgBox("Second one")
a.Activate
Call MsgBox("First one again")
b.Activate
Call MsgBox("And so on")
a.close false
b.close false
End Sub

"grantj" wrot
in
message ...

This is how I have it set-up (and doesn't work):

(The user begins in a workbook which is named something by the user.
I
do not know the name. I want to name it "Compass3")
Set Compass3 = ActiveWorkbook

(next the user willl open a new file of which I name it "Form7".
This
works fine)
Application.FindFile
Set Form7 = ActiveWorkbook

(After performing some operations, the "Form7" workbook is closed.
This works fine)
Form7.Activate
Application.CutCopyMode = False
Form7.Close True

(now when I activate "Compass3". I get an error: Object Required)
Compass3.Activate

Any suggestions??

Jeff Standen Wrote:
That's not the case though. Once you have set an object variable it
works
until that object no longer exists. It doens't even have to be the
active
workbook - as long as it is a reference to a workbook. Makin
another
workbook active should not stop the variable from referring to the
original
workbook unless you reset it to the new one.

Jeff

"grantj"
wrote
in
message ...

I know how to name an active workbook with the following command:
Set Form7 = ActiveWorkbook

In this case I named the active workbook "Form7" which permits m
to
activate and interact with the workbook.

Is it possible to name more than one active workbook (wit
different
names) at the same time and be able to activate and interact with
them?
Apparently the way I use the command, the most recent workbook is
the
active workbook and previous active workbooks that were give
names
are
no longer valid.


--
grantj


------------------------------------------------------------------------
grantj's Profile:
http://www.excelforum.com/member.php...o&userid=35021
View this thread:
http://www.excelforum.com/showthread...hreadid=548972



--
grantj

------------------------------------------------------------------------
grantj's Profile:
http://www.excelforum.com/member.php...o&userid=35021
View this thread
http://www.excelforum.com/showthread...hreadid=548972


--
grant
-----------------------------------------------------------------------
grantj's Profile: http://www.excelforum.com/member.php...fo&userid=3502
View this thread: http://www.excelforum.com/showthread.php?threadid=54897

My code refers to 2 workbooks at the same time so it is possible - can you
post your code?

Jeff

"grantj" wrote in
message ...

Almost but not quite. The user begins by running a macro in an existing
workbook. I do not know the name of this workbook, but I want to assign
a name "Compass3" and activate this workbook after the macro opens
another workbook, assigns a name of "Form7" and then closes "Form7".

Jeff Standen Wrote:
Is this all within the same procedure? If the first one is set then the
code
stops, it will lose the reference as it isn't running anymore. Can you
post
the relevant code as this should most definitely work. Try this to
demonstrate:

Sub test()
Dim a As Workbook
Dim b As Workbook

Set a = Workbooks.Add
Set b = Workbooks.Add
a.Activate
Call MsgBox("First one")
b.Activate
Call MsgBox("Second one")
a.Activate
Call MsgBox("First one again")
b.Activate
Call MsgBox("And so on")
a.close false
b.close false
End Sub

"grantj" wrote
in
message ...

This is how I have it set-up (and doesn't work):

(The user begins in a workbook which is named something by the user.
I
do not know the name. I want to name it "Compass3")
Set Compass3 = ActiveWorkbook

(next the user willl open a new file of which I name it "Form7".
This
works fine)
Application.FindFile
Set Form7 = ActiveWorkbook

(After performing some operations, the "Form7" workbook is closed.
This works fine)
Form7.Activate
Application.CutCopyMode = False
Form7.Close True

(now when I activate "Compass3". I get an error: Object Required)
Compass3.Activate

Any suggestions??

Jeff Standen Wrote:
That's not the case though. Once you have set an object variable it
works
until that object no longer exists. It doens't even have to be the
active
workbook - as long as it is a reference to a workbook. Making
another
workbook active should not stop the variable from referring to the
original
workbook unless you reset it to the new one.

Jeff

"grantj"
wrote
in
message ...

I know how to name an active workbook with the following command:
Set Form7 = ActiveWorkbook

In this case I named the active workbook "Form7" which permits me
to
activate and interact with the workbook.

Is it possible to name more than one active workbook (with
different
names) at the same time and be able to activate and interact with
them?
Apparently the way I use the command, the most recent workbook is
the
active workbook and previous active workbooks that were given
names
are
no longer valid.


--
grantj


------------------------------------------------------------------------
grantj's Profile:
http://www.excelforum.com/member.php...o&userid=35021
View this thread:
http://www.excelforum.com/showthread...hreadid=548972



--
grantj

------------------------------------------------------------------------
grantj's Profile:
http://www.excelforum.com/member.php...o&userid=35021
View this thread:
http://www.excelforum.com/showthread...hreadid=548972



--
grantj
------------------------------------------------------------------------
grantj's Profile:
http://www.excelforum.com/member.php...o&userid=35021
View this thread: http://www.excelforum.com/showthread...hreadid=548972

Here is the code. Is there a way to attach the Excel workbooks bein
referenced?

Sub Copy_Form_7()

Set Compass3 = ActiveWorkbook

If MsgBox("This Macro will copy CFC Form 7 data into the COMPASS
program. Do you want to continue?", vbYesNo) = vbYes Then
Workbooks.Open Filename:="C:\Compass3\Compass Form7.xls"
UpdateLinks:=3
Application.Run "'Compass Form7.xls'!OpenFormSevenFile"
End If

End Sub

Sub OpenFormSevenFile()

MsgBox ("Select and open a CFC Form 7 file (short or long form).")
Application.FindFile
Set Form7 = ActiveWorkbook

' Copy Statement of Operations
Sheets("Page 1").Select
Range("A:G").Select
Selection.Copy
Windows("Compass Form7.xls").Activate
Sheets("Page 1").Visible = True
Sheets("Page 1").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Page 1").Visible = False

' Copy Balance Sheet
Form7.Activate
Sheets("Page 2").Select
Cells.Select
Selection.Copy
Windows("Compass Form7.xls").Activate
Sheets("Page 2").Visible = True
Sheets("Page 2").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Page 2").Visible = False

' Close Form 7 workbook
Form7.Activate
Application.CutCopyMode = False
Sheets("Page 1").Activate
Range("A1").Select
Form7.Close False

copy_Years

End Sub




Sub copy_Years()

' Copy the first future year into "Compass Form 7" spreadsheet
' to determine where to paste the Form 7 data
Compass3.Activate
Sheets("Balance Sheet Information").Select
Range("AE5").Select
Selection.Copy
Windows("Compass Form7.xls").Activate
Sheets("Sheet1").Select
Range("E4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False

If Range("Year") = 1 Then
Copy_Year_1
Else
If Range("Year") = 2 Then
Copy_Year_2
Else
If Range("Year") = 3 Then
Copy_Year_3
Else
End If
End If
End If

End Sub




Sub Copy_Year_1()

Windows("Compass Form7.xls").Activate
Sheets("Workhorse").Visible = True
Sheets("Workhorse").Select
Range("C9:C17").Select
Selection.Copy
Compass3.Activate
Sheets("Expense Information").Select
Range("AE25").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False

Windows("Compass Form7.xls").Activate
Sheets("Workhorse").Select
Range("C19:C25").Select
Selection.Copy
Compass3.Activate
Sheets("Expense Information").Select
Range("AE35").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Range("AE25").Select

Compass3.Activate
Application.CutCopyMode = False
Sheets("General Information").Select
Range("K9").Select

Windows("Compass Form7.xls").Activate
Sheets("Workhorse").Visible = False
Windows("Compass Form7.xls").Close True

End Sub




Sub Copy_Year_2()

Windows("Compass Form7.xls").Activate
Sheets("Workhorse").Visible = True
Sheets("Workhorse").Select
Range("C9:C17").Select
Selection.Copy
Compass3.Activate
Sheets("Expense Information").Select
Range("AF25").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False

Windows("Compass Form7.xls").Activate
Sheets("Workhorse").Select
Range("C19:C25").Select
Selection.Copy
Compass3.Activate
Sheets("Expense Information").Select
Range("AF35").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Range("AF25").Select

Compass3.Activate
Application.CutCopyMode = False
Sheets("General Information").Select

Windows("Compass Form7.xls").Activate
Sheets("Workhorse").Visible = False
Windows("Compass Form7.xls").Close True
Range("K9").Select
End Sub




Sub Copy_Year_3()

Windows("Compass Form7.xls").Activate
Sheets("Workhorse").Visible = True
Sheets("Workhorse").Select
Range("C9:C17").Select
Selection.Copy
Compass3.Activate
Sheets("Expense Information").Select
Range("AG25").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False

Windows("Compass Form7.xls").Activate
Sheets("Workhorse").Select
Range("C19:C25").Select
Selection.Copy
Compass3.Activate
Sheets("Expense Information").Select
Range("AG35").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("AE25").Select

Compass3.Activate
Application.CutCopyMode = False
Sheets("General Information").Select

Windows("Compass Form7.xls").Activate
Sheets("Workhorse").Visible = False
Windows("Compass Form7.xls").Close True
Range("K9").Select
End Sub



Jeff Standen Wrote:
That's not the case though. Once you have set an object variable it
works
until that object no longer exists. It doens't even have to be the
active
workbook - as long as it is a reference to a workbook. Making another
workbook active should not stop the variable from referring to the
original
workbook unless you reset it to the new one.

Jeff

"grantj" wrote
in
message ...

I know how to name an active workbook with the following command:
Set Form7 = ActiveWorkbook

In this case I named the active workbook "Form7" which permits me to
activate and interact with the workbook.

Is it possible to name more than one active workbook (with different
names) at the same time and be able to activate and interact with
them?
Apparently the way I use the command, the most recent workbook is
the
active workbook and previous active workbooks that were given names
are
no longer valid.


--
grantj

------------------------------------------------------------------------
grantj's Profile:
http://www.excelforum.com/member.php...o&userid=35021
View this thread:
http://www.excelforum.com/showthread...hreadid=548972



--
grantj
------------------------------------------------------------------------
grantj's Profile: http://www.excelforum.com/member.php...o&userid=35021
View this thread: http://www.excelforum.com/showthread...hreadid=548972

"grantj" wrote in message
...

Almost but not quite. The user begins by running a macro in an existing
workbook. I do not know the name of this workbook, but I want to assign
a name "Compass3" and activate this workbook after the macro opens
another workbook, assigns a name of "Form7" and then closes "Form7".


ThisWorkbook.Activate

ThisWorkbook always refers to the workbook containing the running code.

--
Tim Williams
Palo Alto, CA

If I'm reading this right, the code appears to be in two seperate workbooks.
As I understand it, after this line:

Application.Run "'Compass Form7.xls'!OpenFormSevenFile"

runs, the procedure stops, so the variables are lost - they aren't carried
over from one procedure to the other as their scope is only within the
workbook (or procedure, or module) that they are defined in. I think if you
define your object variable as Global (instead of public or whatever) it may
carry over, otherwise store the module path somewhere on one of the
workbooks (eg in a Name) and pick it up again in the second workbook. Or,
more simply, put all the code in one workbook - it's far tidier that way.

Jeff

"grantj" wrote in
message ...

Here is the code. Is there a way to attach the Excel workbooks being
referenced?

Sub Copy_Form_7()

Set Compass3 = ActiveWorkbook

If MsgBox("This Macro will copy CFC Form 7 data into the COMPASS3
program. Do you want to continue?", vbYesNo) = vbYes Then
Workbooks.Open Filename:="C:\Compass3\Compass Form7.xls",
UpdateLinks:=3
Application.Run "'Compass Form7.xls'!OpenFormSevenFile"
End If

End Sub

Sub OpenFormSevenFile()

MsgBox ("Select and open a CFC Form 7 file (short or long form).")
Application.FindFile
Set Form7 = ActiveWorkbook

' Copy Statement of Operations
Sheets("Page 1").Select
Range("A:G").Select
Selection.Copy
Windows("Compass Form7.xls").Activate
Sheets("Page 1").Visible = True
Sheets("Page 1").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Page 1").Visible = False

' Copy Balance Sheet
Form7.Activate
Sheets("Page 2").Select
Cells.Select
Selection.Copy
Windows("Compass Form7.xls").Activate
Sheets("Page 2").Visible = True
Sheets("Page 2").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Page 2").Visible = False

' Close Form 7 workbook
Form7.Activate
Application.CutCopyMode = False
Sheets("Page 1").Activate
Range("A1").Select
Form7.Close False

copy_Years

End Sub




Sub copy_Years()

' Copy the first future year into "Compass Form 7" spreadsheet
' to determine where to paste the Form 7 data
Compass3.Activate
Sheets("Balance Sheet Information").Select
Range("AE5").Select
Selection.Copy
Windows("Compass Form7.xls").Activate
Sheets("Sheet1").Select
Range("E4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

If Range("Year") = 1 Then
Copy_Year_1
Else
If Range("Year") = 2 Then
Copy_Year_2
Else
If Range("Year") = 3 Then
Copy_Year_3
Else
End If
End If
End If

End Sub




Sub Copy_Year_1()

Windows("Compass Form7.xls").Activate
Sheets("Workhorse").Visible = True
Sheets("Workhorse").Select
Range("C9:C17").Select
Selection.Copy
Compass3.Activate
Sheets("Expense Information").Select
Range("AE25").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

Windows("Compass Form7.xls").Activate
Sheets("Workhorse").Select
Range("C19:C25").Select
Selection.Copy
Compass3.Activate
Sheets("Expense Information").Select
Range("AE35").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("AE25").Select

Compass3.Activate
Application.CutCopyMode = False
Sheets("General Information").Select
Range("K9").Select

Windows("Compass Form7.xls").Activate
Sheets("Workhorse").Visible = False
Windows("Compass Form7.xls").Close True

End Sub




Sub Copy_Year_2()

Windows("Compass Form7.xls").Activate
Sheets("Workhorse").Visible = True
Sheets("Workhorse").Select
Range("C9:C17").Select
Selection.Copy
Compass3.Activate
Sheets("Expense Information").Select
Range("AF25").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

Windows("Compass Form7.xls").Activate
Sheets("Workhorse").Select
Range("C19:C25").Select
Selection.Copy
Compass3.Activate
Sheets("Expense Information").Select
Range("AF35").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("AF25").Select

Compass3.Activate
Application.CutCopyMode = False
Sheets("General Information").Select

Windows("Compass Form7.xls").Activate
Sheets("Workhorse").Visible = False
Windows("Compass Form7.xls").Close True
Range("K9").Select
End Sub




Sub Copy_Year_3()

Windows("Compass Form7.xls").Activate
Sheets("Workhorse").Visible = True
Sheets("Workhorse").Select
Range("C9:C17").Select
Selection.Copy
Compass3.Activate
Sheets("Expense Information").Select
Range("AG25").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

Windows("Compass Form7.xls").Activate
Sheets("Workhorse").Select
Range("C19:C25").Select
Selection.Copy
Compass3.Activate
Sheets("Expense Information").Select
Range("AG35").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("AE25").Select

Compass3.Activate
Application.CutCopyMode = False
Sheets("General Information").Select

Windows("Compass Form7.xls").Activate
Sheets("Workhorse").Visible = False
Windows("Compass Form7.xls").Close True
Range("K9").Select
End Sub



Jeff Standen Wrote:
That's not the case though. Once you have set an object variable it
works
until that object no longer exists. It doens't even have to be the
active
workbook - as long as it is a reference to a workbook. Making another
workbook active should not stop the variable from referring to the
original
workbook unless you reset it to the new one.

Jeff

"grantj" wrote
in
message ...

I know how to name an active workbook with the following command:
Set Form7 = ActiveWorkbook

In this case I named the active workbook "Form7" which permits me to
activate and interact with the workbook.

Is it possible to name more than one active workbook (with different
names) at the same time and be able to activate and interact with
them?
Apparently the way I use the command, the most recent workbook is
the
active workbook and previous active workbooks that were given names
are
no longer valid.


--
grantj

------------------------------------------------------------------------
grantj's Profile:
http://www.excelforum.com/member.php...o&userid=35021
View this thread:
http://www.excelforum.com/showthread...hreadid=548972



--
grantj
------------------------------------------------------------------------
grantj's Profile:
http://www.excelforum.com/member.php...o&userid=35021
View this thread: http://www.excelforum.com/showthread...hreadid=548972

Thanks Jeff

Jeff Standen Wrote:
If I'm reading this right, the code appears to be in two seperat
workbooks.
As I understand it, after this line:

Application.Run "'Compass Form7.xls'!OpenFormSevenFile"

runs, the procedure stops, so the variables are lost - they aren'
carried
over from one procedure to the other as their scope is only within the
workbook (or procedure, or module) that they are defined in. I think i
you
define your object variable as Global (instead of public or whatever
it may
carry over, otherwise store the module path somewhere on one of the
workbooks (eg in a Name) and pick it up again in the second workbook
Or,
more simply, put all the code in one workbook - it's far tidier tha
way.

Jeff

"grantj" wrote in
message ...

Here is the code. Is there a way to attach the Excel workbook
being
referenced?

Sub Copy_Form_7()

Set Compass3 = ActiveWorkbook

If MsgBox("This Macro will copy CFC Form 7 data into the COMPASS3
program. Do you want to continue?", vbYesNo) = vbYes Then
Workbooks.Open Filename:="C:\Compass3\Compass Form7.xls",
UpdateLinks:=3
Application.Run "'Compass Form7.xls'!OpenFormSevenFile"
End If

End Sub

Sub OpenFormSevenFile()

MsgBox ("Select and open a CFC Form 7 file (short or long form).")
Application.FindFile
Set Form7 = ActiveWorkbook

' Copy Statement of Operations
Sheets("Page 1").Select
Range("A:G").Select
Selection.Copy
Windows("Compass Form7.xls").Activate
Sheets("Page 1").Visible = True
Sheets("Page 1").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Page 1").Visible = False

' Copy Balance Sheet
Form7.Activate
Sheets("Page 2").Select
Cells.Select
Selection.Copy
Windows("Compass Form7.xls").Activate
Sheets("Page 2").Visible = True
Sheets("Page 2").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Page 2").Visible = False

' Close Form 7 workbook
Form7.Activate
Application.CutCopyMode = False
Sheets("Page 1").Activate
Range("A1").Select
Form7.Close False

copy_Years

End Sub




Sub copy_Years()

' Copy the first future year into "Compass Form 7" spreadsheet
' to determine where to paste the Form 7 data
Compass3.Activate
Sheets("Balance Sheet Information").Select
Range("AE5").Select
Selection.Copy
Windows("Compass Form7.xls").Activate
Sheets("Sheet1").Select
Range("E4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

If Range("Year") = 1 Then
Copy_Year_1
Else
If Range("Year") = 2 Then
Copy_Year_2
Else
If Range("Year") = 3 Then
Copy_Year_3
Else
End If
End If
End If

End Sub




Sub Copy_Year_1()

Windows("Compass Form7.xls").Activate
Sheets("Workhorse").Visible = True
Sheets("Workhorse").Select
Range("C9:C17").Select
Selection.Copy
Compass3.Activate
Sheets("Expense Information").Select
Range("AE25").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

Windows("Compass Form7.xls").Activate
Sheets("Workhorse").Select
Range("C19:C25").Select
Selection.Copy
Compass3.Activate
Sheets("Expense Information").Select
Range("AE35").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("AE25").Select

Compass3.Activate
Application.CutCopyMode = False
Sheets("General Information").Select
Range("K9").Select

Windows("Compass Form7.xls").Activate
Sheets("Workhorse").Visible = False
Windows("Compass Form7.xls").Close True

End Sub




Sub Copy_Year_2()

Windows("Compass Form7.xls").Activate
Sheets("Workhorse").Visible = True
Sheets("Workhorse").Select
Range("C9:C17").Select
Selection.Copy
Compass3.Activate
Sheets("Expense Information").Select
Range("AF25").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

Windows("Compass Form7.xls").Activate
Sheets("Workhorse").Select
Range("C19:C25").Select
Selection.Copy
Compass3.Activate
Sheets("Expense Information").Select
Range("AF35").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("AF25").Select

Compass3.Activate
Application.CutCopyMode = False
Sheets("General Information").Select

Windows("Compass Form7.xls").Activate
Sheets("Workhorse").Visible = False
Windows("Compass Form7.xls").Close True
Range("K9").Select
End Sub




Sub Copy_Year_3()

Windows("Compass Form7.xls").Activate
Sheets("Workhorse").Visible = True
Sheets("Workhorse").Select
Range("C9:C17").Select
Selection.Copy
Compass3.Activate
Sheets("Expense Information").Select
Range("AG25").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

Windows("Compass Form7.xls").Activate
Sheets("Workhorse").Select
Range("C19:C25").Select
Selection.Copy
Compass3.Activate
Sheets("Expense Information").Select
Range("AG35").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("AE25").Select

Compass3.Activate
Application.CutCopyMode = False
Sheets("General Information").Select

Windows("Compass Form7.xls").Activate
Sheets("Workhorse").Visible = False
Windows("Compass Form7.xls").Close True
Range("K9").Select
End Sub



Jeff Standen Wrote:
That's not the case though. Once you have set an object variable it
works
until that object no longer exists. It doens't even have to be the
active
workbook - as long as it is a reference to a workbook. Making
another
workbook active should not stop the variable from referring to the
original
workbook unless you reset it to the new one.

Jeff

"grantj"
wrote
in
message ...

I know how to name an active workbook with the following command:
Set Form7 = ActiveWorkbook

In this case I named the active workbook "Form7" which permits me
to
activate and interact with the workbook.

Is it possible to name more than one active workbook (with
different
names) at the same time and be able to activate and interact with
them?
Apparently the way I use the command, the most recent workbook is
the
active workbook and previous active workbooks that were given
names
are
no longer valid.


--
grantj


------------------------------------------------------------------------
grantj's Profile:
http://www.excelforum.com/member.php...o&userid=35021
View this thread:
http://www.excelforum.com/showthread...hreadid=548972



--
grantj

------------------------------------------------------------------------
grantj's Profile:
http://www.excelforum.com/member.php...o&userid=35021
View this thread:
http://www.excelforum.com/showthread...hreadid=548972



--
grantj
------------------------------------------------------------------------
grantj's Profile: http://www.excelforum.com/member.php...o&userid=35021
View this thread: http://www.excelforum.com/showthread...hreadid=548972