PDA

View Full Version : VBA to auto-add rows to 3 Tables (or one and auto-resize 2)



Nytemare
07-25-2013, 05:54 AM
Hello,

Apologies, I've done my due diligence and tried to solve this based on adapting other solutions from this and other forums, and I just don't know VBA syntax well enough to make it work.

I have 3 tables, on 3 different sheets, in an Excel 2010 workbook.

The Table where data is entered is named "Master" and lives on sheet "Master!"
The two Tables which pull data from "Master" are "Hostname" which lives on sheet "Hostnames!", and "Moves" which lives on sheet "Equip Moves!"

Can someone please write a VBA for me that will:
1) Auto-add a row to "Master" when the bottom row, first column, "Building", (or any column) of "Master" has been populated
2) Auto-resize "Hostname" and "Moves" to the same number of rows

OR

Add a row to all 3 tables when the bottom row of "Master" is populated (preferably the first column, but any data in that row works).

Making a module to just add rows to all three is acceptable, as I know how to button-activate it. But it must be relative to add to the bottom of the table, regardless of table row count.

I would use a Dynamic Range, but all my table formulas call the Table Columns, and I use calculated columns. If I can make the table expand like a dynamic range, I'd be in good shape, but I don't think that's possible.



Thanks in advance. Hope I provided adequate specificity. I've tried doing it all 3 ways and utterly failed. My preference is the first method with the numbered conditions, but I'm practical and will take what I can get. =)

SamT
07-25-2013, 06:37 AM
I would use a Dynamic Range, but all my table formulas call the Table Columns, and I use calculated columns. If I can make the table expand like a dynamic range, I'd be in good shape, but I don't think that's possible.
I don't understand the problem, define the tables as using the entire column(s) to the bottom of the sheet then use a LastRow function to find the bottom used row.

LastRow = Range("B" & Rows.Count).End(xlUp.RowAdd 1 to LastRow to get the next empty row number

If you're using a sheet Change Event sub, then CurrentRow =Target.Row

If you're calulating Columns then Cells(CurrentRow, CalkedColNum) returns the correct Range or Cell.