Macro: Copy selected cell to another sheet next blank row

redsh0es

New Member
Joined
Mar 15, 2011
Messages
16
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!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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
 
Upvote 0
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)
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top