PDA

View Full Version : Create macro that inserts variable number of rows into tables



mantooth29
03-24-2012, 07:49 AM
Hello,

I am trying to insert a variable number of rows into several tables, based on a dynamic value.
I have attached a copy of the file I am working with.

Each table is in a new tab (Blank, Blank1,Blank2, etc..), so the macro needs to be generalized to active sheet.

The number of rows is determined from calculations performed in the "Control" Sheet, and can be found under "Num of Yes" column. This number will change via macro for each new tab (Blank1, Blank2, etc.) Each row to be inserted has a corresponding Row Heading, found in the Agreements column of Sheet."Control".

I need to produce a code that will insert the number of rows into my table, preferably with the unique corresponding row headings based on values from control sheet.


Here is my attempt at the code, which is clearly not working. This code can also be found in my attached file. I was attempting to modify a basic insert rows code, by defining a17 in the range via the number of rows produced in the Sheet."Control".


Sub Insert_Rows_Loop()
Dim CurrentSheet As Object
Dim NewCell As Object
Dim CellAdd As Object
Dim OldRange As Range
Dim NewRange As Range
Dim a17 As Long

Set NewCell = Sheets("Control").Cell("E2")
Set CellAdd = 16 + Value.CellAdd - 1
Set a17 = CellAdd


' Loop through all selected sheets.
For Each CurrentSheet In ActiveWindow.SelectedSheets
' Insert n rows depending on values of a1 and a2.
CurrentSheet.Range("a16:a17").EntireRow.Insert
Next CurrentSheet
End Sub


Any help is much appreciated!

parttime_guy
04-01-2012, 06:34 AM
I have made some changes to the code
Review the "Report" sheet
Hope this helps

Regards

mantooth29
04-01-2012, 11:22 AM
This is fantastic, and essentially what I ended up doing (Although your code is definitely more efficient). However, I still have a burning question about this...

Assume that all sumif formulas were removed from the REPORT tab, and therefore you could not reference those cell values to hide. In other words, the table would only have column headings and no data values beyond the title.

How would you reference the sumif 0 values in Control tab, to hide the corresponding name column in REPORT?