MACRO PROBLEM: How to Insert 5 Rows Above Header Row on All Sheets, Except One

[cgeorge4]

Excel 2007 and 2010
Windows 7

This should be easy for me but it's not working.

1) I have a workbook with a Master tab named "pivot sequence".

2) The other sheets in this same workbook have different names - and their header titles are all on row 1.


I need to insert 5 blank rows above the header row on each sheet (except the "pivot sequence" sheet).
Essentially moving the range of data down 5 rows.

I recorded inserting 3 rows above the range of data on one sheet, then made changes so it would loop thru the other sheets.

My code below inserts 14 rows for some reason, and only on the sheet selected.
I would need this code to work on all sheets - no matter what sheet happens to be selected at the time.

I can't see where my error is. Any help would be appreciated.

Sub Insert5_Rows_Loop()

Dim wks  As Worksheet


For Each wks In Worksheets
    If LCase(wks.Name) <> "pivot sequence" Then
        Rows("1:1").Select
    Range("A1").Activate
    Selection.Insert Shift:=xlDown
    End If
Next wks


End Sub

Thank you in advance,
Juicy

 

[VoG]

Try

Sub Insert5_Rows_Loop()

Dim wks  As Worksheet


For Each wks In Worksheets
    If LCase(wks.Name) <> "pivot sequence" Then
        wks.Rows("1:5").Insert
    End If
Next wks


End Sub

 

[cgeorge4]

I was close but no cigar
Thank you VoG. Perfect

One last thing related to my post. Now that there are 5 blank rows on each sheet - I need to copy the following formula to the 3rd row of each of them (except for the master sheet called "pivot sequence")

Starting on A3 of each sheet.
Here is my formula that inserts the sheet name in the middle of a recurring sentence.


[TABLE="width: 235"]
<tbody>[TR]
[TD="class: xl64, width: 235"]="Orders have been reserved for"&" "&REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"")&" "&"as shown below."[/TD]
[/TR]
</tbody>[/TABLE]



Thanks so much darling.....Juicy

 

[VoG]

With some adjustments to your formula

Sub Insert5_Rows_Loop()

Dim wks  As Worksheet


For Each wks In Worksheets
    If LCase(wks.Name) <> "pivot sequence" Then
        wks.Rows("1:5").Insert
        wks.Range("A3").Formula = "=""Orders have been reserved for"" & "" "" & REPLACE(CELL(""filename"",A1),1,FIND(""]"",CELL(""filename"",a1)),"""")& "" "" & ""as shown below."""
    End If
Next wks


End Sub

 

[cgeorge4]

Vog, I'm so happy that you're still awake this late night. Unless your across the pond and it's early morning for you

Perfection again.

Thank you!

 

[cgeorge4]

Thank you VoG!
I had added "FormulaR3C1 =" instead of "wks.Range("A3")".

Thank you for looking at my formula as well. Im lunching in my car right now, but I'll run to my desk right now and try it.

Juicy,

 

[cgeorge4]

VoG, Works perfectly!.

I forgot to add that i need to make one of the words in the sentence a hyperlink to a specific webpage.
I should probably make that request on a new thread.

Let me know if I should post a new thread for help with this hyperlink.

FYI - This hyperlink is the last piece needed in order to complete my project.
Juicy,

 

[VoG]

Hi.

Please start a new thread - I'm about to disappear for the weekend. Good luck!

 

[cgeorge4]

Thank you and have a wonderful weekend!

Have fun - but make sure to come back.........We need you!

Last night I searched and read for hours with no success, sadly.

I'll start a new thread. Thank you so much.

Juicy