Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Macro: Copy selected cell to another sheet next blank row

This is a discussion on Macro: Copy selected cell to another sheet next blank row within the Excel Questions forums, part of the Question Forums category; Hello, Please help me do macro on this please: first sheet, named "SUMMARY" second sheet, it is named randomly, let's ...

  1. #1
    New Member
    Join Date
    Mar 2011
    Posts
    16

    Exclamation Macro: Copy selected cell to another sheet next blank row

    Hello,

    Please help me do macro on this please:

    first sheet, named "SUMMARY"
    second sheet, it is named randomly, let's give a name "STAR"

    what will I do is, if select the cell E3 as an active cell, the data entered on the active cell will copy to the "SUMMARY" sheet Column A on the next blank row.

    Then, if I add another sheet (let's name it "MOON"), then I select the cell E3 as an active cell again, the data should copy again to "SUMMARY" sheet column A on the next blank row.

    I thought it was simple but I cannot find any macro for it. You see, I just find codes for macro. I don't know how to create or analyze. Please help.. Thank you very much!

  2. #2
    Board Regular
    Join Date
    Apr 2004
    Posts
    6,715

    Default Re: Macro: Copy selected cell to another sheet next blank row

    When would you like this action to occur?
    Yesterday I felt on top of the world. Today its falling in on me.

  3. #3
    New Member
    Join Date
    Mar 2011
    Posts
    16

    Default Re: Macro: Copy selected cell to another sheet next blank row

    Thank you for your prompt reply.
    Okay, here's what I'm thinking to happen:

    1. I select the cell E3 as an active cell
    2. press the button (with the macro on it)
    3. the data on E3 will copy to the "SUMMARY" sheet Column A on the next blank row.


    I found this just now. I tried this but it copies the whole row. I just wanted the active cell and it displays the date, I do not need that.

    Sub CpyAct()
    ActiveCell.EntireRow.Copy Destination:=Sheets("SUMMARY").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Sheets("SUMMARY").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Date
    End Sub

  4. #4
    Board Regular
    Join Date
    Apr 2004
    Posts
    6,715

    Default Re: Macro: Copy selected cell to another sheet next blank row

    Will it always be cell E3 ?
    Yesterday I felt on top of the world. Today its falling in on me.

  5. #5
    New Member
    Join Date
    Mar 2011
    Posts
    16

    Default Re: Macro: Copy selected cell to another sheet next blank row

    Yes it's always E3 as the source cell.

  6. #6
    Board Regular
    Join Date
    Apr 2004
    Posts
    6,715

    Default Re: Macro: Copy selected cell to another sheet next blank row

    ActiveCell.Copy Destination:=Sheets("SUMMARY").Range("A" & Rows.Count).End(xlUp).Offset(1)

    or
    Range("e3").Copy Destination:=Sheets("SUMMARY").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Yesterday I felt on top of the world. Today its falling in on me.

  7. #7
    Board Regular Snakehips's Avatar
    Join Date
    May 2009
    Location
    Coventry UK
    Posts
    2,266

    Default Re: Macro: Copy selected cell to another sheet next blank row

    redshOes,

    Try this code. Put the code in either the Worksheet_SelectionChange OR Worksheet_Change, in each of the sheets from which you wish to capture E3. Which is best depends upon whether or not you wish to alter the value in E3.

    Code:
     
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Target.Cells.Count > 1 Then
    If Not Intersect(Target, Range("E3")) Is Nothing Then
    MyRow = Sheets("SUMMARY").Range("A65536").End(xlUp).Row + 1
    Sheets("SUMMARY").Range("A" & MyRow).Value = Target.Value
    End If
    End If
    End Sub
     
     
    'OR
     
     
     
     
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Target.Cells.Count > 1 Then
    If Not Intersect(Target, Range("E3")) Is Nothing Then
    MyRow = Sheets("SUMMARY").Range("A65536").End(xlUp).Row + 1
    Sheets("SUMMARY").Range("A" & MyRow).Value = Target.Value
    End If
    End If
    End Sub
    Hope that helps.
    Last edited by Snakehips; Feb 27th, 2012 at 08:44 AM. Reason: Correction
    Tony

    AbUsing Excel 2002, 2007, 2010
    This line will be updated just as soon as I come up with something, original, witty, profound or interesting. Please don't hold your breath!

  8. #8
    New Member
    Join Date
    Mar 2011
    Posts
    16

    Default Re: Macro: Copy selected cell to another sheet next blank row

    I tried both, but it copies the whole row

  9. #9
    New Member
    Join Date
    Mar 2011
    Posts
    16

    Default Re: Macro: Copy selected cell to another sheet next blank row

    Hi Tony!

    I don't to get your instructions [i'm really sorry for being dumb on this]

    this what I've done.

    Sub Worksheet_SelectionChange()
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Target.Cells.Count > 1 Then
    If Not Intersect(Target, Range("E3")) Is Nothing Then
    MyRow = Sheets("SUMMARY").Range("A65536").End(xlUp).Row + 1
    Sheets("SUMMARY").Range("A" & MyRow).Value = Target.Value
    End If
    End If
    End Sub
    What is wrong with this?

  10. #10
    Board Regular Snakehips's Avatar
    Join Date
    May 2009
    Location
    Coventry UK
    Posts
    2,266

    Default Re: Macro: Copy selected cell to another sheet next blank row

    redshOes,

    Just remove the first line so that you have....

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Target.Cells.Count > 1 Then
    If Not Intersect(Target, Range("E3")) Is Nothing Then
    MyRow = Sheets("SUMMARY").Range("A65536").End(xlUp).Row + 1
    Sheets("SUMMARY").Range("A" & MyRow).Value = Target.Value
    End If
    End If
    End Sub
    This will put the value of E3 into SUMMARY every time you select E3. Give it a try. If not suitable then we can put similar code to a button so that it only copies E3 when it is selected and you click the button.
    Tony

    AbUsing Excel 2002, 2007, 2010
    This line will be updated just as soon as I come up with something, original, witty, profound or interesting. Please don't hold your breath!

Page 1 of 2 12 LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com