switching between workbooks...

[safdarhassan]

Hi,

I have created a workbook (workbook 1) with some buttons (forms) which, when clicked, opens another workbook (workbook 2) and then runs some macros. All the macros do is copy multiple ranges of data from workbook 1 and paste the data into various cell locations in workbook 2. In the macros i do not want to have to specify the workbook name to paste data into, as the workbook i am opening will not always be the same name. i have tried the following but it does not seem to work:

~~~
Sub Button_click()

Workbook.open("xxxx.xls")
' next i have specified a constant (target) to use rather than the name of the workbook
target = Activeworkbook.name
' next i run the macros or copying
run ("macro 1")
run ("macro 2")
etc
~~~


now in the macro i have the following code:
~~~~~
workbooks("workbook 1.xls").Acivate
Sheets("sheet1").Select
Range("A1:H7").Select
Selection.copy
workbooks(target).Activate
Sheets("sheet 5").select
Range("I9").Select
Selection.paste
~~~~~

that is roughly what is happening.
I hope that what i am trying to do is clear. Please let me know where i am going wrong or whether there are any alternative methods i can use.

Thanks in advance

[Ron de Bruin]

Hi safdarhassan

Do you want to select the file you open or do you have a different workbook name in each macro

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



"safdarhassan" <[email protected]> wrote in message
news:[email protected]...
>
> Hi,
>
> I have created a workbook (workbook 1) with some buttons (forms) which,
> when clicked, opens another workbook (workbook 2) and then runs some
> macros. All the macros do is copy multiple ranges of data from workbook
> 1 and paste the data into various cell locations in workbook 2. In the
> macros i do not want to have to specify the workbook name to paste data
> into, as the workbook i am opening will not always be the same name. i
> have tried the following but it does not seem to work:
>
> ~~~
> Sub Button_click()
>
> Workbook.open("xxxx.xls")
> ' next i have specified a constant (target) to use rather than the name
> of the workbook
> target = Activeworkbook.name
> ' next i run the macros or copying
> run ("macro 1")
> run ("macro 2")
> etc
> ~~~
>
>
> now in the macro i have the following code:
> ~~~~~
> workbooks("workbook 1.xls").Acivate
> Sheets("sheet1").Select
> Range("A1:H7").Select
> Selection.copy
> workbooks(target).Activate
> Sheets("sheet 5").select
> Range("I9").Select
> Selection.paste
> ~~~~~
>
> that is roughly what is happening.
> I hope that what i am trying to do is clear. Please let me know where i
> am going wrong or whether there are any alternative methods i can use.
>
> Thanks in advance
>
>
> --
> safdarhassan
> ------------------------------------------------------------------------
> safdarhassan's Profile: http://www.excelforum.com/member.php...o&userid=37424
> View this thread: http://www.excelforum.com/showthread...hreadid=571011
>

[Nigel]

try the following:

Dim target as workbook
Workbook.open("xxxx.xls")
set target = Activeworkbook

workbooks("workbook 1.xls").Sheets("sheet1").Range("A1:H7").copy _
Destination:= target.Sheets("sheet 5").Range("I9")




--
Cheers
Nigel



"safdarhassan" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi,
>
> I have created a workbook (workbook 1) with some buttons (forms) which,
> when clicked, opens another workbook (workbook 2) and then runs some
> macros. All the macros do is copy multiple ranges of data from workbook
> 1 and paste the data into various cell locations in workbook 2. In the
> macros i do not want to have to specify the workbook name to paste data
> into, as the workbook i am opening will not always be the same name. i
> have tried the following but it does not seem to work:
>
> ~~~
> Sub Button_click()
>
W>orkbook.open("xxxx.xls")
> ' next i have specified a constant (target) to use rather than the name
> of the workbook
> target = Activeworkbook.name
> ' next i run the macros or copying
> run ("macro 1")
> run ("macro 2")
> etc
> ~~~
>
>
> now in the macro i have the following code:
> ~~~~~
> workbooks("workbook 1.xls").Acivate
> Sheets("sheet1").Select
> Range("A1:H7").Select
> Selection.copy
> workbooks(target).Activate
> Sheets("sheet 5").select
> Range("I9").Select
> Selection.paste
> ~~~~~
>
> that is roughly what is happening.
> I hope that what i am trying to do is clear. Please let me know where i
> am going wrong or whether there are any alternative methods i can use.
>
> Thanks in advance
>
>
> --
> safdarhassan
> ------------------------------------------------------------------------
> safdarhassan's Profile:
> http://www.excelforum.com/member.php...o&userid=37424
> View this thread: http://www.excelforum.com/showthread...hreadid=571011
>

[Ron de Bruin]

You can use this in a module
Note : the dim line is on top of the module

We use Set to make a reference to the workbook you open
After that you can use wb for it


Public wb As Workbook

Sub test2()
Set wb = Workbooks.Open("C:\Data\test1.xls")
Call mymacro
wb.Close True
Set wb = Nothing
End Sub

Sub mymacro()
ThisWorkbook.Sheets(1).Range("A1:A3").Copy wb.Sheets(1).Range("A1")
End Sub




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



"safdarhassan" <[email protected]> wrote in message
news:[email protected]...
>
> Hi,
>
> I have created a workbook (workbook 1) with some buttons (forms) which,
> when clicked, opens another workbook (workbook 2) and then runs some
> macros. All the macros do is copy multiple ranges of data from workbook
> 1 and paste the data into various cell locations in workbook 2. In the
> macros i do not want to have to specify the workbook name to paste data
> into, as the workbook i am opening will not always be the same name. i
> have tried the following but it does not seem to work:
>
> ~~~
> Sub Button_click()
>
> Workbook.open("xxxx.xls")
> ' next i have specified a constant (target) to use rather than the name
> of the workbook
> target = Activeworkbook.name
> ' next i run the macros or copying
> run ("macro 1")
> run ("macro 2")
> etc
> ~~~
>
>
> now in the macro i have the following code:
> ~~~~~
> workbooks("workbook 1.xls").Acivate
> Sheets("sheet1").Select
> Range("A1:H7").Select
> Selection.copy
> workbooks(target).Activate
> Sheets("sheet 5").select
> Range("I9").Select
> Selection.paste
> ~~~~~
>
> that is roughly what is happening.
> I hope that what i am trying to do is clear. Please let me know where i
> am going wrong or whether there are any alternative methods i can use.
>
> Thanks in advance
>
>
> --
> safdarhassan
> ------------------------------------------------------------------------
> safdarhassan's Profile: http://www.excelforum.com/member.php...o&userid=37424
> View this thread: http://www.excelforum.com/showthread...hreadid=571011
>