Copy Values to Next Empty Row

[unixanalyst]

Hello,

Noob warning. I've managed plenty with formulas but when it comes to macros things are seriously hit and miss, and I think this requires a macro.
I think I could probably manage to record a macro of copying the cells to another sheet, but getting it into the next empty row...?

I copy and paste trading data into a s/s every few days. The s/s extracts the meaningful data and does some calculations to get averages, hi, lo etc.

I would like to be able to copy these calculated values out to another sheet, to the next empty row, to build up a history, before pasting in the next set of trading data.
I guess a button with a macro behind it would be the way forward but I will shout out the noob's battle cry... I'd rather do it with a formula if possible!

TIA

 

[VoG]

This will require VBA. Can you be more specific about the source sheet and range and the destination sheet.

 

[Domski]

Not really possible with formula.

A macro like this would do the trick though:

Sub CopyStuff()

Range("A1:J1").Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

End Sub

It copies A1:J1 from the active sheet to the next available row in column A of Sheet2.

Dom

 

[unixanalyst]

Cool. This is working quite well.

However, all of the values change every time. The old ones aren't staying at the old values.
I've tried adding a second range, which is just =a1, =b1, =c1 etc and the macro runs against that, to try to avoid the formulas being copied, which they are anyway. So the Sheet2 cells contain the formulas from Sheet1, not the values. How do I copy just the values?

 

[Domski]

Like this:

Sub CopyStuff()

Range("A1:J1").Copy

Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

End Sub

Dom

 

[unixanalyst]

Awe....some.

Thanks very much

 

[Tarek_CTG]

Can anyone help me here please ?

http://www.mrexcel.com/forum/excel-questions/891622-copy-paste-data-immediate-next-blank-row.html

 

[SonofFlynn]

This is actually exactly what I need to do.

What would need to change in the code to put those values into a table rather than just bare cells? This is my code thus far:

Sub Logger()
'
' Logger Macro


Range("c22:h22").Copy


Sheets("Data").Range("J" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues


End Sub