+ Reply to Thread
Results 1 to 10 of 10

Copying and PAsting using MACROS

  1. #1

    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


  2. #2
    Don Guillett
    Guest

    Re: Copying and PAsting using MACROS

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


    --
    Don Guillett
    SalesAid Software
    [email protected]
    <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >




  3. #3

    Re: Copying and PAsting using MACROS

    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


  4. #4
    Duke Carey
    Guest

    Re: Copying and PAsting using MACROS

    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)


    "[email protected]" 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
    >
    >


  5. #5

    Re: Copying and PAsting using MACROS

    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


  6. #6
    Duke Carey
    Guest

    Re: Copying and PAsting using MACROS

    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.


    <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >




  7. #7

    Re: Copying and PAsting using MACROS

    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.
    >
    >
    > <[email protected]> wrote in message
    > news:[email protected]...
    > > 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
    > >



  8. #8
    Duke Carey
    Guest

    Re: Copying and PAsting using MACROS

    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


    "[email protected]" 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.
    > >
    > >
    > > <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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
    > > >

    >
    >


  9. #9
    Duke Carey
    Guest

    Re: Copying and PAsting using MACROS

    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

    "[email protected]" 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.
    > >
    > >
    > > <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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
    > > >

    >
    >


  10. #10

    Re: Copying and Pasting using MACROS

    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
    >
    > "[email protected]" 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.
    > > >
    > > >
    > > > <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > > 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
    > > > >

    > >
    > >



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1