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

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" wrote in message
news:safdarhassan.2cfcb2_1155381305.6181@excelforu m-nospam.com...

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

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"
wrote in message
news:safdarhassan.2cfcb2_1155381305.6181@excelforu m-nospam.com...

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

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" wrote in message
news:safdarhassan.2cfcb2_1155381305.6181@excelforu m-nospam.com...

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