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,761

    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
    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!

    Please remember - we cannot see your workbook nor read your mind.
    Help us to help you, post clear detail of what you have and what you want - from the start.

    A screen shot can save a thousand words!
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker
    If posting VBA code, please use Code Tags - see: here

  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,761

    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
    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!

    Please remember - we cannot see your workbook nor read your mind.
    Help us to help you, post clear detail of what you have and what you want - from the start.

    A screen shot can save a thousand words!
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker
    If posting VBA code, please use Code Tags - see: here

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