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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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:
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,214,914
Messages
6,122,211
Members
449,074
Latest member
cancansova

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
Back
Top