VBA to find Last Row to Copy and Past as value. VBA mess. Please help!

[dstepan]

Hello,
I've been trying to create a macro to find, copy, and paste as a value the last line of this table.
However, I also want to copy the formulas in the last row to the next line in preparation for the next Estimate.
Is this possible?
I have searched here, you-tube, and the web to try to piece together a code but to no success.

I am using excel 2016.

Here is the code I have which I have been trying to play with. I can't even get this to work.

 Sub FindinglastRow()
Dim ws As Worksheet
Dim lastRow As Long
Dim DataRange As Range
Set ws = ActiveWorkbook.Sheets("Summary Data Base")
'~~> Find LastRow in Col A
Set DataRange = Range("A1:M" & lastRow)lastRow = Range("A" & Rows.Count).End(xlUp).Row
lastRow = sht.Cells
End Sub
Dim DataRange As Range
Set DataRange = Range("A1:M" & lastRow)

Summary Data Base

	A	B	C	D	E	F
1	Summary Data Base
2	Date	Estimate Number	Customer Name	Total Amount	Rep Inits	Job No. if Applicable
3	05/19/17	6441	Jane Doe	2933.81	JO	6441
4	05/23/17	6442	C. Scott Marple	20,730.93	drs	6442
5

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 256px;"><col style="width: 227px;"><col style="width: 300px;"><col style="width: 122px;"><col style="width: 77px;"><col style="width: 154px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula F3 =+B3 A4 =+ESTIMATE!$E$5 B4 =+B3+1 C4 =+ESTIMATE!$B$10 D4 =+ESTIMATE!$F$41 E4 =+Rep F4 =+[@[Estimate Number]] <tbody> </tbody>

<tbody>
</tbody>

 

[RMS123]

Maybe something like this:

 Sub FindinglastRow()
Dim ws, ws2 As Worksheet
Dim lastRow1, lastRow2 As Long
Dim DataRange As Range
Set ws = ActiveWorkbook.Sheets("Summary Data Base")
Set ws2 = 'Input destsheet here
lastRow1 = ws.Cells(Rows.Count, 1).End(xlUp).Row
lastRow2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row


ws2.Range("A" & lastRow2 + 1, "M" & lastRow2 + 1).Value = ws.Range("A" & lastRow1, "M" & lastRow1).Value
ws2.Range("A" & lastRow2 + 2, "M" & lastRow2 + 2).Formula = ws.Range("A" & lastRow1, "M" & lastRow1).Formula


End Sub

Regards.

 

[footoo]

Or maybe this :

Sub FT()
Dim rng As Range
Set rng = Cells(Rows.Count, "A").End(xlUp).Resize(, 13)
rng.Copy rng.Offset(1)
rng = rng.Value
On Error Resume Next
rng.Offset(1).SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
End Sub

 

[dstepan]

Thanks to you both for the help. I tried Footoo's suggestion and it worked like a charm.

I am wondering if Footoo's code and another code I have can be combined since they are related.

My code copies the last line of "Summary Data Base" to a new workbook and pastes it as a value, saves and closes the workbook.

Here is the code:

Sub Copy_SumDB()
Dim SrcLastRow As Long, DestLastRow As Long
Dim DestFN As String
Dim DestFile As Workbook

Sheets("summary data Base").Select
SrcLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & SrcLastRow & ":e" & SrcLastRow).Select
Selection.Copy

DestFN = "C:\Users\Stepan\Documents\00000-Inn\Estimates\Estimate Summary"

Set DestFile = Workbooks.Open(DestFN)
DestLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & DestLastRow + 1).Select
ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub[code]

 

[Norie]

If you want to copy the formulas from the last line and replace the formulas with values in the last line then you need to copy the formulas first and then replace the formulas with values in the 2nd last line, not the last line.

 

[dstepan]

Thank you and I will be sure to use the tags correctly next time.

 

[footoo]

I am wondering if Footoo's code and another code I have can be combined since they are related.

My code copies the last line of "Summary Data Base" to a new workbook and pastes it as a value, saves and closes the workbook.

Do you just want to combine the two codes into one macro or do you also want to change your macro to do something different?