mantooth29
New Member
Hello,
I am trying to insert a variable number of rows into several tables, based on a dynamic value.
Here is a link to the file I am working with:
https://docs.google.com/file/d/0B-Bd0UJtnQ0RSVp3TjNZUjlRQkdWcjRhd2xJU3VpUQ/edit?pli=1
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!
I am trying to insert a variable number of rows into several tables, based on a dynamic value.
Here is a link to the file I am working with:
https://docs.google.com/file/d/0B-Bd0UJtnQ0RSVp3TjNZUjlRQkdWcjRhd2xJU3VpUQ/edit?pli=1
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!