hello Experts!
Does anyone know how to activate one of two open workbooks?
My code finds opens another workbook, copies a range, activates the workbook
with the code and pastes, then goes back to the found workbook and closes it.
I need to declare the workbook somehow in case the name changes in the future.
Help?

Tim,

It would help if you posted your code, but the general technique is to use a workbook object:

Sub Test()

Dim myBook As Workbook

Set myBook = Workbooks.Open(Application.GetOpenFilename)
myBook.Worksheets(1).Range("A1:A10").Copy _
ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count , 1).End(xlUp)(2)
myBook.Close False

End Sub

HTH,
Bernie
MS Excel MVP


"TimT" wrote in message
...
hello Experts!
Does anyone know how to activate one of two open workbooks?
My code finds opens another workbook, copies a range, activates the workbook
with the code and pastes, then goes back to the found workbook and closes it.
I need to declare the workbook somehow in case the name changes in the future.
Help?

Bernie,
Here is a sample...
this sub is in a module and called from a worksheet click event.
There are four or five variations of this code that refer to the
activeworkbook thats why I want to declare the name somehow in the module so
that it refers to all subs.

Sub GetBusiness()
'
' GetBusiness Macro
' Macro recorded 11/29/2005 by Tim Teska
'

'
Application.DisplayAlerts = False

Workbooks.Open Filename:="C:\1040WP\BusinessInfo.dif"
Columns("G:G").ColumnWidth = 10.14
Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("G:G").Select
Selection.TextToColumns Destination:=Range("G1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("I1").Select
ActiveCell.FormulaR1C1 = "=RC[-2]&"" ""&RC[-1]"
Range("I1").Select
ActiveCell.FormulaR1C1 = "=RC[-2]&""-""&RC[-1]"
Range("I1").Select
Selection.AutoFill Destination:=Range("I1:I5")
Range("I1:I5").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("G:H").Select
Range("H1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("A1:H4").Select
Selection.Copy
Windows("Electronic Workpapers-Final V 1.1.6.xls").Activate
Sheets("E-1").Select
Range("A11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A11").Select
Windows("BusinessInfo.dif").Activate
Range("A1").Select
ActiveWindow.Close

Application.DisplayAlerts = True

End Sub

"Bernie Deitrick" wrote:

Tim,

It would help if you posted your code, but the general technique is to use a workbook object:

Sub Test()

Dim myBook As Workbook

Set myBook = Workbooks.Open(Application.GetOpenFilename)
myBook.Worksheets(1).Range("A1:A10").Copy _
ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count , 1).End(xlUp)(2)
myBook.Close False

End Sub

HTH,
Bernie
MS Excel MVP


"TimT" wrote in message
...
hello Experts!
Does anyone know how to activate one of two open workbooks?
My code finds opens another workbook, copies a range, activates the workbook
with the code and pastes, then goes back to the found workbook and closes it.
I need to declare the workbook somehow in case the name changes in the future.
Help?

Tim,

Does the code reside in this workbook:

Electronic Workpapers-Final V 1.1.6.xls ?

If so, just replace

Windows("Electronic Workpapers-Final V 1.1.6.xls").Activate

with

Thisworkbook.Activate

HTH,
Bernie
MS Excel MVP


"TimT" wrote in message
...
Bernie,
Here is a sample...
this sub is in a module and called from a worksheet click event.
There are four or five variations of this code that refer to the
activeworkbook thats why I want to declare the name somehow in the module so
that it refers to all subs.

Sub GetBusiness()
'
' GetBusiness Macro
' Macro recorded 11/29/2005 by Tim Teska
'

'
Application.DisplayAlerts = False

Workbooks.Open Filename:="C:\1040WP\BusinessInfo.dif"
Columns("G:G").ColumnWidth = 10.14
Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("G:G").Select
Selection.TextToColumns Destination:=Range("G1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("I1").Select
ActiveCell.FormulaR1C1 = "=RC[-2]&"" ""&RC[-1]"
Range("I1").Select
ActiveCell.FormulaR1C1 = "=RC[-2]&""-""&RC[-1]"
Range("I1").Select
Selection.AutoFill Destination:=Range("I1:I5")
Range("I1:I5").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("G:H").Select
Range("H1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("A1:H4").Select
Selection.Copy
Windows("Electronic Workpapers-Final V 1.1.6.xls").Activate
Sheets("E-1").Select
Range("A11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A11").Select
Windows("BusinessInfo.dif").Activate
Range("A1").Select
ActiveWindow.Close

Application.DisplayAlerts = True

End Sub

"Bernie Deitrick" wrote:

Tim,

It would help if you posted your code, but the general technique is to use a workbook object:

Sub Test()

Dim myBook As Workbook

Set myBook = Workbooks.Open(Application.GetOpenFilename)
myBook.Worksheets(1).Range("A1:A10").Copy _
ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count , 1).End(xlUp)(2)
myBook.Close False

End Sub

HTH,
Bernie
MS Excel MVP


"TimT" wrote in message
...
hello Experts!
Does anyone know how to activate one of two open workbooks?
My code finds opens another workbook, copies a range, activates the workbook
with the code and pastes, then goes back to the found workbook and closes it.
I need to declare the workbook somehow in case the name changes in the future.
Help?

what about the other workbook that is open that I copied from?
That one needs to stay open until the pasting of values or I loose the values.


"Bernie Deitrick" wrote:

Tim,

Does the code reside in this workbook:

Electronic Workpapers-Final V 1.1.6.xls ?

If so, just replace

Windows("Electronic Workpapers-Final V 1.1.6.xls").Activate

with

Thisworkbook.Activate

HTH,
Bernie
MS Excel MVP


"TimT" wrote in message
...
Bernie,
Here is a sample...
this sub is in a module and called from a worksheet click event.
There are four or five variations of this code that refer to the
activeworkbook thats why I want to declare the name somehow in the module so
that it refers to all subs.

Sub GetBusiness()
'
' GetBusiness Macro
' Macro recorded 11/29/2005 by Tim Teska
'

'
Application.DisplayAlerts = False

Workbooks.Open Filename:="C:\1040WP\BusinessInfo.dif"
Columns("G:G").ColumnWidth = 10.14
Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("G:G").Select
Selection.TextToColumns Destination:=Range("G1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("I1").Select
ActiveCell.FormulaR1C1 = "=RC[-2]&"" ""&RC[-1]"
Range("I1").Select
ActiveCell.FormulaR1C1 = "=RC[-2]&""-""&RC[-1]"
Range("I1").Select
Selection.AutoFill Destination:=Range("I1:I5")
Range("I1:I5").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("G:H").Select
Range("H1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("A1:H4").Select
Selection.Copy
Windows("Electronic Workpapers-Final V 1.1.6.xls").Activate
Sheets("E-1").Select
Range("A11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A11").Select
Windows("BusinessInfo.dif").Activate
Range("A1").Select
ActiveWindow.Close

Application.DisplayAlerts = True

End Sub

"Bernie Deitrick" wrote:

Tim,

It would help if you posted your code, but the general technique is to use a workbook object:

Sub Test()

Dim myBook As Workbook

Set myBook = Workbooks.Open(Application.GetOpenFilename)
myBook.Worksheets(1).Range("A1:A10").Copy _
ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count , 1).End(xlUp)(2)
myBook.Close False

End Sub

HTH,
Bernie
MS Excel MVP


"TimT" wrote in message
...
hello Experts!
Does anyone know how to activate one of two open workbooks?
My code finds opens another workbook, copies a range, activates the workbook
with the code and pastes, then goes back to the found workbook and closes it.
I need to declare the workbook somehow in case the name changes in the future.
Help?

That was it!
Thank you!!

"Bernie Deitrick" wrote:

Tim,

Does the code reside in this workbook:

Electronic Workpapers-Final V 1.1.6.xls ?

If so, just replace

Windows("Electronic Workpapers-Final V 1.1.6.xls").Activate

with

Thisworkbook.Activate

HTH,
Bernie
MS Excel MVP


"TimT" wrote in message
...
Bernie,
Here is a sample...
this sub is in a module and called from a worksheet click event.
There are four or five variations of this code that refer to the
activeworkbook thats why I want to declare the name somehow in the module so
that it refers to all subs.

Sub GetBusiness()
'
' GetBusiness Macro
' Macro recorded 11/29/2005 by Tim Teska
'

'
Application.DisplayAlerts = False

Workbooks.Open Filename:="C:\1040WP\BusinessInfo.dif"
Columns("G:G").ColumnWidth = 10.14
Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("G:G").Select
Selection.TextToColumns Destination:=Range("G1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("I1").Select
ActiveCell.FormulaR1C1 = "=RC[-2]&"" ""&RC[-1]"
Range("I1").Select
ActiveCell.FormulaR1C1 = "=RC[-2]&""-""&RC[-1]"
Range("I1").Select
Selection.AutoFill Destination:=Range("I1:I5")
Range("I1:I5").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("G:H").Select
Range("H1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("A1:H4").Select
Selection.Copy
Windows("Electronic Workpapers-Final V 1.1.6.xls").Activate
Sheets("E-1").Select
Range("A11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A11").Select
Windows("BusinessInfo.dif").Activate
Range("A1").Select
ActiveWindow.Close

Application.DisplayAlerts = True

End Sub

"Bernie Deitrick" wrote:

Tim,

It would help if you posted your code, but the general technique is to use a workbook object:

Sub Test()

Dim myBook As Workbook

Set myBook = Workbooks.Open(Application.GetOpenFilename)
myBook.Worksheets(1).Range("A1:A10").Copy _
ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count , 1).End(xlUp)(2)
myBook.Close False

End Sub

HTH,
Bernie
MS Excel MVP


"TimT" wrote in message
...
hello Experts!
Does anyone know how to activate one of two open workbooks?
My code finds opens another workbook, copies a range, activates the workbook
with the code and pastes, then goes back to the found workbook and closes it.
I need to declare the workbook somehow in case the name changes in the future.
Help?