Making a macro that works on data with a varying amount of rows

[chough10]

I'll preface this by saying I don't know much about vba except for how to record a macro.

Every month I download an excel list of invoices with their customers and amounts and make some reports from that data. I want to record a macro to change the process of making the report to just a few minutes rather than manually redoing the report every month.

The problem I'm having is that the amount of rows in the list of invoices changes(one month it may have 5000 rows while another it has 6000) and I use pivot tables in the report. So when I go to run the macro on a report that has a more rows than what was originally recorded, the pivot table doesn't get the right amount of data because the reference to make the table is always something like $A$1:$G$5000. It doesn't automatically size itself to the length of the data like a normal pivot table would.

Is there any work around to this so that it always gets the right amount of data from report to report? The columns never change it's just the amount of rows that varies.

Thanks in advance for any help.

[jeffreybrown]

Hi chough10,

The typical way to do this is by creating a variable to capture the LastRow

Please Login or Register  to view this content.

Here's a reference that you could use also >> https://www.thespreadsheetguru.com/b...lumn-using-vba

[chough10]

Ah, dynamic is the word I was looking for. This looks like it'll work. Thanks for the help

[jeffreybrown]

You are very welcome and thanks for the rep/feedback.