Loop through spreadsheets and run individual code

[lyonder2000]

Hello All,

I have a workbook that will have some raw data on about 40 worksheets. I want to clean the data up and make it look presentable. Sheet1 is unique, Sheet2 is unique, Sheet3-40 are all the same (format-wise). I've been trying to create a loop that uses a Select Case statement to determine if the Sheet is Sheet 1, 2, or other and format it accordingly.

For Each Sh In Worksheets
Select Case Sh.Name
Case Is = "QRY_OPEN_WRK_ORDRS"
Sheets("QRY_OPEN_WRK_ORDRS").Activate
Columns("A:I").Select
Selection.ColumnWidth = 19.29
Range("A1:I1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
Range("A1").Select
End With
Case Is ="QRY_TL_OPEN_WOs_by_VNDR"
Sheets("QRY_TL_OPEN_WOs_by_VNDR").Activate
Columns("B:B").EntireColumn.AutoFit
Range("A1:B1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
Range("D1").Select
Case "ACCT_274201"
Columns("A:F").Select
Selection.ColumnWidth = 10.57
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("H1").Select
End Select
Next Sh


However, the first case (Case Is = "QRY_OPEN_WRK_ORDRS") formatting info is being used to format ALL the worksheets. I cannot figure out what I'm doing wrong or if I'm doing this the best way.

 

[Andrew Poulsom]

Firstly, where have you put that code - in a General module or in a Worksheet module. If the latter you will need to qualify any property you use with the relevant worksheet (otherwise the worksheet containing the code will be used). Secondly when posting code please use code tags:

BB Code List - MrExcel Message Board

 

[lyonder2000]

I have this saved in my personal workbook. I've created other macros and saved them similarly in my personal workbook and they work without fail.

(sorry about the missing code tags)

 

[Norie]

You aren't referencing the worksheets in the loop.

    For Each sh In Worksheets
        Select Case sh.Name
            Case Is = "QRY_OPEN_WRK_ORDRS"
                sh .Columns("A:I").ColumnWidth = 19.29
                With sh.Range("A1:I1").Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorDark1
                    .TintAndShade = -0.149998474074526
                    .PatternTintAndShade = 0
                End With
                Application.Goto sh.Range("a1")
            Case Is = "QRY_TL_OPEN_WOs_by_VNDR"
                sh.Columns("B:B").EntireColumn.AutoFit
                With sh.Range("A1:B1").Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorDark1
                    .TintAndShade = -0.149998474074526
                    .PatternTintAndShade = 0
                End With
                Application.Goto sh.Range("D1")
            Case "ACCT_274201"
                With sh.Columns("A:F")
                    .ColumnWidth = 10.57
                    .HorizontalAlignment = xlCenter
                    .VerticalAlignment = xlBottom
                    .WrapText = False
                    .Orientation = 0
                    .AddIndent = False
                    .IndentLevel = 0
                    .ShrinkToFit = False
                    .ReadingOrder = xlContext
                    .MergeCells = False
                End With
                Application.Goto sh.Range("H1")
        End Select
    Next sh

 

[lyonder2000]

How should I go about referencing the sheet? I tried a sheetname.activate but that didn't seem to work.

 

[Norie]

In the code I posted I've added sheet references where they are needed.

For example here I've addded the sheet reference sh.

sh.Columns("A:I").ColumnWidth = 19.29