Copying and PAsting using MACROS
 

Hi, wonder if anyone can help with this little conundrum. I want to run
a macro that copies a specific row of data from a specific worksheet
and pastes it into another worksheet. I want to specify which
particular row the date gets pasted onto though, preferably by simply
highighting the destination row and then running the macro. At the
moment I am using a manual cut and paste process but would love to make
my workbook a bit slicker. Assistance appreciated. Thanx Andy Fletcher

as always, post your macro for comments. You haven't told us where to copy
to??


--
Don Guillett
SalesAid Software

wrote in message
oups.com...
Hi, wonder if anyone can help with this little conundrum. I want to run
a macro that copies a specific row of data from a specific worksheet
and pastes it into another worksheet. I want to specify which
particular row the date gets pasted onto though, preferably by simply
highighting the destination row and then running the macro. At the
moment I am using a manual cut and paste process but would love to make
my workbook a bit slicker. Assistance appreciated. Thanx Andy Fletcher

Don, as requested:

ActiveWindow.SmallScroll Down:=-3
Sheets("Clipboard").Select
Range("A21:H24").Select
Selection.Copy
Sheets("Points List").Select
Range("A9").Select
ActiveSheet.Paste

As you can see I am copying a standard plant configuration from the
worksheet called clipboard. I may want to paste the selection at any
row on the destination worksheet. The column configuration will always
remain the same though.

Cheers,

Andy

Are you ALWAYS copying A21:H24 on the Clipboard sheet?

Can't you do something like this with the destination already selected?
(*warning* untested)

dim x as variant
set x = activecell
Worksheets("Clipboard").range(("A21:H24").Copy(x)


" wrote:

Don, as requested:

ActiveWindow.SmallScroll Down:=-3
Sheets("Clipboard").Select
Range("A21:H24").Select
Selection.Copy
Sheets("Points List").Select
Range("A9").Select
ActiveSheet.Paste

As you can see I am copying a standard plant configuration from the
worksheet called clipboard. I may want to paste the selection at any
row on the destination worksheet. The column configuration will always
remain the same though.

Cheers,

Andy

Hi, THx for the prompt response. No there are various cellranges with
different configurations of plant that I want to copy from the
clipboard to rhe points list. I have taken this further now and named
these ranges, so I should be able to reference to the names. With
respect to the formula how do I get that into a macro? Sorry dont know
anything about VB. Thanks again for your interest in assisting me.
Andrew

You use a range name as demonstrated below

dim x as variant
set x = activecell
Worksheets("Clipboard").range(range_name).Copy(x)

If you only had a small number of of ranges that you'll copy, you could
create a separate Subroutine for each one, then add buttons to your toolbar
and assign each subroutine to a different button.


wrote in message
oups.com...
Hi, THx for the prompt response. No there are various cellranges with
different configurations of plant that I want to copy from the
clipboard to rhe points list. I have taken this further now and named
these ranges, so I should be able to reference to the names. With
respect to the formula how do I get that into a macro? Sorry dont know
anything about VB. Thanks again for your interest in assisting me.
Andrew

Hi Duke,

CHeers for that. Im afraid that i not sure how to get this into a
macro, i assume that its via VB editor. Im a bit of a novice in this
area and wonder if you could advise me as to how to implement the code
you have suggested.

Again, thanks.

Andy


Duke Carey wrote:
You use a range name as demonstrated below

dim x as variant
set x = activecell
Worksheets("Clipboard").range(range_name).Copy(x)

If you only had a small number of of ranges that you'll copy, you
could
create a separate Subroutine for each one, then add buttons to your
toolbar
and assign each subroutine to a different button.


wrote in message
oups.com...
Hi, THx for the prompt response. No there are various cellranges
with
different configurations of plant that I want to copy from the
clipboard to rhe points list. I have taken this further now and
named
these ranges, so I should be able to reference to the names. With
respect to the formula how do I get that into a macro? Sorry dont
know
anything about VB. Thanks again for your interest in assisting me.
Andrew

Andrew -

You posted the code for the macro that you'd recorded. Presumably you got
there by way of Tools | Macro | Visual Basic Editor, so you know how to do
that (a shortcut is Alt-F11)

In the editor, you create a new subroutine by typing "sub routine_name" and
pressing Enter. Excel adds the required "()" at the end of the name you
provide, a blank line, and the required "End Sub". All you need to do is put
the suggested code in the blank lines between the Sub and End Sub lines.

One thought you may want to consider is naming your various sets of rows
'Plant01', 'Plant02', etc., then use this code which prompts the user to type
in the range name - then you don't have to assign everything to buttons

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


Give it a shot and let me know

Duke


" wrote:

Hi Duke,

CHeers for that. Im afraid that i not sure how to get this into a
macro, i assume that its via VB editor. Im a bit of a novice in this
area and wonder if you could advise me as to how to implement the code
you have suggested.

Again, thanks.

Andy


Duke Carey wrote:
You use a range name as demonstrated below

dim x as variant
set x = activecell
Worksheets("Clipboard").range(range_name).Copy(x)

If you only had a small number of of ranges that you'll copy, you
could
create a separate Subroutine for each one, then add buttons to your
toolbar
and assign each subroutine to a different button.


wrote in message
oups.com...
Hi, THx for the prompt response. No there are various cellranges
with
different configurations of plant that I want to copy from the
clipboard to rhe points list. I have taken this further now and
named
these ranges, so I should be able to reference to the names. With
respect to the formula how do I get that into a macro? Sorry dont
know
anything about VB. Thanks again for your interest in assisting me.
Andrew

No error checking in the previous code. Use this instead

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

Set x = ActiveCell
strRange = InputBox("Plant Config?", "Range to Copy", "Plant")
If strRange = "" Then Exit Sub

Worksheets("Clipboard").Range(strRange).Copy (x)

End Sub

" wrote:

Hi Duke,

CHeers for that. Im afraid that i not sure how to get this into a
macro, i assume that its via VB editor. Im a bit of a novice in this
area and wonder if you could advise me as to how to implement the code
you have suggested.

Again, thanks.

Andy


Duke Carey wrote:
You use a range name as demonstrated below

dim x as variant
set x = activecell
Worksheets("Clipboard").range(range_name).Copy(x)

If you only had a small number of of ranges that you'll copy, you
could
create a separate Subroutine for each one, then add buttons to your
toolbar
and assign each subroutine to a different button.


wrote in message
oups.com...
Hi, THx for the prompt response. No there are various cellranges
with
different configurations of plant that I want to copy from the
clipboard to rhe points list. I have taken this further now and
named
these ranges, so I should be able to reference to the names. With
respect to the formula how do I get that into a macro? Sorry dont
know
anything about VB. Thanks again for your interest in assisting me.
Andrew

Duke,

Thats tremendous, works a treat! It needs a bit of refining now though
because there are many plant configurations on the clipboard it is
difficult to remember each name. The configurations have names like
two_boilers_flow_and_return_temperature_sensors.

I was hoping to be able to either:

1: Assign each configuration to a macro button
or:
2: Access some sort of drop down list or list inserted into right click
menu. (Preferable)

Nonetheless what we have achieved is a great step forward. THanks for
your help so far and if you have any ideas about the above I would be
very pleased to hear em.

Regards,

Andy FLetcher





Duke Carey wrote:
No error checking in the previous code. Use this instead

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

Set x = ActiveCell
strRange = InputBox("Plant Config?", "Range to Copy", "Plant")
If strRange = "" Then Exit Sub

Worksheets("Clipboard").Range(strRange).Copy (x)

End Sub

" wrote:

Hi Duke,

CHeers for that. Im afraid that i not sure how to get this into a
macro, i assume that its via VB editor. Im a bit of a novice in
this
area and wonder if you could advise me as to how to implement the
code
you have suggested.

Again, thanks.

Andy


Duke Carey wrote:
You use a range name as demonstrated below

dim x as variant
set x = activecell
Worksheets("Clipboard").range(range_name).Copy(x)

If you only had a small number of of ranges that you'll copy, you
could
create a separate Subroutine for each one, then add buttons to
your
toolbar
and assign each subroutine to a different button.


wrote in message
oups.com...
Hi, THx for the prompt response. No there are various
cellranges
with
different configurations of plant that I want to copy from the
clipboard to rhe points list. I have taken this further now
and
named
these ranges, so I should be able to reference to the names.
With
respect to the formula how do I get that into a macro? Sorry
dont
know
anything about VB. Thanks again for your interest in assisting
me.
Andrew