| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Closing Hidden Workbook when Active Workbook is Closed | Excel Programming | |||
| copy worksheet from closed workbook to active workbook using vba | Excel Worksheet Functions | |||
| How do I make an open workbook the active workbook | Excel Programming | |||
| Copy Modules from One workbook to Active Workbook | Excel Programming | |||
| Preventing opening workbook inside active workbook. | Excel Programming | |||
Hybrid Mode
