Excel Readjusting Code

[luckyajr]

Hi All,

I found this code, and was hoping to be able to tweek it a bit. The code dictates an input box where the user then has to express how many rows they want to be added. I would like the code to be set up so that a cell value will tell it how many rows to insert. Let's say that the referenced cell would be C1.
I appreciate your input!

Sub InsertRowsAndFillFormulas_caller()
  '-- this macro shows on Tools, Macro..., Macros ([URL="http://dmcritchie.mvps.org/excel/buildtoc.htm#AltF8"]Alt+F8[/URL]) dialog 
  Call InsertRowsAndFillFormulas
End Sub

Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0)
' Documented:  [url]http://www.mvps.org/dmcritchie/excel/insrtrow.htm[/url]
' Re: Insert Rows --   [URL="http://groups.google.com/groups?selm=34292567.90497936%40charm.net.noSpam"]1997/09/24[/URL] Mark Hill <markhill@charm.net.noSpam>
   ' row selection based on active cell -- rev. 2000-09-02 David McRitchie
   Dim x as long 
   ActiveCell.EntireRow.Select  'So you do not have to preselect entire row
   If vRows = 0 Then
    vRows = Application.InputBox(prompt:= _
      "How many rows do you want to add?", Title:="Add Rows", _
      Default:=1, Type:=1) 'Default for 1 row, type 1 is number
    If vRows = False Then Exit Sub
   End If

   'if you just want to add cells and not entire rows
   'then delete ".EntireRow" in the following line

   'rev. [URL="http://groups.google.com/groups?threadm=u2L4MXMgAHA.948%40tkmsftngp03"]2001-01-17[/URL] Gary L. Brown, programming, Grouped sheets
   Dim sht As Worksheet, shts() As String, i As Long
   ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
       Windows(1).SelectedSheets.Count)
   i = 0
   For Each sht In _
       Application.ActiveWorkbook.Windows(1).SelectedSheets
    Sheets(sht.Name).Select
    i = i + 1
    shts(i) = sht.Name

    x = Sheets(sht.name).UsedRange.Rows.Count '[URL="http://dmcritchie.mvps.org/excel/lastcell.htm#resetall"]lastcell fixup[/URL]

    Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
     Resize(rowsize:=vRows).Insert Shift:=xlDown

    Selection.AutoFill Selection.Resize( _
     rowsize:=vRows + 1), xlFillDefault

    On Error Resume Next    'to handle no constants in range -- John McKee 2000/02/01
    ' to remove the non-formulas -- [URL="http://groups.google.com/groups?selm=35071d9e.118609251%40msnews.microsoft.com"]1998/03/11[/URL] Bill Manville
    Selection.Offset(1).Resize(vRows).EntireRow. _
     SpecialCells(xlConstants).ClearContents
   Next sht
   Worksheets(shts).Select
End Sub

 

[Norie]

What is it exactly you want to do?

Do you just want to insert a set no of blank rows between values?

 

[luckyajr]

When the code runs I do not want the input box to be used. Rather, I would like the code to reference the cell value of C1 to use as the number of rows. The cell value of C1 will be based upon a formula and thus will change with different inputs (but will always be an integer). The idea is that once the code runs, I do not want to have the user then have to dictate the number of rows, rather I would like the code to automatically tell itself the number of rows to insert, thus the cell reference.
Hope this clarifies. Thanks!!