Automatically copy entire row

  • Hi, I am trying to find a way to text from Sheet1 onto sheet2...the
    situation is as follows...Sheet1 is a food menu and each row has in its cells an
    item, an amount and a price. and
    each row calculates the totals cost.
    Is it possible to copy only the row of cells I, pick and have it sent to sheet 2 just by clicking that row somewhere?
    I tryed everything I could think of.. so far not even close...Cut and
    paste was my only other means, Thank you for any help you can offer.


    James

  • Try this


    You will need to assign the MACRO a short code such as Ctrl A, but works simple enough


    Code
    With Selection.EntireRow
    .Copy Destination:=Sheets("Sheet2").Range("A1")
    End With


    Good Luck


    Ray

    ________________________________________


    Good Luck!


    Ray :nana:

  • Hi,


    paste the code onto sheet module
    when you w-click on any cell in column A, its row will be copied and
    pasted in the first available row in Sheet2



    hoe it helps

  • There's a lot of ways to solve this problem.


    1) Automate the copy/cut and paste process by using a macro. Excel even has a record function. Just record the macro as you are doing it, and the code will write itself.


    2) Consider clicking on the TOP of a row, or the SIDE of a column to make it know you want the ENTIRE row. It's easier than selecting it all, and then trying to copy it.


    3) Let one of these guys teach you VBA. I see a couple posts already.


    4) Set up 'sheet 2' to automatically pull the information from 'sheet 1" by linking the two sheets together. (--> in sheet 2: =Sheet1!A1)


    5) Combine the two sheets so that it is no longer necessary to copy the information from one place to another.

  • Hi,


    here's how to set,


    1) Hit F11(Function11) key with holding down Alt key to open VB Editor
    2) W-click on the Sheet1 icon on the leftside
    3) Paste the code onto somewhere on the rightside
    4) HIt F11 with holding down Alt key again to get back to Excel


    then, if you w-click on any cell in column A contains value on sheet1, its value will be copied and pasted onto sheet2.


    hope it helps


    jindon

  • So much good help and I cant seem to get a result...I think I am missing a step


    I have my spreedsheet open to my work
    I hit alt and f11
    I receive a screen dark blank screen named Visual Basic


    What Sheet 1 icon ? are you refer to on the left do I w-click on
    I see nothing of a sheet1

  • Here we go, thanks..
    MS Excel


    Sheet 1 (this is my main menu order sheet) all items are listed on this sheet1
    Sheet 2 (this is were I need to repeat my selections from sheet1)
    Staring from sheet1 I hold down (alt) +press f 11
    up pops Microsoft Visual Basic...
    Sheet1 icon on left ? what does this look like...I see X Icon.....I see User form icon and a Save icon and the rest of the tool bar...

  • Hi,


    OK,


    here's another way to paste.


    "Right click" on the sheet tub which you want the data to copy from and select "View code???", (i'm not sure the exact indication, but the bottom one), then paste the code in the blank on the right side.

  • OK Jindon
    I have it working with a slight problem (you did good)


    When I w-click on the row of the A column it will send it to sheet2, that part works......But if I w-click on second item it will replace the first item on sheet 2. so I can only show 1 item.


    It keeps replacing what is there and always on row 17
    any thoughts (you got me almost there, Half way home)

  • Hi,


    Do you want to replace what is in 17th row on sheet2 with anything you w-click on sheet1? if it that is the case,


    Code
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        Dim ws2 As Worksheet, lastA As Long
        Set ws2 = Sheets("Sheet2")
        With Target
            If .Column <> 1 Then Exit Sub
            Rows(.Row).Copy Destination:= _
            ws2.Rows("17")
        End With
        Set ws2 = Nothing
    End Sub
  • OK JINDON.....You did it for me, Thanks a million for your help


    I just needed to keep all the A cells filled on sheet2 so nest available would be the following cell and my new item would not replace but go to the next cell......works great now
    Again thank for all your help and also thanks to TownDawg.


    Hope you don't mind if I call on you guys again some confussed day.


    James

  • Need a little more help


    Can I change the MACRO code to allow the Sheet1 transfer to sheet2 to limit the Rows to only tranfer column A,B,C,D,E,F.......
    When I w-click on A now it sends all of the row and replaces everything after column F....which is data I need to keep


    James

  • Hi, James
    here's a code


    Code
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        Dim ws2 As Worksheet
        Set ws2 = Sheets("Sheet2")
        With Target
            If .Column <> 1 Then Exit Sub
            .Resize(, 5).Copy Destination:= _
            ws2.Range("a17")
        End With
        Set ws2 = Nothing
    End Sub
  • Jindon
    Need a little more help


    Can I change the MACRO code to allow the Sheet1 transfer to sheet2 to limit the Rows to only tranfer column A,B,C,D,E,F.......
    When I w-click on A now it sends all of the row and replaces everything after column F....which is data I need to keep


    James

  • Hi,


    Right, up to col.F


    Code
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 
        Dim ws2 As Worksheet 
        Set ws2 = Sheets("Sheet2") 
        With Target 
            If .Column <> 1 Then Exit Sub 
            .Resize(, 6).Copy Destination:= _ 
            ws2.Range("a17") 
        End With 
        Set ws2 = Nothing 
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!