Naming more than one active workbook

[grantj]

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.

[Ardus Petus]

set Form1=WorkBooks("Something.xls")

HTH
--
AP

"grantj" <[email protected]> a écrit dans
le message de news: [email protected]...
>
> 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]

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.

[Jeff Standen]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Ardus Petus]

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" <[email protected]> a écrit dans
le message de news: [email protected]...
>
> 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
>

[Jeff Standen]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Ardus Petus]

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" <[email protected]> a écrit dans
le message de news: [email protected]...
>
> 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
>

[Ardus Petus]

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" <[email protected]> a écrit dans
le message de news: [email protected]...
>
> 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
>

[Jeff Standen]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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]

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??

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Jeff Standen]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >
>> > 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]

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".

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >
>> > 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
>

[Jeff Standen]

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

Jeff

"grantj" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >
>> > 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" <[email protected]>
>> wrote
>> >> in
>> >> message news:[email protected]...
>> >> >
>> >> > 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
>

[grantj]

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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Tim Williams]

"grantj" <[email protected]> wrote in message
news:[email protected]...
>
> 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


>

[Jeff Standen]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >
>> > 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]

Thanks Jeff

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >
>> > 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
>