Automating copy/paste/paste special when row references change

[Carl LaFong]

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?

 

[Guest]

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

 

[Guest]

If I read his message correctly, Carl needed to paste twice - once for
formulas in a new row, and once to replace the "old" last row with values. So
amending the code slightly to do the second paste:

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

Range("B" & Lastrow & ":W" & Lastrow).Copy
' Paste formulas into new row:
Range("B" & (Lastrow + 1)).PasteSpecial Paste:=xlPasteFormulas, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
' Replace existing row with values:
Range("B" & (Lastrow)).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
' Turn off the "highlight" around the copied cells:
Application.CutCopyMode = False
End Sub
---------------------------------------------

 

[Guest]

"Cowering Dragon" I agree. If there were formulas in row 260, then the 2nd
paste special replaces the formulas with values.

 

[Carl LaFong]

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

Joel:

Thanks for the help.

I am guessing I didn't explain myself well enough in my original post.

You state "It also looks like you code was pasting twice". That is correct
and I need to paste twice.

The first paste must paste the formulas to the next row down.

The second paste must paste the raw values (not the formulas) back to the
source row.

I tried your code. It pastes the formulas down to the correct row, but does
NOT then also paste the values back to the source row, overwriting the
formulas. I need that to happen. The macro must leave the formulas ONLY in
the very last row, so it must overwrite the original row, which becomes the
next to last row after the formulas are pasted down.

In the meantime, I will fiddle with your code to see if I can get it to work
as required.

Thanks again.