Macro help

C

ChuckF

Can a macro start from wherever cell the active cursor is on? or
perhaps from the cell that the macro button is clicked?

What I am trying to do is copy the data from F(whatever) to whatever
the last empty cell is in the row holds the active cursor. Or I can
create buttons for the macro to run, and it could copy the information
in that particular row.

I don't know if this makes sense or not...I would be greatful for any
help.
 
D

Don Guillett

This might help

lr=cells(rows.count,activecell.column).end(xlup).row
range("f2:f"&lr).copy sheets("sheet2").range("a2")
 
C

ChuckF

Don,
Thank you for your time...not sure what I am doing wrong, but this
isn't working for me.

Again, I'm not sure if it is a macro that I need. basically what I am
trying to do is this...
I have a column to enter a date field when paperwork is recieved. Once
this data is entered, I want the data copied to another cell, so that I
can keep a running total of dates the paperwork was recieved, but I
want to keep the actual cell where we input this data clear. SO...I
enter the date of 4/1/2006 in cell J2. 4/1/2006 is copied to Z2, and
J2 is cleared. I then enter 4/5/2006 in cell J2 and it copies 4/5/06
to AA2.

Any help would be wonderful.
Thank you again for your time.
 
S

Scoops

Hi ChuckF

Try this in your worksheet's code (right-click the sheet tab and select
View Code):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim newcell As Range
If Target <> Range("J2") Then Exit Sub
Application.EnableEvents = False
Set newcell = Range("IV2").End(xlToLeft).Offset(0, 1)
If newcell.Column < 26 Then
Range("Z2").Value = Target
Else
newcell.Value = Target
End If
Set newcell = Nothing
Target.Select
Target.ClearContents
Application.EnableEvents = True
End Sub

I don't know if you wanted Z to be the first column to include your
data but I put the check in anyway, remove it if it's not necessary.

Be aware that there are only 256 columns in a worksheet. If you're
going to be entering a lot of data you might want to consider going
down rows rather than across columns.

Regards

Steve
 
C

ChuckF

Steve,
Many thanks...I have tested this in a dummysheet, and it appears to
work beautifuly. Can you tell me what adjustments need to made for the
following to occur...

Only when I enter a date in column L2 AND M2 would I like the
information copied from J2 to Z2(or 3 or wherever)
I would also like this to occur in these colulmns....not just the 2nd
row.
 
S

Scoops

Hi ChuckF

I *think* you're changing the column emphasis from J to L&M, if that's
the case, try this:

Dim newcell As Range
With Target
If Cells(.Row, "L") = "" Or Cells(.Row, "M") = "" Then Exit Sub

Application.EnableEvents = False

Set newcell = Cells(.Row, "IV").End(xlToLeft).Offset(0, 1)
With Cells(.Row, "J")
If newcell.Column < 26 Then
Cells(.Row, "Z").Value = .Value
Else
newcell.Value = .Value
End If
.ClearContents
End With
End With
Set newcell = Nothing
Application.EnableEvents = True
End Sub

Regards

Steve
 
C

ChuckF

I'm not sure what is meant by column emphasis....what I am trying to do
is IF L AND M have a value, then take the information from J and copy
it out to Z (or whatever column is next) J represents a vlookup
formula so I would need to copy the value of J to Z.

J is the date of the visit
L is the date that I recieved the photo's from the visit
M is the date that I recieved the inspection report from the visit.

J is a Vlookup formula taking the date from another spreadsheet.

If I enter a date in L AND M I would like all 3 columns to go blank,
but keep the date that was orgionally in J off to the right somewhere
so that I can keep a running total of how many visits have been
completed.
 
S

Scoops

Good Morning ChuckF

This will remove data from J, L and M:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim newcell As Range
With Target
If Cells(.Row, "L") = "" Or Cells(.Row, "M") = "" Then Exit Sub

Application.EnableEvents = False

Set newcell = Cells(.Row, "IV").End(xlToLeft).Offset(0, 1)
With Cells(.Row, "J")
If newcell.Column < 26 Then
Cells(.Row, "Z").Value = .Value
Else
newcell.Value = .Value
End If
.ClearContents 'delete this line to preserve your VLOOKUP
End With
Range(Cells(.Row, "L"), Cells(.Row, "M")).ClearContents
End With
Set newcell = Nothing
Application.EnableEvents = True
End Sub

I note your comment that J contains a VLOOKUP so I guess you might not
actually want to clear the contents, thereby deleting the formula too.
If that's the case then delete the marked line above.

Regards

Steve
 
C

ChuckF

And my wife doesn't understand why I love excel so much...

Steve, you have made my week. Thank you SO much!!
 
Top