Below is a list of steps outlining what I’m trying to accomplish:
1. Select a range of specified worksheets using the following subroutine:
Public Sub SelectSheets()
Dim MyList As String
Dim arr As Variant
Dim ws As Worksheet
MyList = "Summary,Details"
For Each ws In .Worksheets
If LCase(ws.Name) Like "zone*" Then
MyList = MyList & "," & ws.Name
arr = Split(MyList, ",")
2. Copy the contents (formulas, values, formatting, and etc) in the row of the cell that I have selected (i.e. whether I have C4, A4, B4, or D4 selected; it would copy all the contents of row 4.
3. Trigger a dialog box that allows me to input the number of rows I’d like insert between the row that has been copied and the row above it. Below is a nice code that is very close to what I need, but may need some very small adjustments.
Public Sub InsertRows()
Dim Rng, n As Long, k As Long
Application.ScreenUpdating = False
Rng = InputBox("Enter number of rows required." & vbCrLf & vbCrLf & _
"All formulas and formatting in row above current position of cursor will be copied.")
If Rng = "" Then Exit Sub
Range(ActiveCell, ActiveCell.Offset(Val(Rng) - 1, 0)).EntireRow.Insert
'need To know how many formulas To copy down.
'Assumesfrom A over To last entry In row.
k = ActiveCell.Offset(-1, 0).Row
n = Cells(k, 256).End(xlToLeft).Column
Range(Cells(k, 1), Cells(k + Val(Rng), n)).FillDown
Application.ScreenUpdating = True
4. The insert should be applied on all selected worksheets
As always, I’m grateful for any proposed solutions. Thanks!