Macro to cut and paste data on the next empty row

[mitchellj]

Hi, I've made a spreadsheet where sheet 2 is a calculator of sorts, I input all relevant data and the calculations are filled in via formulas. Sheet 3 is a records sheet. I'm trying to create a macro that can take all data from sheet 2 once the calculations are done and store it in sheet 3. The issue is that I need to store it in the next empty row but every time I run my macro it deletes the previous record. Also, when I cut the information from the calculator sheet I need to keep the formulas. If anyone knows how I could go about this I would very much appreciate it. I have attached some screenshots to help show you what I'm talking about.

Screen Shot 2015-08-26 at 8.17.52 am.png
Screen Shot 2015-08-26 at 8.23.07 am.png

[skywriter]

It would be best if you posted your workbook.

View Pic

[bakerman2]

Please Login or Register  to view this content.

[mitchellj]

I'll post it now sorry!

[mitchellj]

Sheet Blank1.xlsm

This is the sheet with all formulas. I'm trying to take all the information from sheet 2 to sheet 3, sheet 1 is not relevant to this question.

[mitchellj]

Thanks bakerman2, I'll give it a go, I have no experience with code though so no guarantees!

[bakerman2]

Or

Please Login or Register  to view this content.

[mitchellj]

That seems to be working, but it doesn't clear the cell after copying and pasting it into sheet 3. Is there a way I could make it do this? I'm just not sure how to do that where the formulas are concerned as I need to clear the cell but keep the formula. Thanks again

[mitchellj]

Bakerman2

Sorry to be a pain but I have literally no experience with this,

What exactly do these lines of the code mean?

Cells(fRow, 1).Value = wsCalc.Range("B1").Value
Cells(fRow, 3).Value = wsCalc.Range("B2").Value

I'm unsure and so I can't replicate them for the rest of the data.

Cheers

[bakerman2]

Please Login or Register  to view this content.

calculate rownumber first available empty row

Please Login or Register  to view this content.

cells arguments are cells(rownumber,columnnumber)
so if fRow = 10 then cells(fRow,1) means A10, cells(fRow,2) means B10, and so one

[mitchellj]

Ah okay, I used your updated code and it is almost perfect. The only issue remaining is that on the bet records sheet there is a column called exchange and I don't have a column section on the calculator sheet so when i run the macro it copies the word exchange into the exchange column. It does the same thing with 'profit/loss' and 'return (%)'. With those two it is also copying the title of the column instead of the value calculated in the calculator sheet. If you had any suggestions on how to fix this I would be eternally grateful!

Cheers

[bakerman2]

I did these things on purpose because
1. I can't figure out which value from Calculator sheet is to be copied into Exchange column
2.Same for Profit/Loss and Return % column

So provide cell address on calculator sheet for these 3 columns

[mitchellj]

I used your code and tweaked it and I've got it working perfectly! Thank you so much for your help

Cheers

[bakerman2]

You're welcome and thanks for the rep