Thread: Naming more than one active workbook
View Single Post
  #10   Report Post  
Posted to microsoft.public.excel.programming
grantj[_5_] grantj[_5_] is offline
external usenet poster
  
Posts: 1
Default Naming more than one active workbook

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

Reply With QuoteReply With Quote