-1

The file we receive from contractors is always in the same format and needs to be reformatted. Files can vary in the number of tabs, but nomenclature is always the same. The macro built ran previously for the active sheet. I've added the looping part to do it for all tabs. Our goal is to run the macro on every tab provided in the excel file provided by our contractors. However, it does not format it on the current tab where the data is stored--just on the active sheet. I know I need to change something with Active Sheet, but I am lost on this one. Any help would be great. Thanks, Nick

Sub WorksheetLoop()

     Dim WS_Count As Integer
     Dim I As Integer

     ' Set WS_Count equal to the number of worksheets in the active
     ' workbook.
     WS_Count = ActiveWorkbook.Worksheets.Count

     ' Begin the loop.
     For I = 1 To WS_Count

Cells.Select
Selection.UnMerge
Rows("1:9").Select
Selection.Delete Shift:=xlUp
Range("A2").Select
ActiveCell.FormulaR1C1 = "=R[-1]C"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=R[-1]C"
Columns("F:F").Select
Selection.Delete Shift:=xlToLeft
Columns("I:I").Select
Selection.Delete Shift:=xlToLeft
Columns("L:L").Select
Selection.Delete Shift:=xlToLeft
Columns("O:O").Select
Selection.Delete Shift:=xlToLeft
Columns("R:R").Select
Selection.Delete Shift:=xlToLeft
Columns("U:U").Select
Selection.Delete Shift:=xlToLeft
Columns("X:X").Select
Selection.Delete Shift:=xlToLeft
Columns("AA:AA").Select
Selection.Delete Shift:=xlToLeft
Columns("AD:AD").Select
Selection.Delete Shift:=xlToLeft
Columns("AG:AG").Select
Selection.Delete Shift:=xlToLeft
Columns("AJ:AJ").Select
Selection.Delete Shift:=xlToLeft
Columns("AM:AM").Select
Selection.Delete Shift:=xlToLeft
Columns("AP:AP").Select
Selection.Delete Shift:=xlToLeft
Columns("AS:AS").Select
Selection.Delete Shift:=xlToLeft
Columns("AV:AV").Select
Selection.Delete Shift:=xlToLeft
Columns("AY:AY").Select
Selection.Delete Shift:=xlToLeft
Columns("BB:BB").Select
Selection.Delete Shift:=xlToLeft
Columns("BE:BE").Select
Selection.Delete Shift:=xlToLeft
Columns("BH:BH").Select
Selection.Delete Shift:=xlToLeft
Columns("BK:BK").Select
Selection.Delete Shift:=xlToLeft
Columns("BN:BN").Select
Selection.Delete Shift:=xlToLeft
Columns("BQ:BQ").Select
Selection.Delete Shift:=xlToLeft
Columns("BT:BT").Select
Selection.Delete Shift:=xlToLeft
Columns("BW:BW").Select
Selection.Delete Shift:=xlToLeft
Columns("BZ:BZ").Select
Selection.Delete Shift:=xlToLeft
Columns("CC:CC").Select
Selection.Delete Shift:=xlToLeft
Columns("CF:CF").Select
Selection.Delete Shift:=xlToLeft
Columns("CI:CI").Select
Selection.Delete Shift:=xlToLeft
Columns("CL:CL").Select
Selection.Delete Shift:=xlToLeft
Columns("CO:CO").Select
Selection.Delete Shift:=xlToLeft
Columns("CR:CR").Select
Selection.Delete Shift:=xlToLeft
Columns("CU:CU").Select
Selection.Delete Shift:=xlToLeft
Columns("CX:CX").Select
Selection.Delete Shift:=xlToLeft
Columns("DA:DA").Select
Selection.Delete Shift:=xlToLeft
Columns("DD:DD").Select
Selection.Delete Shift:=xlToLeft
Columns("DG:DG").Select
Selection.Delete Shift:=xlToLeft
Columns("DJ:DJ").Select
Selection.Delete Shift:=xlToLeft
Columns("DM:DM").Select
Selection.Delete Shift:=xlToLeft
Columns("DP:DP").Select
Selection.Delete Shift:=xlToLeft
Columns("DS:DS").Select
Selection.Delete Shift:=xlToLeft
Columns("DV:DV").Select
Selection.Delete Shift:=xlToLeft
Columns("DY:DY").Select
Selection.Delete Shift:=xlToLeft
Columns("EB:EB").Select
Selection.Delete Shift:=xlToLeft
Columns("EE:EE").Select
Selection.Delete Shift:=xlToLeft
Columns("EH:EH").Select
Selection.Delete Shift:=xlToLeft
Columns("EK:EK").Select
Selection.Delete Shift:=xlToLeft
Columns("EN:EN").Select
Selection.Delete Shift:=xlToLeft
Columns("EQ:EQ").Select
Selection.Delete Shift:=xlToLeft
Columns("ET:ET").Select
Selection.Delete Shift:=xlToLeft
Columns("EW:EW").Select
Selection.Delete Shift:=xlToLeft
Columns("EY:EY").Select
Selection.Delete Shift:=xlToLeft
Columns("FB:FB").Select
Selection.Delete Shift:=xlToLeft
Columns("FE:FE").Select
Selection.Delete Shift:=xlToLeft
Columns("FH:FH").Select
Selection.Delete Shift:=xlToLeft
Columns("FK:FK").Select
Selection.Delete Shift:=xlToLeft
Columns("FN:FN").Select
Selection.Delete Shift:=xlToLeft
Columns("FQ:FQ").Select
Selection.Delete Shift:=xlToLeft
Columns("FT:FT").Select
Selection.Delete Shift:=xlToLeft
Columns("FW:FW").Select
Selection.Delete Shift:=xlToLeft
Columns("FZ:FZ").Select
Selection.Delete Shift:=xlToLeft
Columns("GC:GC").Select
Selection.Delete Shift:=xlToLeft
Columns("GF:GF").Select
Selection.Delete Shift:=xlToLeft
Columns("GI:GI").Select
Selection.Delete Shift:=xlToLeft
Columns("GL:GL").Select
Selection.Delete Shift:=xlToLeft
Columns("GO:GO").Select
Selection.Delete Shift:=xlToLeft
Columns("GR:GR").Select
Selection.Delete Shift:=xlToLeft
Columns("GU:GU").Select
Selection.Delete Shift:=xlToLeft
Columns("GX:GX").Select
Selection.Delete Shift:=xlToLeft
Columns("HA:HA").Select
Selection.Delete Shift:=xlToLeft
Columns("HD:HD").Select
Selection.Delete Shift:=xlToLeft
Columns("HG:HG").Select
Selection.Delete Shift:=xlToLeft
Columns("HJ:HJ").Select
Selection.Delete Shift:=xlToLeft
Columns("HM:HM").Select
Selection.Delete Shift:=xlToLeft
Columns("HP:HP").Select
Selection.Delete Shift:=xlToLeft
Columns("HS:HS").Select
Selection.Delete Shift:=xlToLeft
Columns("HV:HV").Select
Selection.Delete Shift:=xlToLeft
Columns("HY:HY").Select
Selection.Delete Shift:=xlToLeft
Columns("IB:IB").Select
Selection.Delete Shift:=xlToLeft
Columns("IE:IE").Select
Selection.Delete Shift:=xlToLeft
Columns("IH:IH").Select
Selection.Delete Shift:=xlToLeft
Columns("IK:IK").Select
Selection.Delete Shift:=xlToLeft
Columns("IN:IN").Select
Selection.Delete Shift:=xlToLeft
Columns("IQ:IQ").Select
Selection.Delete Shift:=xlToLeft
Columns("IT:IT").Select
Selection.Delete Shift:=xlToLeft
Columns("IW:IW").Select
Selection.Delete Shift:=xlToLeft
Columns("IZ:IZ").Select
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 10.86
Columns("F:F").Select
Columns("F:F").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("I:I").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("L:L").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveWindow.SmallScroll ToRight:=7
Columns("O:O").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("R:R").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("U:U").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveWindow.SmallScroll ToRight:=12
Columns("X:X").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("AA:AA").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("AD:AD").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("AG:AG").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("AJ:AJ").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveWindow.SmallScroll ToRight:=10
Columns("AM:AM").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("AP:AP").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("AS:AS").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("AV:AV").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveWindow.SmallScroll ToRight:=11
Columns("AY:AY").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("BB:BB").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("BE:BE").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveWindow.ScrollColumn = 1
Range("C2").Select
ActiveCell.FormulaR1C1 = "T1"
Range("C2:E2").Select
Selection.AutoFill Destination:=Range("C2:BD2"), Type:=xlFillDefault
Range("C2:BD2").Select
Columns("BE:BE").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("BH:BH").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("BB2:BD2").Select
Selection.AutoFill Destination:=Range("BB2:BJ2"), Type:=xlFillDefault
Range("BB2:BJ2").Select
ActiveWindow.ScrollColumn = 1
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Range("C3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=D1"
Range("C3").Select
Selection.Copy
Range("D1").Select
Application.CutCopyMode = False
Selection.Copy
Range("C3").Select
ActiveSheet.Paste
Cells.Select
Selection.Font.Bold = False
With Selection
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
With Selection
    .HorizontalAlignment = xlGeneral
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
Range("G1").Select
Selection.Copy
Range("F3").Select
ActiveSheet.Paste
Range("J1").Select
Application.CutCopyMode = False
Selection.Copy
Range("I3").Select
ActiveSheet.Paste
Range("M1").Select
Application.CutCopyMode = False
Selection.Copy
Range("L3").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=8
Range("P1").Select
Application.CutCopyMode = False
Selection.Copy
Range("O3").Select
ActiveSheet.Paste
Range("S1").Select
Application.CutCopyMode = False
Selection.Copy
Range("R3").Select
ActiveSheet.Paste
Range("V1").Select
Application.CutCopyMode = False
Selection.Copy
Range("U3").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=9
Range("Y1").Select
Application.CutCopyMode = False
Selection.Copy
Range("X3").Select
ActiveSheet.Paste
Range("AB1").Select
Application.CutCopyMode = False
Selection.Copy
Range("AA3").Select
ActiveSheet.Paste
Range("AE1").Select
Application.CutCopyMode = False
Selection.Copy
Range("AD3").Select
ActiveSheet.Paste
Range("AH1").Select
Application.CutCopyMode = False
Selection.Copy
Range("AG3").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=8
Range("AK1").Select
Application.CutCopyMode = False
Selection.Copy
Range("AJ3").Select
ActiveSheet.Paste
Range("AN1").Select
Application.CutCopyMode = False
Selection.Copy
Range("AM3").Select
ActiveSheet.Paste
Range("AQ1").Select
Application.CutCopyMode = False
Selection.Copy
Range("AP3").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=12
Range("AT1").Select
Application.CutCopyMode = False
Selection.Copy
Range("AS3").Select
ActiveSheet.Paste
Range("AW1").Select
Application.CutCopyMode = False
Selection.Copy
Range("AV3").Select
ActiveSheet.Paste
Range("AZ1").Select
Application.CutCopyMode = False
Selection.Copy
Range("AY3").Select
ActiveSheet.Paste
Range("BC1").Select
Application.CutCopyMode = False
Selection.Copy
Range("BB3").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=7
Range("BF1").Select
Application.CutCopyMode = False
Selection.Copy
Range("BE3").Select
ActiveSheet.Paste
Range("BI1").Select
Application.CutCopyMode = False
Selection.Copy
Range("BH3").Select
ActiveSheet.Paste
ActiveWindow.ScrollColumn = 1
Range("C3").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("C3:C3499"), Type:=xlFillDefault
Range("C3:C3499").Select
ActiveWindow.ScrollRow = 1
Columns("D:E").Select
Selection.EntireColumn.Hidden = True
Range("F3").Select
Selection.AutoFill Destination:=Range("F3:F3499")
Range("F3:F3499").Select
Columns("G:H").Select
Selection.EntireColumn.Hidden = True
Range("I3").Select
Selection.AutoFill Destination:=Range("I3:I3499")
Range("I3:I3499").Select
Columns("J:K").Select
Selection.EntireColumn.Hidden = True
Range("L3").Select
Selection.AutoFill Destination:=Range("L3:L3499")
Range("L3:L3499").Select
Columns("M:N").Select
Selection.EntireColumn.Hidden = True
Range("O3").Select
Selection.AutoFill Destination:=Range("O3:O3499")
Range("O3:O3499").Select
Columns("P:Q").Select
Selection.EntireColumn.Hidden = True
Range("R3").Select
Selection.AutoFill Destination:=Range("R3:R3499")
Range("R3:R3499").Select
Columns("S:T").Select
Selection.EntireColumn.Hidden = True
Range("U3").Select
Selection.AutoFill Destination:=Range("U3:U3499")
Range("U3:U3499").Select
Columns("V:W").Select
Selection.EntireColumn.Hidden = True
Range("X3").Select
Selection.AutoFill Destination:=Range("X3:X3499")
Range("X3:X3499").Select
Columns("Y:Z").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.SmallScroll ToRight:=4
Range("AA3").Select
Selection.AutoFill Destination:=Range("AA3:AA3499")
Range("AA3:AA3499").Select
Columns("AB:AC").Select
Selection.EntireColumn.Hidden = True
Range("AD3").Select
Selection.AutoFill Destination:=Range("AD3:AD3499")
Range("AD3:AD3499").Select
Columns("AE:AF").Select
Selection.EntireColumn.Hidden = True
Range("AG3").Select
Selection.AutoFill Destination:=Range("AG3:AG3499")
Range("AG3:AG3499").Select
Columns("AH:AI").Select
Selection.EntireColumn.Hidden = True
Range("AJ3").Select
Selection.AutoFill Destination:=Range("AJ3:AJ3499"), Type:=xlFillDefault
Range("AJ3:AJ3499").Select
ActiveWindow.ScrollRow = 1
Columns("AK:AL").Select
Selection.EntireColumn.Hidden = True
Range("AM3").Select
Selection.AutoFill Destination:=Range("AM3:AM3499"), Type:=xlFillDefault
Range("AM3:AM3499").Select
ActiveWindow.ScrollRow = 1
Columns("AN:AO").Select
Selection.EntireColumn.Hidden = True
Range("AP3").Select
Selection.AutoFill Destination:=Range("AP3:AP3499"), Type:=xlFillDefault
Range("AP3:AP3499").Select
ActiveWindow.ScrollRow = 1
Columns("AQ:AR").Select
Selection.EntireColumn.Hidden = True
Range("AS3").Select
Selection.AutoFill Destination:=Range("AS3:AS3499"), Type:=xlFillDefault
Range("AS3:AS3499").Select
ActiveWindow.ScrollRow = 1
Columns("AT:AU").Select
Selection.EntireColumn.Hidden = True
Range("AV3").Select
Selection.AutoFill Destination:=Range("AV3:AV3499"), Type:=xlFillDefault
Range("AV3:AV3499").Select
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollRow = 1
Columns("AW:AX").Select
Selection.EntireColumn.Hidden = True
Range("AY3").Select
Selection.AutoFill Destination:=Range("AY3:AY3499"), Type:=xlFillDefault
Range("AY3:AY3499").Select
ActiveWindow.ScrollRow = 1
Columns("AZ:BA").Select
Selection.EntireColumn.Hidden = True
Range("BB3").Select
Selection.AutoFill Destination:=Range("BB3:BB3499"), Type:=xlFillDefault
Range("BB3:BB3499").Select
ActiveWindow.ScrollRow = 1
Columns("BC:BD").Select
Selection.EntireColumn.Hidden = True
Range("BE3").Select
Selection.AutoFill Destination:=Range("BE3:BE3499"), Type:=xlFillDefault
Range("BE3:BE3499").Select
ActiveWindow.ScrollRow = 1
Columns("BF:BG").Select
Selection.EntireColumn.Hidden = True
Range("BH3").Select
Selection.AutoFill Destination:=Range("BH3:BH3499"), Type:=xlFillDefault
Range("BH3:BH3499").Select
ActiveWindow.ScrollRow = 1

MsgBox ActiveWorkbook.Worksheets(I).Name

     Next I

  End Sub
Community
  • 1
  • 1
user3059073
  • 21
  • 3

1 Answers1

0

Modify thusly:

 For I = 1 To WS_Count
    ActiveWorkbook.Worksheets(I).Activate

This is because unqaulified range objects (like Cells and Range) always implicitly belong to the active sheet, so in order to make this code run on all sheets, you will simply activate each sheet in sequence. (This is not technically true, but it is the easiest solution to your immediate problem).

The rest of your code needs no immediate changes, but you could benefit greatly from this:

How to avoid using Select in Excel VBA macros

Avoid relying on Select and Activate methods. These merely let you simulate keystrokes/mouseclicks, which is OK for learning and recording macros, but the point of object-oriented programming is to work directly with the object model. Cheers.

Community
  • 1
  • 1
David Zemens
  • 53,033
  • 11
  • 81
  • 130