Macro: Copy selected cell to another sheet next blank row

S

SteveO59L

Well-known Member

Joined

Apr 21, 2004

Messages

7,899

• Feb 27, 2012

• #2

When would you like this action to occur?

 

Upvote 0

R

redsh0es

New Member

Joined

Mar 15, 2011

Messages

16

• Feb 27, 2012

• #3

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

Click to expand...

 

Upvote 0

S

SteveO59L

Well-known Member

Joined

Apr 21, 2004

Messages

7,899

• Feb 27, 2012

• #4

Will it always be cell E3 ?

 

Upvote 0

R

redsh0es

New Member

Joined

Mar 15, 2011

Messages

16

• Feb 27, 2012

• #5

Yes it's always E3 as the source cell.

 

Upvote 0

S

SteveO59L

Well-known Member

Joined

Apr 21, 2004

Messages

7,899

• Feb 27, 2012

• #6

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

Snakehips

Well-known Member

Joined

May 17, 2009

Messages

6,029

Office Version

1. 365

Platform

1. MacOS

• Feb 27, 2012

• #7

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: Feb 27, 2012

Upvote 0

R

redsh0es

New Member

Joined

Mar 15, 2011

Messages

16

• Feb 27, 2012

• #8

I tried both, but it copies the whole row

 

Upvote 0

R

redsh0es

New Member

Joined

Mar 15, 2011

Messages

16

• Feb 27, 2012

• #9

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

Click to expand...

What is wrong with this?

 

Upvote 0

Snakehips

Well-known Member

Joined

May 17, 2009

Messages

6,029

Office Version

1. 365

Platform

1. MacOS

• Feb 27, 2012

• #10

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