I've posted a thread
that is related to this question, but I want to bring it in a different direction and add a little more clarity behind what I'm trying to accomplish.
I’m looking for a code that will select a specific range of worksheets that includes: ‘Summary’, ‘Details’, and sheet names that start with ‘Zone’, and insert a specified number of rows immediately below the row with the active cell. The number of rows to insert should be referenced from a message input box that requires a user to type how many rows are required to be inserted.
Next it should copy the contents of the row containing the active cell and paste them into the rows that were just inserted. The reason for inserting prior to copying is to ensure the formulas that reference other worksheets refer to the correct cell row. If I were to copy and then insert, the formulas would not update correct.
If I were to manually execute this without a macro, the steps would go like this:
1. In the attached file, notice Summary!A8 has a formula of SUM('Zone XY:Zone 8B'!A8
2. Select the following sheets: ‘Summary’, ‘Details’, ‘Zone XY’, ‘Zone 12’, and ‘Zone 8B’
3. Select rows 5, 6, 7 to insert three rows below row 4
4. Copy row 4 and pasted into rows 5, 6, 7
5. Now Summary!A11 has a formula of SUM('Zone XY:Zone 8B'!A11
That’s the logic, but I just can't piece together the right code that will execute it properly. This is a tough one. Thanks in advance for your help.
P.S. A really nice macro to select the specified worksheets is listed below (compliments of MatthewsPatrick)
Dim MyList As String
Dim arr As Variant
Dim ws As Worksheet
Application.ScreenUpdating = False
MyList = "Summary,Details"
For Each ws In .Worksheets
If LCase(ws.Name) Like "zone*" Then
MyList = MyList & "," & ws.Name
arr = Split(MyList, ",")