Extending formula rows

[John44]

Hi,

I have set up a Sage nominal ledger transaction list in Sage that refreshes automatically with new Sage transactions. I have added to the right hand side of this table additional information brought in to supplement the download to drive project pivots etc. Below is an extract, A - S is the Sage download and formulas from column T onwards.

I have recorded a macro which does this;

- Data refresh all (brings in latest Sage entries)
- Highlights row 2 from column T onwards and copies the formulas down.
- Moves to the pivot tables and does 'data refresh' to update these.

The macro does not work as I want it to as the copy down of the formulas from column T to column Z only copies down to the last row with data in on column S So if new rows are added then the macro doesn't copy the formula down far enough. I thought I could just add zeros manually down a more few thousand rows but then I just have formulas of #N/A which could manifest themselved into other parts of the spreadsheet. I could also embed an additional condition in the IF statements to not formulate if there's #N/As but then I'm doubling up memory and processing time.

Is there an easy macro solution to this?

Thanks

John

 

[RoryA]

You have a table, so you can use the Listobject's DataBodyRange property to work out how many rows you need the formulas to extend to.

 

[John44]

Hi thanks for your reply. I'm a bit of a 'luddite' in this regard. I asked a colleague and he said I need to drop in something like this in the macro after I sent him your comment but he's too busy to help me get this over the line this week;

Worksheets("WORKSHEET").Activate
ActiveSheet.ListObjects.Item(1).DataBodyRange.Select

So do I insert this into the code below somewhere?

John

 

[RoryA]

You can get the last row number using something like:

With Worksheets("WORKSHEET").ListObjects.Item(1).DataBodyRange
lastRow = .Row + .Rows.Count - 1
end with

then concatenate the lastrow into your autofill destination.

 

[John44]

Hi,

So whereabouts in the below should the above be slotted in this code;

Thanks

John

 

[RoryA]

I'm not going to retype that... Please post it as actual code, not a picture.

 

[John44]

Sorry. Is this ok?


Sub Refresh()
'
' Refresh Macro
'

'
Sheets("Nominal Ledger Trans").Select
Range("Table_Query_from_Electrical_15[[#Headers],[TRAN_NUMBER]]").Select
ActiveWorkbook.RefreshAll
Range("T2:AF2").Select
Selection.AutoFill Destination:=Range("T2:AF23558")
Range("T2:AF23558").Select
Sheets("TB Pivot").Select
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("Detailed Pivot for Joe").Select
Range("G115").Select
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
Sheets("Cost Pivot for CVR").Select
Range("E68").Select
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
End Sub

 

[RoryA]

Like this:

Sub Refresh()
'
' Refresh Macro
'

'
ActiveWorkbook.RefreshAll
With Sheets("Nominal Ledger Trans")
    With .ListObjects.Item(1).DataBodyRange
        lastRow = .Row + .Rows.Count - 1
    end with
    .Range("T2:AF2").AutoFill Destination:=.Range("T2:AF" & lastrow)
end with
Sheets("TB Pivot").PivotTables("PivotTable4").PivotCache.Refresh
Sheets("Detailed Pivot for Joe").PivotTables("PivotTable4").PivotCache.Refresh
Sheets("Cost Pivot for CVR").PivotTables("PivotTable4").PivotCache.Refresh
End Sub