Removing Input Box from Macro

[luckyajr]

Hi All,

I found a very helpful code that I have used in one of my spreadsheets. I am hoping to have it amended to where the input box is removed and instead it automatically inserts the rows based upon the cell value of "A1". As it is currently written, the input box pops up and the user then has to hit enter, I don't want to have the user hit enter after the macro is run. Your thoughts are appreciated. THANKS!

ActiveCell.Copy  '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:=Range("A1"), Type:=1) 'Default for 1 row, type 1 is number
    If vRows = False Then Exit Sub
    
   End If

 

[JackDanIce]

Try:

ActiveCell.Copy  'So you do not have to preselect entire row
vRows = Range("A1")

 

[p45cal]

replace:

If vRows = 0 Then
    vRows = Application.InputBox(prompt:= _
                                 "How many rows do you want to add?", Title:="Add Rows", _
                                 Default:=Range("A1"), Type:=1)    'Default for 1 row, type 1 is number
    If vRows = False Then Exit Sub

with :

vrows=Range("A1").value

or:

If IsNumeric(Range("A1").Value) And Range("A1").Value > 0 Then
    vrows = Range("A1").Value
Else
    Exit Sub
End If

 

[luckyajr]

Thank you for the quick responses!
It now inserts the correct rows without the input box, however instead of inserting and pasting the formulas in just the selected cells (A6:F6) it now blankets the formulas across every possible cell in the row. Here is the full code. Your thoughts are appreciated!

Sub InsertRowsAndFillFormulas_caller()
  Call InsertRowsAndFillFormulas
End Sub
 
Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0)
   Dim x As Long
   Sheets("scenarios").Select
    Range("A7:F150").Select
    Selection.ClearContents
    Range("A7").Select
    Sheets("POSSIBILITIES").Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SetSourceData Source:=Sheets("scenarios").Range("B3:F7")
    ActiveChart.SeriesCollection(1).XValues = "=scenarios!$A$4:$A$7"
   Sheets("scenarios").Select
    Range("A6:F6").Select
   ActiveCell.Copy
   If vRows = 0 Then
    vRows = Application.InputBox(prompt:= _
      "How many rows do you want to add?", Title:="Add Rows", _
      Default:=Range("A1"), Type:=1)
    If vRows = False Then Exit Sub
    
   End If
   
   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 'lastcell fixup
    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 -- 1998/03/11 Bill Manville
    Selection.Offset(1).Resize(vRows).EntireRow. _
     SpecialCells(xlConstants).ClearContents
   Next sht
   Worksheets(shts).Select
End Sub

 

[p45cal]

this is because in the line
Selection.Resize(rowsize:=2).Rows(2).EntireRow.Resize(rowsize:=vRows).Insert Shift:=xlDown
you have Entirerow
This line is inserting rows and copying the formula/value from the Scenarios sheet, cell A6 (yes, a single cell)
this is not a result of changing how vrows is set.
In the next line you autofill down into the newly inserted rows whatever columns were selected on that sheet before the macro was run, overwriting what had just been copied, leaving the other columns untouched, still containing whatever formula/data was in sheet scenarios cell A6.

What do you want to happen?

 

[luckyajr]

Thanks again for responding. Apologies for my ignorance as my coding knowledge is novice at best.
What I would like to happen is to only have cells A6:F6 copied and then insert those formulas and cells the number of times as dictated in A1, as opposed to the entire row.
Basically, I want everything to run as it does in the original macro but without the input box, I don't want the user to have to hit enter to confirm the number of rows, but rather it just automatically insert based upon value in A1. Hope this clarifies. Thank you!

 

[p45cal]

try (untested):

Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0)
Dim x As Long
Sheets("scenarios").Select
Range("A7:F150").Select
Selection.ClearContents
Range("A7").Select
Sheets("POSSIBILITIES").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SetSourceData Source:=Sheets("scenarios").Range("B3:F7")
ActiveChart.SeriesCollection(1).XValues = "=scenarios!$A$4:$A$7"
If vRows = 0 Then
    vRows = Application.InputBox(prompt:="How many rows do you want to add?", Title:="Add Rows", Default:=Range("A1"), Type:=1)
    If vRows = False Then Exit Sub
End If

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    'lastcell fixup
    Selection.Resize(rowsize:=2).Rows(2).EntireRow.Resize(vRows).Insert Shift:=xlDown
    Sheets("Scenarios").Range("A6:F6").Copy Selection.Resize(rowsize:=2).Rows(2).Resize(vRows, 6)
    ' 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 -- 1998/03/11 Bill Manville
    Selection.Offset(1).Resize(vRows).EntireRow.SpecialCells(xlConstants).ClearContents
Next sht
Worksheets(shts).Select
End Sub

 

[luckyajr]

Trialed it and the inputbox still came up, and then an error came up which stopped the macro from completing.

 

[luckyajr]

Any other thoughts? Thanks as always!

 

[p45cal]

Trialed it and the inputbox still came up, and then an error came up which stopped the macro from completing.

This will happen if you call the macro with the likes of
Call InsertRowsAndFillFormulas
or
Call InsertRowsAndFillFormulas(0)
but if you use
Call InsertRowsAndFillFormulas(5)
or
Call InsertRowsAndFillFormulas(range("A1"))
and A1 has a number in it, the input box shouldn't show.

Re error coming up: I can't see your screen, I don't know what's on the sheets, I don't know which line it stopped on, I don't know what the error said etc. etc.