Code - if particular value in cell/sheet name

Barefoot

Board Regular
Joined
Sep 20, 2005
Messages
87
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 :biggrin:
Haveagreatweekend!!!!
:cool: 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
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
This is the basic principle :-
Code:
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

Board Regular
Joined
Sep 20, 2005
Messages
87
Code

Hi Brian

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

Cheers
Julian :unsure:

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

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
In future please let us know what error message you get.
I think it is because you have left off the End With :-
Code:
With ActiveSheet.PageSetup 
    .PrintTitleRows = "$1:$7" 
    .PrintTitleColumns = "" 
End With
 

Barefoot

Board Regular
Joined
Sep 20, 2005
Messages
87

ADVERTISEMENT

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

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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

Board Regular
Joined
Sep 20, 2005
Messages
87
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
:oops:
 

Barefoot

Board Regular
Joined
Sep 20, 2005
Messages
87
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,206
Messages
5,600,302
Members
414,375
Latest member
Onmyown

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top