Code - if particular value in cell/sheet name

[Barefoot]

Hi All

Looking for some assistance please in a macro that runs on all sheets with a particular value/name.

My code below sets the printing settings for all sheets in a workbook.
What I am having difficulty in working out, and seek your assistance, is how to only format sheets that have a particular value in a cell (say “@” in A1). Also is it possible to do the same but on a worksheets name or value within the name?

Thank you
Julian
Haveagreatweekend!!!!
I Wish!!!

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Rows("1:1").Select
Selection.Insert Shift:=x1Down
Selection.Insert Shift:=x1Down
Selection.Insert Shift:=x1Down
Rows("2:3").Select
Selection.RowHeight = 4.5
Range("b4").Select

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$7"
.PrintTitleColumns = ""

PLUS CODE THAT SETS MARGINS AND HEADERS ETC.

End With
Next ws
End Sub

 

[BrianB]

This is the basic principle :-

Sub test()
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name = "Sheet1" _
            Or ws.Range("A1").Value = "@" _
            Or InStr(1, ws.Name, "@", vbTextCompare) > 0 Then
            '- code to do something
        Else
            '- code to do something else
        End If
    Next
End Sub

 

[Barefoot]

Code

Hi Brian

Thanks for that.
Not sure if I am doing something wrong but it get hung up of Else

Cheers
Julian

For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "Sheet1" _
Or ws.Range("A1").Value = "@" _
Or InStr(1, ws.Name, "@", vbTextCompare) > 0 Then
Rows("1:1").Select
Selection.Insert Shift:=x1Down
Selection.Insert Shift:=x1Down
Selection.Insert Shift:=x1Down
Rows("2:3").Select
Selection.RowHeight = 4.5
Range("b4").Select

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$7"
.PrintTitleColumns = ""
Else
Range("a1").Select

End If
Next
End Sub

 

[BrianB]

In future please let us know what error message you get.
I think it is because you have left off the End With :-

With ActiveSheet.PageSetup 
    .PrintTitleRows = "$1:$7" 
    .PrintTitleColumns = "" 
End With

 

[Barefoot]

Hi Brian

Thanks, have changed that and played around some more.
I still cannot get it to work.

In my 'test' file I have 2 sheets with an email address in a1 followed by 5 sheets without.

What is happening is that the macro works on the first sheet (twice for some reason - it is inserting double the amount of rows) and does not roll over to the following worksheet(s).

I am lost in what is going on and how to make it move to the next and following worksheet(s).

Cheers
Julian

Sub NewMacro()
'
' NewMacro Macro
' Macro recorded 02/12/2005 by Wiltshire County Council User
'

For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "Sheet1" _
Or ws.Range("A1").Value = "@" _
Or InStr(1, ws.Name, "@", vbTextCompare) > 0 Then
Rows("1:1").Select
Selection.Insert Shift:=x1Down
Range("b4").Select

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$7"
.PrintTitleColumns = ""
End With
Else
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$4"
.PrintTitleColumns = "EXT RPT"

End If
Next
End Sub

 

[BrianB]

I have to say that I was in two minds whether to bother answering this or not. You have obviously not read my earlier reply - especially regarding the necessity to let us know what error message you get and on which line.

The code you have posted will not run. See my previous reply.

Nor have you bothered to comply with the request in my signature to select the code in the message and click the Code button. If your code was formatted correctly you would probably have seen your main error.

Finally, as a hint to further error .. it is not possible to select a cell in a worksheet unless the sheet has been activated.

 

[Barefoot]

Dear Brian

Thanks for posting back and your honesty.

Sorry for ignoring you/your instructions. I will re-look at everything, more closely, when I am back in the office towards the end of the week.

I understand that it is frustrating and time wasting when people do not read/listen properly and I do apologise. I am not myself at the moment and am trying all sorts of things to keep my mind occupied. I am not making excuses but your last post WAS the kick up the bum I needed.

Lesson learnt.

Thanks for all your help

Cheers
Julian

 

[Barefoot]

Code now working

Hi Brian (and all)

Have been a bit tied up lately so only managed to work on this over the weekend.

Just to let you know that I have the code working now and as you say you need to activate the ws.

Thanks for all your help with this.

Regards
Julian