So I pull data from a data base this information may change
My issue is control a then creating a table may change from report to report so when I first do the macro it may have only 100 rows and when I run the macro for another report it may have 150 rows. When running it it will only catch when I did the first report. Is there a way to automatically catch all rows regardless if it has 100 rows or 500 rows of data?

Hi
make your range an Excel Table and correct your Macro accordingly
(BTW there is a VBA dedicated subforum on this forum)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

You can also use CurrentRegion, which is like pressing CTRL+* in a range, and may be better depending on the source of the data/workbook. TABLES are generally better.
Where the variable MyTableRange is DIM as a RANGE
and MyWorkSheet is DIM as a Worksheet and defined to the desired worksheet where the table resides.