Name Active Workbook
 

I am trying to create a simple copy/paste macro from one workbook with many
tabs to another with many tabs. The process is: a user opens an always
different named workbook runs macro from a menu. The macro then has the user
select the second workbook manually. The macro will then take data from the
second workbook and paste into first workbook.

How do I rename the first workbook only for the duration of the macro so the
macro knows to go back to that file to paste too? I hope this is clear.

Name Active Workbook
 

Hi Matthew

You can create a reference to the first workbook before you open the other one

Dim wb1 As Workbook
Set wb1 = ActiveWorkbook

You can use wb1 now in your code to paste

wb1.Sheets("Sheet1").Range ("A1")



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Matthew R" <Matthew wrote in message ...
I am trying to create a simple copy/paste macro from one workbook with many
tabs to another with many tabs. The process is: a user opens an always
different named workbook runs macro from a menu. The macro then has the user
select the second workbook manually. The macro will then take data from the
second workbook and paste into first workbook.

How do I rename the first workbook only for the duration of the macro so the
macro knows to go back to that file to paste too? I hope this is clear.

Name Active Workbook
 

What am I doing wrong?

Sub ITMODELCOPY()

Dim wb1 As Workbook
Set wb1 = ActiveWorkbook

MsgBox "Select the IT Model File"
TEMPLa = Application.GetOpenFilename("Excel (*.xls), *.xls")
TEMPL = Dir(TEMPLa)
TEMPLacik = 0
If ActiveWorkbook Is Nothing Then GoTo ac
ad = ActiveWorkbook.Name
If ad = TEMPL Then TEMPLacik = 1
ActiveWindow.ActivateNext
Do While ActiveWorkbook.Name < ad
If ActiveWorkbook.Name = TEMPL Then TEMPLacik = 1
ActiveWindow.ActivateNext
Loop
ac:
If TEMPLacik = 0 Then Workbooks.Open (TEMPLa)
Workbooks(TEMPL).Activate
Sheets("IT Costing Detail").Select
Range("C112").Select

Workbooks(wb1).Activate

"Ron de Bruin" wrote:

Hi Matthew

You can create a reference to the first workbook before you open the other one

Dim wb1 As Workbook
Set wb1 = ActiveWorkbook

You can use wb1 now in your code to paste

wb1.Sheets("Sheet1").Range ("A1")



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Matthew R" <Matthew wrote in message ...
I am trying to create a simple copy/paste macro from one workbook with many
tabs to another with many tabs. The process is: a user opens an always
different named workbook runs macro from a menu. The macro then has the user
select the second workbook manually. The macro will then take data from the
second workbook and paste into first workbook.

How do I rename the first workbook only for the duration of the macro so the
macro knows to go back to that file to paste too? I hope this is clear.

Name Active Workbook
 

Hi

You can test if the workbook is open with a function

Sub ITMODELCOPY()

Dim wb1 As Workbook
Dim TEMPLa As Variant
Dim TEMPL As String
Set wb1 = ActiveWorkbook

MsgBox "Select the IT Model File"
TEMPLa = Application.GetOpenFilename("Excel (*.xls), *.xls")
TEMPL = Dir(TEMPLa)
If bIsBookOpen(TEMPL) Then
'do nothing
Else
Workbooks.Open (TEMPLa)
End If

wb1.Activate
End Sub

Function bIsBookOpen(ByRef szBookName As String) As Boolean
' Rob Bovey
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Matthew R" wrote in message ...
What am I doing wrong?

Sub ITMODELCOPY()

Dim wb1 As Workbook
Set wb1 = ActiveWorkbook

MsgBox "Select the IT Model File"
TEMPLa = Application.GetOpenFilename("Excel (*.xls), *.xls")
TEMPL = Dir(TEMPLa)
TEMPLacik = 0
If ActiveWorkbook Is Nothing Then GoTo ac
ad = ActiveWorkbook.Name
If ad = TEMPL Then TEMPLacik = 1
ActiveWindow.ActivateNext
Do While ActiveWorkbook.Name < ad
If ActiveWorkbook.Name = TEMPL Then TEMPLacik = 1
ActiveWindow.ActivateNext
Loop
ac:
If TEMPLacik = 0 Then Workbooks.Open (TEMPLa)
Workbooks(TEMPL).Activate
Sheets("IT Costing Detail").Select
Range("C112").Select

Workbooks(wb1).Activate

"Ron de Bruin" wrote:

Hi Matthew

You can create a reference to the first workbook before you open the other one

Dim wb1 As Workbook
Set wb1 = ActiveWorkbook

You can use wb1 now in your code to paste

wb1.Sheets("Sheet1").Range ("A1")



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Matthew R" <Matthew wrote in message ...
I am trying to create a simple copy/paste macro from one workbook with many
tabs to another with many tabs. The process is: a user opens an always
different named workbook runs macro from a menu. The macro then has the user
select the second workbook manually. The macro will then take data from the
second workbook and paste into first workbook.

How do I rename the first workbook only for the duration of the macro so the
macro knows to go back to that file to paste too? I hope this is clear.

Name Active Workbook
 

Thanks Ron!...That worked perfectly. Have a nice day!

"Ron de Bruin" wrote:

Hi

You can test if the workbook is open with a function

Sub ITMODELCOPY()

Dim wb1 As Workbook
Dim TEMPLa As Variant
Dim TEMPL As String
Set wb1 = ActiveWorkbook

MsgBox "Select the IT Model File"
TEMPLa = Application.GetOpenFilename("Excel (*.xls), *.xls")
TEMPL = Dir(TEMPLa)
If bIsBookOpen(TEMPL) Then
'do nothing
Else
Workbooks.Open (TEMPLa)
End If

wb1.Activate
End Sub

Function bIsBookOpen(ByRef szBookName As String) As Boolean
' Rob Bovey
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Matthew R" wrote in message ...
What am I doing wrong?

Sub ITMODELCOPY()

Dim wb1 As Workbook
Set wb1 = ActiveWorkbook

MsgBox "Select the IT Model File"
TEMPLa = Application.GetOpenFilename("Excel (*.xls), *.xls")
TEMPL = Dir(TEMPLa)
TEMPLacik = 0
If ActiveWorkbook Is Nothing Then GoTo ac
ad = ActiveWorkbook.Name
If ad = TEMPL Then TEMPLacik = 1
ActiveWindow.ActivateNext
Do While ActiveWorkbook.Name < ad
If ActiveWorkbook.Name = TEMPL Then TEMPLacik = 1
ActiveWindow.ActivateNext
Loop
ac:
If TEMPLacik = 0 Then Workbooks.Open (TEMPLa)
Workbooks(TEMPL).Activate
Sheets("IT Costing Detail").Select
Range("C112").Select

Workbooks(wb1).Activate

"Ron de Bruin" wrote:

Hi Matthew

You can create a reference to the first workbook before you open the other one

Dim wb1 As Workbook
Set wb1 = ActiveWorkbook

You can use wb1 now in your code to paste

wb1.Sheets("Sheet1").Range ("A1")



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Matthew R" <Matthew wrote in message ...
I am trying to create a simple copy/paste macro from one workbook with many
tabs to another with many tabs. The process is: a user opens an always
different named workbook runs macro from a menu. The macro then has the user
select the second workbook manually. The macro will then take data from the
second workbook and paste into first workbook.

How do I rename the first workbook only for the duration of the macro so the
macro knows to go back to that file to paste too? I hope this is clear.