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