copy formulas on a weekly basis to next available

[bjohnson]

I have a large spreadsheet where I need to automatically copy formulas
in row 3 (range B3:AH3) to the next available row starting in column B
each week - column A already lists the weekly dates. In addition, I
need to make sure the previous weeks row performs a copy past value
onto itself so as not to continue to have the paste links on that row.

I am trying to write this macro so that the user can just click a
button and have it perform in the background.

Any help would be greatly appreciated.

 

[JLGWhiz]

Sub copyRwToCol()
Dim lstRw As Long
Dim c As Range
For Each c In Range("B3:AH3")
lstRw = Cells(Rows.Count, 2).End(xlUp).Row
If Not c Is Nothing Then
c.Copy
Range("B" & lstRw + 1).PasteSpecial Paste:=xlPasteValues
End If
Application.CutCopyMode = False
Next
End Sub

 

[bjohnson]

Thank you! However, this copies the row's formulas into the column
and down - I need it to copy the formulas in row 3 (B3:AH3) to the
next available row that has emtpy cells starting in column B - all the
way across the row, not down the column. Then I need it to change the
formulas to values in the row previous to the row that the formulas
were just copied to.....does that make sense?

 

[JLGWhiz]

When I tested it on my system, it did not copy the formulas. That is why I
used PasteSpecial instead of a direct paste. If you check in the formula
window at the top, you will see that only the cell values are pasted to
column B. If you find otherwise, then there is a malfunction in your system.

 

[JLGWhiz]

Hold on, I just re-read your second post. I see what you mean now.

 

[bjohnson]

I'm sorry - I am not making sense....the script you wrote works, but
when it pastes, it is transposing the row of formulas/value straight
down column B - I need it to paste the row across the next available
row

 

[JLGWhiz]

If I read it right this time, this should do what you want.

Sub copyRwToCol()
Dim lstRw As Long
lstRw = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
Range("B3:AH3").Copy Range("B" & lstRw + 1)
Set ChRng = Range("B" & lstRw & ":AH" & lstRw)
ChRng.Value = ChRng.Value
End Sub

 

[JLGWhiz]

If you are not using absolute references on your formulas, you might be
disappointed in the results. Your new row of formulas will change their
references relative to their new location without the $ to make them absolute.