Apply code to all worksheets in workbook

[AndyEd]

I have some code that formats a range of cells in a particular worksheet in a named workbook.

How could I apply the same code to ALL worksheets in the workbook. The worksheet names are changeable to I won't be able to reference specific worksheet names.

Is that doable?

[maniacb]

If the code uses “activesheeT” instead of sheet name, it will work on any sheet

[AndyEd]

Yes, but I'd like the code to run on ALL the worksheets, not just one.

[maniacb]

You can write the code to work on all worksheets in the workbook, something like this

Please Login or Register  to view this content.

[AndyEd]

It's still only applying to the activesheet???

[maniacb]

Any chance you can share your code, I might be able to provide you a better insight

[AndyE]

Sub Macro1()

Windows("Before.xlsx").Activate

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

Selection.Replace What:="XX", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

' Autofit relevant columns
Columns.EntireColumn.AutoFit

end sub

[Fluff13]

How about

Please Login or Register  to view this content.

[AndyEd]

Hi Fluff

That's great. Works a treat.

I've added a few other elements which work fine however I'm struggling to adapt the code to make the following changes,

Change the worksheet cell colour to white,
Add a new row to the top,
Merge cells ("C1") to the last populated row beneath,
Populated the merged 'cell' with the relevant worksheet name.

I've copied the code below, as compiled from recording macros.

Can you please help...

Please Login or Register  to view this content.

[Fluff13]

Sorry but I will have nothing to do with merged cells, they are an abomination & should be avoided like the plague.

[AndyEd]

I understand that. It is purely to create a centralised heading.

[Norie]

I've got to agree about merged cells so I've used center across selection instead, see if this does what you want.

Please Login or Register  to view this content.

P.S. Why are you copying from row 2 to row 1 and then clearing row 1?

[AndyEd]

I was copying row 2 to 1 and then clearing row 1's contents to merge the cells before placing the tab name into the merged cell.

I didn't even consider centering it across the selection. Sounds like that would work. When I ran the above code though it placed the tab name into each cell in row 1.

I've attached a version of the workbook indicating what I'm trying to achieve on the Apr 2021 (formatted) sheet.

The code I have so far is shown below,

Please Login or Register  to view this content.

[AndyEd]

Oh, and to color the worksheet white, prior to applying the header color.

[Norie]

...it placed the tab name into each cell in row 1...

Oops.
I know why that happened, see if this fixes that.

I'll have a look at the other stuff later and post back.

Please Login or Register  to view this content.

[AndyEd]

I've figured out of it now, albeit the code likely isn't pretty.

There's only one element that's still causing me problems,

I can't seem to select columns(3) to the end of the used range, to then change the columnwidth to 4.33 and centre the contents.

I've been playing around with

Please Login or Register  to view this content.

and variations of but keep getting errors.

Can anyone push me in the right direction...

[AndyEd]

I've tried numerous ways but can't get the following to work,

Columns "C" to the end of the used range, Horizontal alignment = centre & column widths = 4.3.

The best I can do is get the first sheet to work but t doesn't copy through the remaining sheets.

???