Hi,

CAn anyone offer any MACRO advice? Ive recorded the folling MACRO which
has got me 90% the

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 31/03/2005 by Andrew Fletcher
'

'
Application.Goto Reference:="ahu1"
Selection.Copy
Sheets("Points List").Select
Range("A9").Select
ActiveSheet.Paste
Range("A25").Select
End Sub

This macro copies a group of cells, in this case with the range name
"ahu1" from a sheet known as 'clipboard' and pastes them to a sheet
known as 'points list'.

I need to be able to select where in the points list sheet the paste
occurs by selecting the active cell, which in the above example refers
to cell A9 every time I run the MACRO.

Thanks to previous contributions, due to my lack of VBA skills I
couldnt get the examples kindly proffered to work. THe above is all I
require to achieve however.

Regards,
Andy Fletcher.

Hi, Andy,

Why don't you just have your macro performing the copy and switch to
PointsList sheet?
Then, you could just click the cell you want and do [Control]-V or click the
Paste tool to paste your data in?
You have to click your target cell anyway, so one more click shouldn't make
much difference. Also, as your VBA improves, you'll see that you don't need
to GOTO a range in order to copy it (see below)

Sub Macro1()
Worksheets("Clipboard").Range("ahu1").copy
Sheets("Points List").activate
End Sub

Hope this helps

Pete

Sub Macro1()
Dim rng As Range

Set rng = Application.InputBox("Select position to paste to", Type:=8)
Worksheets("Clipboard").Range("ahu1").Copy Destination:=rng
End Sub


--

HTH

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


"Fletch" wrote in message
oups.com...
Hi,

CAn anyone offer any MACRO advice? Ive recorded the folling MACRO which
has got me 90% the

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 31/03/2005 by Andrew Fletcher
'

'
Application.Goto Reference:="ahu1"
Selection.Copy
Sheets("Points List").Select
Range("A9").Select
ActiveSheet.Paste
Range("A25").Select
End Sub

This macro copies a group of cells, in this case with the range name
"ahu1" from a sheet known as 'clipboard' and pastes them to a sheet
known as 'points list'.

I need to be able to select where in the points list sheet the paste
occurs by selecting the active cell, which in the above example refers
to cell A9 every time I run the MACRO.

Thanks to previous contributions, due to my lack of VBA skills I
couldnt get the examples kindly proffered to work. THe above is all I
require to achieve however.

Regards,
Andy Fletcher.

Thanks fellas, I have cracked it with this:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 31/03/2005 by Andrew Fletcher
'
Application.Goto Reference:="ahu1"
Selection.Copy
Sheets("Points List").Select
ActiveSheet.Paste
End Sub

For all the other plant groups I have just copied and pasted modules,
substituting the range name along the way.

Remarkably, the solution was to just remove the "range" commands. It
was staring me in the face I just couldnt see it. I have added all the
plant items to a drop down list - its very slick.

Thanks again, I will get the hang of this VB if it kills me. What
originally looked like klingon now bears some resemblance to French, ie
some comprehension is evident...

Andy.

Some supplemental information:
Since your new to vba, it might be useful to point out that if the user
clicks cancel in the input box you will get an error. You can correct this
with

Sub Macro1()
Dim rng As Range
On Error Resume Next
Set rng = Application.InputBox("Select position to paste to", Type:=8)
Worksheets("Clipboard").Range("ahu1").Copy Destination:=rng
End Sub

again, since you are new, this could be interpreted to suggest that you
should put On Error Resume Next at the top of every procedure, but that
would be a really bad idea. A better example although in this case
unnecessary would be to do:

Sub Macro1()
Dim rng As Range
On Error Resume Next
Set rng = Application.InputBox("Select position to paste to", Type:=8)
On Error goto 0
if not rng is nothing then
Worksheets("Clipboard").Range("ahu1").Copy Destination:=rng
End if
End Sub


This limits the amount of time in which On Error Resume Next is in effect
and which is highly desirable, as otherwise it could mask real errors that
need to be corrected.

--
Regards,
Tom Ogilvy



"Bob Phillips" wrote in message
...
Sub Macro1()
Dim rng As Range

Set rng = Application.InputBox("Select position to paste to", Type:=8)
Worksheets("Clipboard").Range("ahu1").Copy Destination:=rng
End Sub


--

HTH

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


"Fletch" wrote in message
oups.com...
Hi,

CAn anyone offer any MACRO advice? Ive recorded the folling MACRO which
has got me 90% the

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 31/03/2005 by Andrew Fletcher
'

'
Application.Goto Reference:="ahu1"
Selection.Copy
Sheets("Points List").Select
Range("A9").Select
ActiveSheet.Paste
Range("A25").Select
End Sub

This macro copies a group of cells, in this case with the range name
"ahu1" from a sheet known as 'clipboard' and pastes them to a sheet
known as 'points list'.

I need to be able to select where in the points list sheet the paste
occurs by selecting the active cell, which in the above example refers
to cell A9 every time I run the MACRO.

Thanks to previous contributions, due to my lack of VBA skills I
couldnt get the examples kindly proffered to work. THe above is all I
require to achieve however.

Regards,
Andy Fletcher.