Hi All,
Thank you in advance for taking your time to review my post.
I have the following code which someone helped me out with. However, with this macro I am wanting to remove the Input Box that pops up and requires the user to hit 'enter' everytime the macro runs. Instead, I would like the macro to automatically insert the rows based upon the value of cell A1 without having to confirm with the Input Box.
Because I am such a novice with coding, if the response could be in a form where the entire macro code is spelled out instead of a suggestion on what needs to be changed, it would be greatly appreciated. That way I can just copy and paste the macro response instead of having to try and figure out how to apply your suggestions to my existing code. Thanks!!
Thank you in advance for taking your time to review my post.
I have the following code which someone helped me out with. However, with this macro I am wanting to remove the Input Box that pops up and requires the user to hit 'enter' everytime the macro runs. Instead, I would like the macro to automatically insert the rows based upon the value of cell A1 without having to confirm with the Input Box.
Code:
Sub InsertRowsAndFillFormulas_caller()
Call InsertRowsAndFillFormulas
End Sub
Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0)
Dim x As Long
Sheets("SAVINGS").Select
Range("A7:J80").Select
Selection.ClearContents
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("A6:J6").Select
ActiveCell.Copy
If vRows = 0 Then
vRows = Application.InputBox(prompt:= _
"Click Enter to Continue", 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
Range( _
"D27,B7:J7,B9:J9,B11:J11,B13:J13,B15:J15,B17:J17,B19:J19,B21:J21,B23:J23,B25:J25,B27:J27,B29:J29,B31:J31,B33:J33,B35:J35,B37:J37,B39:J39,B41:J41" _
).Select
Range("B41").Activate
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(B41))>0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End SubBecause I am such a novice with coding, if the response could be in a form where the entire macro code is spelled out instead of a suggestion on what needs to be changed, it would be greatly appreciated. That way I can just copy and paste the macro response instead of having to try and figure out how to apply your suggestions to my existing code. Thanks!!
