• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Create macro that inserts variable number of rows into tables

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!
 
Hi, mantooh29!


This code will insert a number of rows (specified in cell E2 of sheet Parameters) after a given row (in F2 of same sheet) in all worksheets.

Adjust it to fit you case.


-----

[pre]
Code:
Sub InsertRows()
' constants
Const ksParameters = "Parameters"
' declarations
Dim iRowsAdded As Integer, iRowAfter As Integer, sh As Worksheet
' start
iRowsAdded = Worksheets(ksParameters).Range("E2").Value
iRowAfter = Worksheets(ksParameters).Range("F2").Value
' process
For Each sh In ActiveWorkbook.Worksheets
sh.Range(sh.Rows(iRowAfter), sh.Rows(iRowAfter + iRowsAdded - 1)).Insert
Next sh
' end
Worksheets(ksParameters).Activate
End Sub
[/pre]
-----


Regards!
 
Thank you for the reply!


I can definitely see where this code is going, however there is one hang up for me.


The sh variable is declared as worksheet, but my code needs to look like this...


(
Code:
)For Each sh In ActiveSheet(
)


This is because my E2 of parameters will change every time I go to a new sheet and re-run the macro.


However, the code above produces an error : Object doesn't support this property or method...

Which makes sense, because sh is declared as worksheet.


Any suggestions on changing that declaration in a way that the code will still run?
 
Hi, mantooth29!


In my example sh variable needs to be Worksheet because it's used to navigate thru all worksheets.

If you're going use this macro once for each worksheet and manually (i.e. not once for all worksheets) then you can simply use the instruction within the FOR NEXT loop changing sh by ActiveSheet, and adjusting iRowsAdded and iRowAfter variable values.


Regards!
 
Actually, now that you mention it..


Ideally it would update each worksheet, excluding the first 3 or so (which contain data, a control tab, etc.)


I have a macro that will auto create these sheets, and if I can fit your code into it while excluding the first few sheets that would be PERFECT.


Here is the code


(`)Sub CreateSheets()

Dim MyCell As Range, MyRange As Range


Set MyRange = Sheets("Control").Range("I2")

Set MyRange = Range(MyRange, MyRange.End(xlDown))


For Each MyCell In MyRange

Sheets.Add after:=Sheets(Sheets.Count) 'creates a new worksheet

Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet

Worksheets("Test").Cells.Copy ActiveSheet.Range("A1")

With ActiveSheet

.Range("B4").Value = .Name

End With

Next MyCell


End Sub(`)
 
Actually, when using your line of almost exactly, but changing Worksheet(ksparameters) to Worksheet(Control)


I receive the following error.


"Application defined or object defined error."
 
Hi, mantooth29!


I don't understand why should you need to insert blank rows in recently created (so empty!) worksheets.

I suggest you to redefine exactly what you need, what you've done and start working from that clear and stable situation. You've changed twice the code and my previous suggestion doesn't enter in this scenario.

Consider uploading a sample file. Refer to the green sticky topics at this forums main page for guidelines.


Regards!
 
Yeah I knew I would make this confusing. Not sure how to upload, but here is a googledocs link to my file as it currently is.


https://docs.google.com/open?id=0B-Bd0UJtnQ0RS1N0Wm8wRUpTbDJBNHkwYmFoV1lHUQ


To clarify, each new sheet being created will have the format from a previous sheet copied into it. The command is in this part of the code


(
Code:
)Worksheets("Test").Cells.Copy ActiveSheet.Range("A1")(
)


You are correct, I have not attempted your last suggestion because I realized that doing the all worksheets method (excluding the first 3) will be much more efficient.
 
Hi, mantooth29!


Give a look at your uploaded file with some modifications. They're just secondary and don't solve the main issue, but are prerequisites for facing it.

See you on Monday night.


http://dl.dropbox.com/u/60558749/Create%20macro%20that%20inserts%20variable%20number%20of%20rows%20into%20tables%20-%20ChandooSample%20%28for%20mantooth29%20at%20chandoo.org%29.xlsm


Regards!
 
Back
Top