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.

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" wrote:

> 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.
>

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?

On 11 Feb, 14:16, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
> 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" wrote:
> > 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.- Hide quoted text -
>
> - Show quoted text -

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.

"bjohnson" wrote:

> 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?
>
> On 11 Feb, 14:16, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
> > 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" wrote:
> > > 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.- Hide quoted text -
> >
> > - Show quoted text -
>
>

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

"bjohnson" wrote:

> 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?
>
> On 11 Feb, 14:16, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
> > 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" wrote:
> > > 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.- Hide quoted text -
> >
> > - Show quoted text -
>
>

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

On 11 Feb, 15:33, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
> 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.
>
>
>
> "bjohnson" wrote:
> > 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?
>
> > On 11 Feb, 14:16, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
> > > 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" wrote:
> > > > 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 columnB
> > > > 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.- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

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


"bjohnson" wrote:

> 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?
>
> On 11 Feb, 14:16, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
> > 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" wrote:
> > > 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.- Hide quoted text -
> >
> > - Show quoted text -
>
>

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.

"JLGWhiz" wrote:

> Hold on, I just re-read your second post. I see what you mean now.
>
> "bjohnson" wrote:
>
> > 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?
> >
> > On 11 Feb, 14:16, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
> > > 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" wrote:
> > > > 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.- Hide quoted text -
> > >
> > > - Show quoted text -
> >
> >