The code assumes Column B if filled in for every row. the code uses this
column to determine last row. It also looks like you code was pasting twice.
I simplified the original code

Sub Macro2test()
'
' Macro2test Macro
' yada yada
'
' Keyboard Shortcut: Ctrl+f
'
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row

Range("B" & Lastrow & ":W" & Lastrow).Copy
Range("B" & (Lastrow + 1)).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub


"Carl LaFong" wrote:

I am a rookie with macros and even more of a rookie with VBA. I have
reviewed Excel's help file and posts in this and other groups via Google and
have not found anything directly on this point.

I am using Excel 2007.

I need to copy formulas from a row to the next row down and then overwrite
the formulas in the source row with the values themselves. Each row
represents numerical values for the end of each week. The formulas grab the
number from elsewhere in the same file.

I have used the macro recorder and it shows this code in the edit box. I
haven't made any manual edits.


Sub Macro2test()
'
' Macro2test Macro
' yada yada
'
' Keyboard Shortcut: Ctrl+f
'
Range("B260:W260").Select
Range("W260").Activate
Selection.Copy
Range("B261").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("B260").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub





In this case, formulas representing the weekly values are in B260 through
W260. I need to grab the formulas and paste them to B261 through W261, and
then overwrite B260 through W260 with the values per se (not the formulas).

It works as far as it goes, but doesn't allow for the fact that I am
marching down another row every week. A month from today, I will be
operating down around row 264 and the macro as written above is locked into
rows 260 and 261.

Is there any way to force the row references in the macro to increment
weekly, or to simply force the formula paste to the next row down and the
value paste special back to the source row, regardless of row number?