I have a workbook with 2 worksheets-- one is where I'm putting the final results I need to send to someone. The other is a sheet that I paste in a range to-- it has 18 columns and a variable number of rows. I then change that range to an Excel Table, add a TOTALS row, set some of the TOTALS to COUNT, and copy them from there into the final results worksheet. (not really a "copy"-- I use a formula on the final results sheet, to get those values individually and put them in the right place, then turn them from formulas to actual values).
The problem is that I used the macro recorder to create this. my table had 139 rows the first time I did it. But when I clear out the sheet and pasted in another range (with more or less rows), and create the table, it still creates a table with 139 rows. If my new table has less than 139 rows, I still get the correct numbers in the totals row, so I get the correct numbers on the final results sheet. But if I paste in a range with more than 139 rows, and then run my macro-- it still just creates a table with 139 rows, and I get wrong numbers in the total row (and on my final results sheet).
How can I make this work so it knows what size table to create from my range based on how many rows I have?
The problem is that I used the macro recorder to create this. my table had 139 rows the first time I did it. But when I clear out the sheet and pasted in another range (with more or less rows), and create the table, it still creates a table with 139 rows. If my new table has less than 139 rows, I still get the correct numbers in the totals row, so I get the correct numbers on the final results sheet. But if I paste in a range with more than 139 rows, and then run my macro-- it still just creates a table with 139 rows, and I get wrong numbers in the total row (and on my final results sheet).
How can I make this work so it knows what size table to create from my range based on how many rows I have?