Copy row to next open row, then paste-value source

[KW1M]

Been all through the search and not finding what I need -- I'ma VBA virgin... Think the problem is so simple, I can't find an example to build from!

I have a simple tab/report (blank columns in A, headers, ... just showing data in a sample B2:I3 in this case.

	7/16	81	2300.15	156	158	2400	0	1800
	7/23	69	1500	200	220	2600	200	2000

This tab has values from previous week's reports.

What I want to do is copy (or fill down) the last row with data to the row underneath it. The references are set up to correctly duplicate so my other tabs will report cleanly.
Then the row I just copied (Row 3 in the above example) I would like to copy and paste-value to preserve the data. I've got the rest of the simple macro to clear contents on the other tabs and save the file under a new name -- but this is driving me crazy.

Any suggestions?

Thanks, Martin in NH

 

[MARK858]

Maybe something like the code below?

Sub fllDwn()
    With Intersect(Rows(Columns("B:I").Find("*", , xlValues, xlPart, xlByRows, xlPrevious, False).Row), Columns("B:I"))
        .Resize(2).FillDown
        .Offset(1).Value = .Offset(1).Value
    End With
End Sub

 

[KW1M]

Really close - thanks... I see it filling down (good). But I would like to keep the top row as the preserved (paste-value row) and the bottom row is always the one with formulas.

Ran it twice to simulate week 3 and it dupped the bottom.

It should have looked like
7/16 (now just pasted values)
7/23 (pasted values)
7/30 (live links)

Then I need to figure out how to preserve formatting -- the cells are formatted for accounting (1st 2 with decimals, remaining 4 w/o decimal). Odd that it's not respecting the format on the sheet...

Thanks - I have to read up on what your code is doing.

 

[MARK858]

Sub fllDwn()
    With Intersect(Rows(Columns("B:I").Find("*", , xlValues, xlPart, xlByRows, xlPrevious, False).Row), Columns("B:I"))
        .Resize(2).FillDown
        .Value = .Value
    End With
End Sub

7/16 if it is a real date formatted and the only cell in the selection to filldown will give 7/17 not 7/23. To get 7/23 in a filldown it would have to be 2 cells 7/09 & 7/16 selected and filled down.

 

[KW1M]

Thanks Mark -- perfect solution!

I had to change formatting to currency and that seems to work. Accounting gave me fits.

Appreciate the help! Martin

 

[MARK858]

You might want to try...

Sub fllDwn()
    With Intersect(Rows(Columns("B:I").Find("*", , xlValues, xlPart, xlByRows, xlPrevious, False).Row), Columns("B:I"))
       .AutoFill .Resize(2), xlFillDefault
        .Value = .Value
    End With
End Sub

 

[KW1M]

Thanks Mark -- works like a charm...

If I may ask... wouldn't this code below set the numeric value of a cell to "1"?

    Range("E8:E13").Select
    ActiveCell.FormulaR1C1 = "1"

I'm resetting one of my tables where values may be 2, 3, 1... and want to start fresh with 1.

 

[KW1M]

Figured it out... need to set value - this worked:

    Dim cellRange As Range
    Set cellRange = Range("E4:E6")
    cellRange.Value = "1"

Repeated as necessary as my ranges down column E are not contiguous.

Whew -- I'm in business. Thanks Mark for all the help!

 

[MARK858]

Sets it as a text of 1, lose the brackets if you want it as a numeric value...

cellRange.Value = 1