How to save calculations.

[123Maximus]

Hello!

In Sheet 1 I have raw data in column A and in B1 and C1 I have calculated values from this column. D1 gives me the date,
i. e =TODAY().
The three values are transferred to B1, C1 and D1 in Sheet 2 (=Sheet1!B1...D1).

I have also a button (named "Delete Report") in Sheet 1 that deletes the data in column A and highlights A1.

Now I want to put in some more code that only saves the data in Sheet 2 and highlights the next emty row when I click the "Delete Report button".

Is this possible?

Thanks in advance!

 

[NoSparks]

try this....

    With Sheets("Sheet2")
        .UsedRange.Cells.Value = .UsedRange.Cells.Value
        .Cells(Rows.Count, "B").End(xlUp).Offset(1).EntireRow.Select
    End With

 

[123Maximus]

Hello!

I can't get it work.

I want to save the data in "Sheet 2" before I delete the raw data in Sheet 1 (=A1-A500)
First time the data are presented on row A2, B2, C2, D2 and E2.
When I delete the raw data in Sheet 1 I want the data in Sheet 2 to remain and then highligt the next row (=A3, B3, C3, D3 and E3) to receive the next data input.



Sub Macro9()
'
' Macro9 Macro
'
' Saves values in Sheet 2 and highlights next row.

' Deletes raw data from A1-A500 in Sheet 1.

' Highlights A1 in Sheet 1.
'
With Sheets("Sheet2")
.UsedRange.Cells.Value = .UsedRange.Cells.Value
.Cells(Rows.Count, "?????").End(xlUp).Offset(1).EntireRow.Select
End With
'

Range("A1:A500").Select
Selection.ClearContents
Range("A1").Select
End Sub

 

[NoSparks]

Perhaps this is what you want

Sub TestMacro()

    Dim writeRow As Long
    Dim nextRow As Long

'move sheet1 range A1:E1 to next row on sheet 2 as values
With Sheets("Sheet2")
    writeRow = .Cells(Rows.Count, "D").End(xlUp).Row
    nextRow = writeRow + 1
    .Range("A" & writeRow).Resize(, 5).Value = Sheets("Sheet1").Range("A1").Resize(, 5).Value
    'highlight next row -- don't know why you would want to do this, but anyway...
    'must be active sheet in order to select anything on sheet
    .Select
    .Range("A" & nextRow).Resize(, 5).Select
End With

'delete raw data from sheet1 A1:A500
With Sheets("Sheet1")
    .Range("A1:A500").ClearContents
    'must be active sheet in order to select anything on sheet
    .Select
    .Range("A1").Select
End With

End Sub

 

[123Maximus]

Thanks...but:
that code deletes the raw data in sheet 1 (Sheet1!A1:A500) and highlights A1 and that's all right but it also deletes my formulas (and values) in Sheet 2 (Sheet2!A2:E2)!
My idea is to save the values in Sheet 2 every time I delete my report in Sheet 1 (the raw data) and then should next row be ready to receive new data from my next report.

However I discovered another problem; in Sheet 2 I use the formula =Sheet1!F28 to transfer data to B2 in Sheet 2 for example. If I copy that formula downwards all the cells will be filled with the same data...

I think all this will be too complex for me to sort out I'm afraid, I'm just an amateur...

Thanks for your help anyway.