Recorded a Macro and now when I try to use it on a different table it will not run because it references Table9 how can I run this for a differ table?

Question

Below is the VBA code:

Please help - I need this to run for different tables - I am hoping there is an easy fix to fix that it references table9. I am hoping this is a very easy fix that I am overlooking - please explain as I am just now starting to use VBA more and more and this would be a great stepping stone.

    Cells.Select

    Cells.EntireColumn.AutoFit

    ActiveWindow.ScrollColumn = 2

    ActiveWindow.ScrollColumn = 3

    ActiveWindow.ScrollColumn = 4

    Columns("K:L").Select

    Selection.Font.Bold = True

    Selection.Style = "Currency"

    Range("Table9[[#Headers],[Invoiced]]").Select

    Selection.End(xlDown).Select

    ActiveCell.Offset(2, 0).Range("Table9[[#Headers],[Customer '#]]").Select

    ActiveCell.FormulaR1C1 = "Past Due"

    Range("K7").Select

    ActiveCell.Offset(-6, 1).Range("A1:A2").Select

    Selection.End(xlDown).Select

    ActiveCell.Offset(2, 0).Range("Table9[[#Headers],[Customer '#]]").Select

    Application.CutCopyMode = False

    ActiveCell.FormulaR1C1 = "=SUMIF(Table9[Past Due],"">0"",Table9[Balance])"

    Range("L7").Select

    ActiveCell.Offset(-6, -1).Range("Table9[[#Headers],[Customer '#]]").Select

    Selection.End(xlDown).Select

    ActiveCell.Offset(3, 0).Range("Table9[[#Headers],[Customer '#]]").Select

    ActiveCell.FormulaR1C1 = "Total"

    ActiveCell.Offset(1, 0).Range("Table9[[#Headers],[Customer '#]]").Select

    ActiveCell.Offset(-7, 1).Range("Table9[[#Headers],[Customer '#]]").Select

    Selection.End(xlDown).Select

    ActiveCell.Offset(3, 0).Range("Table9[[#Headers],[Customer '#]]").Select

    Application.CutCopyMode = False

    ActiveCell.FormulaR1C1 = "=SUM(Table9[Balance])"

    Range("L8").Select

End Sub

I would remove all reference to Activecell and Table9. Instead, at the top of the code, take the user's Activecell as input, check if `ActiveCell.ListObject` exists. If yes, continue with the code using that `ListObject`. — Toddleson, Aug 26 '22 at 19:35
Doing actions like `Selection.End(xlDown).Select` will not work if you want this code to be repeatable. You need to find more reliable ways to get the ranges from the table. Use the ListColumns and ListRows collections to search the table. — Toddleson, Aug 26 '22 at 19:35
[Here`s how you can avoid using Select in your code](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — cybernetic.nomad, Aug 26 '22 at 19:50

Recorded a Macro and now when I try to use it on a different table it will not run because it references Table9 how can I run this for a differ table?

0 Answers0