ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Activate workbook (https://www.excelbanter.com/excel-programming/346859-activate-workbook.html)

TimT
Activate workbook
 
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 Deitrick
Activate workbook
 
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?



TimT
Activate workbook
 
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?




Bernie Deitrick
Activate workbook
 
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?

TimT
Activate workbook
 
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?

TimT
Activate workbook
 
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?


All times are GMT +1. The time now is 02:30 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com