Excel - Copy cell contents to next blank row

[Wire216]

I use Excel to log calls at my work. On each call I fill out a small template (2 columns, 11 rows). I have a button (macro) that copies the template to a clipboard so I can paste it into a call tracking system.

In the same workbook I also have an area where I record a few of the fields in the template, for each call. Each call goes on a separate row.

What I want to do is add some code to the button macro that not only copies the template to the clipboard, but also copies over the cells that I need to the next blank row of the other area.

To be more specific:

My template that I fill out and clear after each call goes from I4:J15.

I track all of the calls on columns A - G. For each call, the values for columns A, B, F, and G come from the template. Instead of typing the information twice, I'd rather copy it over automatically, on the next blank row.

Any help will be greatly appreciated.

 

[Wire216]

I know that's a little wordy so I'll make it much easier. I want to find out how to copy the contents of a cell in column J to the first blank cell in column A. I should be able to do the rest from there.

Thanks

 

[PHV]

They are at least 2 FAQs in this forum explaining how to find the last used cell.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Wire216]

I looked over the FAQs, and for the most part I understand them.

How can you specify column A when you are searching for the first empty row? My template goes from I4:J15. I start entering information on Row 2 for each call, so the template is down further. I want it to ignore what's in any column after G.

 

[PHV]

What have you tried so far ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Wire216]

I'm very new to VBA.

I was trying to use something like this:

Sub MIT()
Dim lRow As Long
' Find the FIRST EMPTY row by adding 1 to the last row
lRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row + 1

'Paste the data into the first
'COMPLETELY empty row
ActiveSheet.Paste Destination:=Cells(lRow, 1)
End Sub


I can't get it to work though. I know there is an easy solution, but unfortunately I'm not familiar enough with VBA to know how to do it.

 

[PHV]

You may try this:
lRow = ActiveSheet.[!]Columns(1)[/!].Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row + 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Wire216]

What am I missing here?

'This is the first cell I want to copy over to the first
'blank cell in column A

Range("J4").Select
Selection.Copy

lRow = ActiveSheet.Columns(1).Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row + 1

ActiveSheet.Paste

 

[PHV]

lRow = ActiveSheet.Columns(1).Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row + 1
Range("J4").Copy Destination:=Cells(lRow, 1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Wire216]

That works great, thanks!

Here's what I have so far:

lRow = ActiveSheet.Columns(1).Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row + 1
Range("J4").Copy Destination:=Cells(lRow, 1)
Range("J7").Copy Destination:=Cells(lRow, 2)
Range("J5").Copy Destination:=Cells(lRow, 6)

There's only one more thing I have to do. One of the cells contains the result of a formula (J14-J5). How do I copy the value of the cell, and ignore the formula? It's subtracting time and giving the minute difference.

 

[PHV]

Have a look at the PasteSpecial method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Wire216]

When I tried to pastespecial, it altered the formula and put that value in instead.

Here's the code:

Range("J15").Copy
Cells(lRow, 7).Select
Selection.pastespecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

J15 contains the formula (J14-J5), and the value of that cell is currenly 0:10. When I run the above code, it copies over the formula G15-G6, instead of 0:10. The next time I copy it, the formula changes to G16-G7. Why is it copying the formula instead of the value of cell J15?

 

[Wire216]

I just got it. Instead of Paste:=xlPasteAll I used Paste:=xlValues.

PHV, thanks a lot for all the help. I would have been stuck on this for quite awhile without you.