Hi some help would be appreciated - I use this MACRO to copy a cell
group from one sheet (called clipboard) to another sheet (pointslist)
at a position determined by the highlighted cell on the destination
sheet:

Sub CopyPlant()
Dim x As Variant, strRange As String

Set x = ActiveCell
strRange = InputBox("Plant Config?", "Range to Copy", "Plant")
Worksheets("Clipboard").Range(strRange).Copy (x)
End Sub

Various different groups of cells have been given 'names' on the
clipboard. When I execute the Macro I am asked to enter the 'name',
the macro then copies the requisite cell group to the points list.

I want to automate this further and give each group a seperate macro
which can be activated by a button. Initially I thought this would be
simple - just create a macro which runs the existing macro, types in
the requisite plant name and job done, but you dont seem to be able to
run a macro within a macro. Any ideas anyone? Best Regards, Andy
Fletcher.

Hi,
Set up this sub..

Sub CopyPlant(strRange as string)
Worksheets("Clipboard").Range(*strRange).Copy ActiveCell
End Sub

Then call it like this..

Sub Button1Click()
Let strRange = InputBox("Plant Config?", "Range to Copy", "Plant")
CopyPlants strRange
End Sub

assign Button1 to Button1Click (assuming it is a Forms Button).
Untested but should work (i think). The principle is there; pasing a
variable to a common routine...
Hth,
OJ

Hi,
I'm not sure I was thinking when I wrote this....

You have buttons to represent each named cell? Is that right?
OJ

Do you mean?

Sub CopyPlant()
Dim x As Variant, strRange As String

Set x = ActiveCell
strRange = InputBox("Plant Config?", "Range to Copy", "Plant")
Select Case strRange
Case "Value1": Macro1
Case "Value2": Macro2
Case "Value3": Macro3
End Select
End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andrew Fletcher" wrote in message
om...
Hi some help would be appreciated - I use this MACRO to copy a cell
group from one sheet (called clipboard) to another sheet (pointslist)
at a position determined by the highlighted cell on the destination
sheet:

Sub CopyPlant()
Dim x As Variant, strRange As String

Set x = ActiveCell
strRange = InputBox("Plant Config?", "Range to Copy", "Plant")
Worksheets("Clipboard").Range(strRange).Copy (x)
End Sub

Various different groups of cells have been given 'names' on the
clipboard. When I execute the Macro I am asked to enter the 'name',
the macro then copies the requisite cell group to the points list.

I want to automate this further and give each group a seperate macro
which can be activated by a button. Initially I thought this would be
simple - just create a macro which runs the existing macro, types in
the requisite plant name and job done, but you dont seem to be able to
run a macro within a macro. Any ideas anyone? Best Regards, Andy
Fletcher.

Thanks for the prompt response guys. I could use custom buttons but
thought the tidier solution would be to assign each macro to an item in
a drop down list.

Thx. Andy

Assuming the item selected in the dropdown is the name of the range to copy:

Sub DropDown1_Click()
Dim x As Range, strRange As String
Dim sName as String, drpdwn as DropDown
sName = Application.Caller
set drpdwn = Activesheet.DropDowns(sName)
if drpdwn.ListIndex < 0 then
strRange = drpdwn.List(drpdwn.ListIndex)
Set x = ActiveCell
Worksheets("Clipboard").Range(strRange).Copy x
End if
End Sub

Assumes a dropdown (combobox) from the Forms Toolbar.

You shouldn't put x in parentheses - unless you want to refer to the value
stored in the cell referenced by x. Apparently it has been working for you,
but there are situations where this will cause an error. In VBA you put
arguments in parentheses only when the function is returning a value that
will be used or if you use the CALL statement. Copy is a method, but I
would still follow the convention.

--
Regards,
Tom Ogilvy


"Fletch" wrote in message
ups.com...
Thanks for the prompt response guys. I could use custom buttons but
thought the tidier solution would be to assign each macro to an item in
a drop down list.

Thx. Andy