Adding Formula after last row of data macro not working

[MHamid]

Hello,

I have thebelow code that will find the last row of data and will add a formulaafterwards. It was working a few minutes ago. I made an update to it andremoved it and now it’s not working. I am not getting an error message. Themacro runs all the way through and does nothing.
What am I missing?

SubWatchlistTable()
'
'WatchlistTable Macro
'

Dim lRow AsLong
Dim lRow2 AsLong
Dim LR As Long

‘Find last rowof data in Table
LR =Range("AY" & Rows.Count).End(xlUp).Row

‘Find last rowof data in Pivot Table
lRow =Range("BS" & Rows.Count).End(xlUp).Row

‘Find last row ofdata in table and add formula after last row of data
Range("AY"& LR + 1).FormulaR1C1 ="=IF(R[-1]C[16]=""(blank)"",""TBD"",R[-1]C[16])"
Range("AZ"& LR + 1).FormulaR1C1 = "=R[-1]C[16]&"" /""&R[-1]C[19]"
Range("BA"& LR + 1).FormulaR1C1 = "=IF(R[-1]C[23]=""(blank)"",R[-1]C[16],R[-1]C[23])"
Range("BB"& LR + 1).FormulaR1C1 = "=""Published on""&TEXT(R[-1]C[18],""mm/dd/yyyy"")&""with ""&R[-1]C[20]&"" rating and""&R[-1]C[21]& ""issues:"""
Range("BC"& LR + 1).FormulaR1C1 = "=R[-1]C[18]"


‘Copy formuladown to last data in pivot table
   Range("AY5:BC" & LR).Copy Range("AY5" &lRow)
    Application.CutCopyMode = False


End Sub

Thank you

 

[Fluff]

Try

   Range("AY5:BC" & LR).Copy Range("AY" & lRow)

 

[MHamid]

I got the first piece to work, however when I want to copy the formulathat I just added using the last row of data it’s not doing what I need.
Row 3 has the header and this will always stay the same
Row 4 will have data already added from the first pivot table
Depending on how many rows are added from the first pivot table, I willneed to find the last row of data used to add the formula in the code belowusing the second pivot table.
The code right now will find the last row of data and will add theformula looking up the data from the second pivot table and it does add it tothe row underneath the last row used from 1^st pivot table.

The issue that I am having now is when I want to copy these formulasusing the last row used, what it does is it will find the last row of data fromthe first pivot table and will skip three rows, then it copies the data fromthe 1^st pivot table to the 4^th row, which lines up withthe last row of data in the second pivot table.

Sub WatchlistTable()
'
' WatchlistTable Macro
 
Dim LR As Long
Dim lRow As Long
 
'Find last row of data in Table
LR = Range("AY" & Rows.Count).End(xlUp).Row
 
'Find last row of data in Pivot Table
lRow = Range("BX" & Rows.Count).End(xlUp).Row
 
'Copy Data from PivotTable into predefined Table and copy until lastrow
Range("AY" & LR + 1).FormulaR1C1 ="=IF(R[0]C[16]=""(blank)"",""TBD"",R[0]C[16])"
Range("AZ" & LR + 1).FormulaR1C1 ="=R[0]C[16]&"" / ""&R[0]C[19]"
Range("BA" & LR + 1).FormulaR1C1 ="=IF(R[0]C[23]=""(blank)"",R[0]C[16],R[0]C[23])"
Range("BB" & LR + 1).FormulaR1C1 ="=""Published on""&TEXT(R[0]C[18],""mm/dd/yyyy"")&""with ""&R[-1]C[20]&"" rating and""&R[-1]C[21]& ""issues:"""
Range("BC" & LR + 1).FormulaR1C1 = "=R[0]C[18]"
 
Range("AY4:BC" & LR).Copy Range("AY" &lRow)
Application.CutCopyMode = False
 
MsgBox "Complete"
 
 
End Sub

Thank you

 

[Fluff]

Are you saying that you simply want to copy the formula down from LR+1 to lRow?

 

[MHamid]

yes

 

[Fluff]

In that case how about

Range("AY" & lr + 1 & ":AY" & lRow).FormulaR1C1 = "=IF(R[0]C[16]=""(blank)"",""TBD"",R[0]C[16])"

 

[MHamid]

Works perfectly!! **** you're good!! LOL

 

[Fluff]

Glad to help & thanks for the feedback

 

[MHamid]

I just added the piece of coding you just helped me with to the finalcode that does everything and it’s not working properly.

The first piece of coding works on its own when I am pulling data fromthe 1^st pivot table. When I include the second piece for the 2^ndpivot table. I am not getting all the data. I should be getting all of the datain the 2^nd pivot table, but it’s not including the first one in thepivot table.

I have two pivot tables in the same sheet with the table. All are nextto each other with a couple blank columns in between. It looks like the datawill only stop at the end of the second pivot table. Am I messing things up byhaving these pivot tables next to each other or is it because when we’re addingthe data from the pivot table it is not lining up?
My goal is to get all of the data from both pivot tables added to themain table one after the other.

Let me know if you need to see the full code.

'Copy Data from 1st PivotTable into predefined Table and copy untillast row
Dim lRow As Long
lRow = Range("BJ" & Rows.Count).End(xlUp).Row
 
   Range("AY4").FormulaR1C1 ="=IF(OR(RC[7]="""",RC[7]=""(blank)""),""TBD"",RC[7])"
   Range("AZ4").FormulaR1C1 = "=RC[7]&"" /""&RC[10]"
   Range("BA4").FormulaR1C1 = "=RC[7]"
   Range("BB4").FormulaR1C1 = "="" ""&CHAR(149)&""     ""&RC[7]"
   Range("BC4").FormulaR1C1 = "=RC[8]"
    
   Range("AY4:BB4").Copy Range("AY" & lRow)
    Application.CutCopyMode =False
    
'Copy Data from 2nd PivotTable into predefined Table and copy untillast row
Dim LR As Long
Dim lRow3 As Long
lRow3 = Range("BX" & Rows.Count).End(xlUp).Row
LR = Range("AY" & Rows.Count).End(xlUp).Row
 
    Range("AY" & LR+ 1 & ":AY" & lRow3).FormulaR1C1 ="=IF(RC[16]=""(blank)"",""TBD"",RC[16])"
    Range("AZ" & LR+ 1 & ":AZ" & lRow3).FormulaR1C1 ="=RC[16]&"" / ""&RC[19]"
    Range("BA" & LR+ 1 & ":BA" & lRow3).FormulaR1C1 ="=IF(RC[23]=""(blank)"",RC[16],RC[23])"
    Range("BB" & LR+ 1 & ":BB" & lRow3).FormulaR1C1 ="=""Published on ""&TEXT(RC[18],""mm/dd/yyyy"")&""with ""&RC[20]&"" rating and""&RC[21]& "" issues:"""
    Range("BC" & LR+ 1 & ":BC" & lRow3).FormulaR1C1 = "=RC[18]"

Thank you

 

[Fluff]

As I've never used pivots & I'm not sure that I can help any further.