*** How do you have a macro automatically repeat on all worksheets in a workbook and stop after the last one, then return to the first sheet? Thank yo

B

brad7989

New Member
Joined
Aug 1, 2011
Messages
45
So I have a macro that formats a bunch of data. Currently I run it, then press CTRL + Page Down to shift sheets, then run it again, then shift sheets, run it, etc.

I want to fix it to make it automatically run through all sheets, then return to the first sheet afterwards.

Thank you so much for your help!
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
Code: 
Sub test()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
'your code here
Next ws

End Sub
 
Upvote 0
Here it is. So the FOR and NEXT are within notes that say " ' REPEATER" and " ' END"


Thanks!









Sub UnitMixFormatting()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Dim wsName As String
' REPEATER






Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets








' END


' Start CODE
Range("A1").Select
Cells.Find(What:="SECTION II", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Rows("1:783").EntireRow.Select
Selection.ClearContents
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.End(xlUp).Select
ActiveWindow.SmallScroll Down:=-6
Selection.ClearContents

Application.Goto Reference:="R1C1"
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(4, 0).Range("A1:N1").Select
Selection.ClearContents
ActiveCell.Offset(0, 11).Columns("A:A").EntireColumn.Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Application.Goto Reference:="R1C1"
' 3
' Delete top row????
ActiveCell.Offset(0, 1).Range("A1").Select
Application.Goto Reference:="R1C1"
ActiveCell.Range("A1:O1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Application.Goto Reference:="R5C1"
ActiveCell.Offset(-2, 0).Range("A1:A3").Select
ActiveCell.Activate
Selection.Merge
Application.Goto Reference:="R5C2"
ActiveCell.FormulaR1C1 = "xxx"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R3C3"
ActiveCell.FormulaR1C1 = "x"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R4C3"
ActiveCell.FormulaR1C1 = "x"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R3C4"
ActiveCell.FormulaR1C1 = "xxx"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R4C4"
ActiveCell.FormulaR1C1 = "xxxx"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R3C5"
ActiveCell.FormulaR1C1 = "xxx"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R4C5"
ActiveCell.FormulaR1C1 = "xxxr"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R5C5"
ActiveCell.FormulaR1C1 = "xxx"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R3C6"
ActiveCell.FormulaR1C1 = "#"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R4C6"
ActiveCell.FormulaR1C1 = "xxx"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R3C7"
ActiveCell.FormulaR1C1 = "xxxx"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R4C7"
ActiveCell.FormulaR1C1 = "xxx"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R3C8"
ActiveCell.FormulaR1C1 = "xxxx"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R4C8"
ActiveCell.FormulaR1C1 = "xxxx"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R3C9"
ActiveCell.FormulaR1C1 = "xxxx"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R4C9"
ActiveCell.FormulaR1C1 = "xxxxx"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R3C10"
ActiveCell.FormulaR1C1 = "xxxxx"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R4C10"
ActiveCell.FormulaR1C1 = "xxxxx"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R3C11"
ActiveCell.FormulaR1C1 = "xxxxx"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R4C11"
ActiveCell.FormulaR1C1 = "xxxxx"

' Hey


Application.Goto Reference:="R5C1"
ActiveCell.FormulaR1C1 = "Size"
' Hey



ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R3C12"
ActiveCell.FormulaR1C1 = "xxxxxxx"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R4C12"
ActiveCell.FormulaR1C1 = "xxxxxxxx"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R5C12"
ActiveCell.FormulaR1C1 = "xxxxxx"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R3C13"
ActiveCell.FormulaR1C1 = "%"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R4C13"
ActiveCell.FormulaR1C1 = "xxxxxxxx"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R5C13"
ActiveCell.FormulaR1C1 = "xxxxxxxx"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R3C14"
ActiveCell.FormulaR1C1 = "xxxxxx"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R4C14"
ActiveCell.FormulaR1C1 = "xxxxxxxx"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R3C15"
ActiveCell.FormulaR1C1 = "x"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R4C15"
ActiveCell.FormulaR1C1 = "xxx"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R5C15"
ActiveCell.FormulaR1C1 = "xxxxxx"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R1C1"
ActiveCell.Range("A1:O5").Select
Selection.Font.Bold = True
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R1C1"

' 4

Application.Goto Reference:="R6C12"
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-5]"
ActiveCell.Offset(1, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "x"
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveCell.FormulaR1C1 = "x"
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlUp).Select
Application.Goto Reference:="R6C16"
ActiveCell.FormulaR1C1 = "=RC[-3]/100"
ActiveCell.Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(0, -3).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.Goto Reference:="R6C12"
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.Goto Reference:="R6C16"
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
Application.Goto Reference:="R1C1"

Application.Goto Reference:="R6C12"
ActiveCell.Range("A1:B1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Style = "Percent"
Selection.NumberFormat = "0.0%"
Application.Goto Reference:="R1C1"




'
Range("F" & Rows.Count).End(xlUp).Offset(2).Formula = "=SUBTOTAL(109,R6C:R[-2]C)"
Range("G" & Rows.Count).End(xlUp).Offset(2).Formula = "=SUBTOTAL(109,R6C:R[-2]C)"
Range("H" & Rows.Count).End(xlUp).Offset(2).Formula = "=SUBTOTAL(109,R6C:R[-2]C)"
Range("I" & Rows.Count).End(xlUp).Offset(2).Formula = "=SUBTOTAL(109,R6C:R[-2]C)"
Range("J" & Rows.Count).End(xlUp).Offset(2).Formula = "=SUBTOTAL(109,R6C:R[-2]C)"
Range("K" & Rows.Count).End(xlUp).Offset(2).Formula = "=SUBTOTAL(109,R6C:R[-2]C)"
Range("N" & Rows.Count).End(xlUp).Offset(2).Formula = "=SUBTOTAL(109,R6C:R[-2]C)"
Range("O" & Rows.Count).End(xlUp).Offset(2).Formula = "=SUBTOTAL(109,R6C:R[-2]C)"

' END SUBTOTALS




Application.Goto Reference:="R6C1"
Selection.End(xlDown).Select
ActiveCell.Offset(2, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Totals"
ActiveCell.Offset(0, 5).Range("A1").Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.Style = "Percent"
With Selection.Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Selection.NumberFormat = "0.0%"
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-5]"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RC[-5]/RC[-7]"
ActiveCell.Offset(0, 1).Range("A1").Select

ActiveCell.Offset(0, 1).Range("A1").Select

ActiveCell.Offset(0, 1).Range("A1").Select
Application.Goto Reference:="R6C1"
Selection.End(xlDown).Select
Selection.End(xlDown).Select
ActiveCell.Range("A1:O1").Select
Selection.Font.Bold = True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Application.Goto Reference:="R6C1"
Selection.End(xlDown).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 11).Range("A1:B1").Select
Selection.Style = "Percent"
Selection.NumberFormat = "0.0%"
Application.Goto Reference:="R1C1"


Application.Goto Reference:="R2000C14"
Selection.End(xlUp).Select
ActiveCell.Range("A1:B1").Select
Selection.Style = "Currency"
Selection.NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
Selection.NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
Application.Goto Reference:="R1C1"

' DONE

' END MAIN CODE

' REPEATER










Next




' DONE






'Add in Fix Tab Names


For Each ws In Worksheets
If ws.Name Like "######*" Then
wsName = ws.Name
wsName = Left(wsName, 6)
ws.Name = wsName
End If
Next
'Done
'Add in Title From Worksheet

For Each ws In Worksheets
ws.Range("A1").Value = ws.Name
Next

'Done



End Sub
 
Upvote 0
Here's some code. Not sure why it's not working well.


Code: 
Sub UnitMixFormatting()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Dim wsName As String
' REPEATER
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
' END

' Start CODE
    Range("A1").Select
    Cells.Find(What:="SECTION II", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Rows("1:783").EntireRow.Select
    Selection.ClearContents
    ActiveCell.Offset(-1, 0).Range("A1").Select
    Selection.End(xlUp).Select
    ActiveWindow.SmallScroll Down:=-6
    Selection.ClearContents

    Application.Goto Reference:="R1C1"
    ActiveCell.Rows("1:1").EntireRow.Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.Offset(4, 0).Range("A1:N1").Select
    Selection.ClearContents
    ActiveCell.Offset(0, 11).Columns("A:A").EntireColumn.Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Application.Goto Reference:="R1C1"
'  3
' Delete top row????
 ActiveCell.Offset(0, 1).Range("A1").Select
    Application.Goto Reference:="R1C1"
    ActiveCell.Range("A1:O1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    Application.Goto Reference:="R5C1"
    ActiveCell.Offset(-2, 0).Range("A1:A3").Select
    ActiveCell.Activate
    Selection.Merge
    Application.Goto Reference:="R5C2"
    ActiveCell.FormulaR1C1 = "Description"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R3C3"
    ActiveCell.FormulaR1C1 = "Sq"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R4C3"
    ActiveCell.FormulaR1C1 = "Ft"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R3C4"
    ActiveCell.FormulaR1C1 = "Rent"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R4C4"
    ActiveCell.FormulaR1C1 = "Rate"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R3C5"
    ActiveCell.FormulaR1C1 = "Rent"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R4C5"
    ActiveCell.FormulaR1C1 = "Per"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R5C5"
    ActiveCell.FormulaR1C1 = "SqFt"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R3C6"
    ActiveCell.FormulaR1C1 = "#"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R4C6"
    ActiveCell.FormulaR1C1 = "Units"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R3C7"
    ActiveCell.FormulaR1C1 = "Total"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R4C7"
    ActiveCell.FormulaR1C1 = "SqFt"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R3C8"
    ActiveCell.FormulaR1C1 = "Occupied"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R4C8"
    ActiveCell.FormulaR1C1 = "Units"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R3C9"
    ActiveCell.FormulaR1C1 = "Vacant"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R4C9"
    ActiveCell.FormulaR1C1 = "Units"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R3C10"
    ActiveCell.FormulaR1C1 = "Damaged"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R4C10"
    ActiveCell.FormulaR1C1 = "Units"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R3C11"
    ActiveCell.FormulaR1C1 = "Occupied"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R4C11"
    ActiveCell.FormulaR1C1 = "SqFt"
    
    ' Hey
    
    
    Application.Goto Reference:="R5C1"
    ActiveCell.FormulaR1C1 = "Size"
    ' Hey
    
    
    
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R3C12"
    ActiveCell.FormulaR1C1 = "%"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R4C12"
    ActiveCell.FormulaR1C1 = "x"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R5C12"
    ActiveCell.FormulaR1C1 = "xxx"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R3C13"
    ActiveCell.FormulaR1C1 = "%"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R4C13"
    ActiveCell.FormulaR1C1 = "x"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R5C13"
    ActiveCell.FormulaR1C1 = "xx"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R3C14"
    ActiveCell.FormulaR1C1 = "xxx"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R4C14"
    ActiveCell.FormulaR1C1 = "x"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R3C15"
    ActiveCell.FormulaR1C1 = "xxx"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R4C15"
    ActiveCell.FormulaR1C1 = "x"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R5C15"
    ActiveCell.FormulaR1C1 = "xx"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R1C1"
    ActiveCell.Range("A1:O5").Select
    Selection.Font.Bold = True
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R1C1"
    
    ' 4
    
    Application.Goto Reference:="R6C12"
    ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-5]"
    ActiveCell.Offset(1, -1).Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "x"
    ActiveCell.Offset(0, 4).Range("A1").Select
    ActiveCell.FormulaR1C1 = "x"
    ActiveCell.Offset(0, -1).Range("A1").Select
    Selection.End(xlUp).Select
    Application.Goto Reference:="R6C16"
    ActiveCell.FormulaR1C1 = "=RC[-3]/100"
    ActiveCell.Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(0, -3).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.Goto Reference:="R6C12"
    Application.CutCopyMode = False
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.Goto Reference:="R6C16"
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Application.Goto Reference:="R1C1"
    
     Application.Goto Reference:="R6C12"
    ActiveCell.Range("A1:B1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Style = "Percent"
    Selection.NumberFormat = "0.0%"
    Application.Goto Reference:="R1C1"
    
    
    
    
    ' NEW STUFF - SUBTOTALS
    
    Range("F" & Rows.Count).End(xlUp).Offset(2).Formula = "=SUBTOTAL(109,R6C:R[-2]C)"
    Range("G" & Rows.Count).End(xlUp).Offset(2).Formula = "=SUBTOTAL(109,R6C:R[-2]C)"
    Range("H" & Rows.Count).End(xlUp).Offset(2).Formula = "=SUBTOTAL(109,R6C:R[-2]C)"
    Range("I" & Rows.Count).End(xlUp).Offset(2).Formula = "=SUBTOTAL(109,R6C:R[-2]C)"
    Range("J" & Rows.Count).End(xlUp).Offset(2).Formula = "=SUBTOTAL(109,R6C:R[-2]C)"
    Range("K" & Rows.Count).End(xlUp).Offset(2).Formula = "=SUBTOTAL(109,R6C:R[-2]C)"
    Range("N" & Rows.Count).End(xlUp).Offset(2).Formula = "=SUBTOTAL(109,R6C:R[-2]C)"
    Range("O" & Rows.Count).End(xlUp).Offset(2).Formula = "=SUBTOTAL(109,R6C:R[-2]C)"

    ' END SUBTOTALS
    
    
    
    
    Application.Goto Reference:="R6C1"
    Selection.End(xlDown).Select
    ActiveCell.Offset(2, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Totals"
    ActiveCell.Offset(0, 5).Range("A1").Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.Style = "Percent"
    With Selection.Font
        .Name = "Calibri"
        .FontStyle = "Regular"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    Selection.NumberFormat = "0.0%"
    ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-5]"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=RC[-5]/RC[-7]"
    ActiveCell.Offset(0, 1).Range("A1").Select
   
    ActiveCell.Offset(0, 1).Range("A1").Select
    
    ActiveCell.Offset(0, 1).Range("A1").Select
    Application.Goto Reference:="R6C1"
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    ActiveCell.Range("A1:O1").Select
    Selection.Font.Bold = True
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Application.Goto Reference:="R6C1"
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 11).Range("A1:B1").Select
    Selection.Style = "Percent"
    Selection.NumberFormat = "0.0%"
    Application.Goto Reference:="R1C1"
    
    'Add in FIX Income Column formatting
    
        Application.Goto Reference:="R2000C14"
    Selection.End(xlUp).Select
    ActiveCell.Range("A1:B1").Select
    Selection.Style = "Currency"
    Selection.NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
    Selection.NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    Application.Goto Reference:="R1C1"
    
    ' DONE
    
    ' END MAIN CODE
    
    ' REPEATER
    Next
    ' DONE
    
   
   
   
   
   
    'Add in Fix Tab Names
    
    
    For Each ws In Worksheets
    If ws.Name Like "######*" Then
    wsName = ws.Name
    wsName = Left(wsName, 6)
    ws.Name = wsName
    End If
    Next
    'Done
    'Add in Title From Worksheet
    
    For Each ws In Worksheets
    ws.Range("A1").Value = ws.Name
    Next
    
    'Done
   
 
    
    End Sub
 
Upvote 0
If you use...
For Each ws In ThisWorkbook.Worksheets
...you also would also have to do one of the following two things.

1.) Select the worksheet at the beginning of each loop e.g.
Code: 
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
[COLOR="Red"]ws.Select[/COLOR]
'your code here
Next ws
Your existing code would then work on the selected worksheet for each loop. This is an easy fix to make but it's not necessarily the most efficient.


2.) The other method is to reference the ws variable for each Range or Cells reference in your code e.g.
ws.Range("A1")
This method would require you to rewrite much of your code and remove the .Select commands. More efficient code but less convenient to create.
 
Upvote 0
Thanks for the help...

It's erroring out on

ws.Select

though.

Where should I go from here?



Code: 
Sub UnitMixFormatting()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Dim wsName As String
' REPEATER
Dim ws As Worksheet
[COLOR=#ff0000]ws.Select[/COLOR]
For Each ws In ThisWorkbook.Worksheets
' END

' Start CODE
    Range("A1").Select
    Cells.Find(What:="SECTION II", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Rows("1:783").EntireRow.Select
    Selection.ClearContents
    ActiveCell.Offset(-1, 0).Range("A1").Select
    Selection.End(xlUp).Select
    ActiveWindow.SmallScroll Down:=-6
    Selection.ClearContents

    Application.Goto Reference:="R1C1"
    ActiveCell.Rows("1:1").EntireRow.Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.Offset(4, 0).Range("A1:N1").Select
    Selection.ClearContents
    ActiveCell.Offset(0, 11).Columns("A:A").EntireColumn.Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Application.Goto Reference:="R1C1"
'  3
' Delete top row????
 ActiveCell.Offset(0, 1).Range("A1").Select
    Application.Goto Reference:="R1C1"
    ActiveCell.Range("A1:O1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    Application.Goto Reference:="R5C1"
    ActiveCell.Offset(-2, 0).Range("A1:A3").Select
    ActiveCell.Activate
    Selection.Merge
    Application.Goto Reference:="R5C2"
    ActiveCell.FormulaR1C1 = "Description"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R3C3"
    ActiveCell.FormulaR1C1 = "Sq"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R4C3"
    ActiveCell.FormulaR1C1 = "Ft"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R3C4"
    ActiveCell.FormulaR1C1 = "Rent"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R4C4"
    ActiveCell.FormulaR1C1 = "Rate"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R3C5"
    ActiveCell.FormulaR1C1 = "Rent"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R4C5"
    ActiveCell.FormulaR1C1 = "Per"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R5C5"
    ActiveCell.FormulaR1C1 = "SqFt"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R3C6"
    ActiveCell.FormulaR1C1 = "#"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R4C6"
    ActiveCell.FormulaR1C1 = "Units"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R3C7"
    ActiveCell.FormulaR1C1 = "Total"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R4C7"
    ActiveCell.FormulaR1C1 = "SqFt"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R3C8"
    ActiveCell.FormulaR1C1 = "Occupied"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R4C8"
    ActiveCell.FormulaR1C1 = "Units"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R3C9"
    ActiveCell.FormulaR1C1 = "Vacant"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R4C9"
    ActiveCell.FormulaR1C1 = "Units"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R3C10"
    ActiveCell.FormulaR1C1 = "Damaged"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R4C10"
    ActiveCell.FormulaR1C1 = "Units"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R3C11"
    ActiveCell.FormulaR1C1 = "Occupied"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R4C11"
    ActiveCell.FormulaR1C1 = "SqFt"
    
    ' Hey
    
    
    Application.Goto Reference:="R5C1"
    ActiveCell.FormulaR1C1 = "Size"
    ' Hey
    
    
    
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R3C12"
    ActiveCell.FormulaR1C1 = "%"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R4C12"
    ActiveCell.FormulaR1C1 = "x"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R5C12"
    ActiveCell.FormulaR1C1 = "xxx"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R3C13"
    ActiveCell.FormulaR1C1 = "%"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R4C13"
    ActiveCell.FormulaR1C1 = "x"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R5C13"
    ActiveCell.FormulaR1C1 = "xx"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R3C14"
    ActiveCell.FormulaR1C1 = "xxx"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R4C14"
    ActiveCell.FormulaR1C1 = "x"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R3C15"
    ActiveCell.FormulaR1C1 = "xxx"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R4C15"
    ActiveCell.FormulaR1C1 = "x"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R5C15"
    ActiveCell.FormulaR1C1 = "xx"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R1C1"
    ActiveCell.Range("A1:O5").Select
    Selection.Font.Bold = True
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.Goto Reference:="R1C1"
    
    ' 4
    
    Application.Goto Reference:="R6C12"
    ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-5]"
    ActiveCell.Offset(1, -1).Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "x"
    ActiveCell.Offset(0, 4).Range("A1").Select
    ActiveCell.FormulaR1C1 = "x"
    ActiveCell.Offset(0, -1).Range("A1").Select
    Selection.End(xlUp).Select
    Application.Goto Reference:="R6C16"
    ActiveCell.FormulaR1C1 = "=RC[-3]/100"
    ActiveCell.Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(0, -3).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.Goto Reference:="R6C12"
    Application.CutCopyMode = False
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.Goto Reference:="R6C16"
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Application.Goto Reference:="R1C1"
    
     Application.Goto Reference:="R6C12"
    ActiveCell.Range("A1:B1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Style = "Percent"
    Selection.NumberFormat = "0.0%"
    Application.Goto Reference:="R1C1"
    
    
    
    
    ' NEW STUFF - SUBTOTALS
    
    Range("F" & Rows.Count).End(xlUp).Offset(2).Formula = "=SUBTOTAL(109,R6C:R[-2]C)"
    Range("G" & Rows.Count).End(xlUp).Offset(2).Formula = "=SUBTOTAL(109,R6C:R[-2]C)"
    Range("H" & Rows.Count).End(xlUp).Offset(2).Formula = "=SUBTOTAL(109,R6C:R[-2]C)"
    Range("I" & Rows.Count).End(xlUp).Offset(2).Formula = "=SUBTOTAL(109,R6C:R[-2]C)"
    Range("J" & Rows.Count).End(xlUp).Offset(2).Formula = "=SUBTOTAL(109,R6C:R[-2]C)"
    Range("K" & Rows.Count).End(xlUp).Offset(2).Formula = "=SUBTOTAL(109,R6C:R[-2]C)"
    Range("N" & Rows.Count).End(xlUp).Offset(2).Formula = "=SUBTOTAL(109,R6C:R[-2]C)"
    Range("O" & Rows.Count).End(xlUp).Offset(2).Formula = "=SUBTOTAL(109,R6C:R[-2]C)"

    ' END SUBTOTALS
    
    
    
    
    Application.Goto Reference:="R6C1"
    Selection.End(xlDown).Select
    ActiveCell.Offset(2, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Totals"
    ActiveCell.Offset(0, 5).Range("A1").Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.Style = "Percent"
    With Selection.Font
        .Name = "Calibri"
        .FontStyle = "Regular"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    Selection.NumberFormat = "0.0%"
    ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-5]"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=RC[-5]/RC[-7]"
    ActiveCell.Offset(0, 1).Range("A1").Select
   
    ActiveCell.Offset(0, 1).Range("A1").Select
    
    ActiveCell.Offset(0, 1).Range("A1").Select
    Application.Goto Reference:="R6C1"
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    ActiveCell.Range("A1:O1").Select
    Selection.Font.Bold = True
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Application.Goto Reference:="R6C1"
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 11).Range("A1:B1").Select
    Selection.Style = "Percent"
    Selection.NumberFormat = "0.0%"
    Application.Goto Reference:="R1C1"
    
    'Add in FIX Income Column formatting
    
        Application.Goto Reference:="R2000C14"
    Selection.End(xlUp).Select
    ActiveCell.Range("A1:B1").Select
    Selection.Style = "Currency"
    Selection.NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
    Selection.NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    Application.Goto Reference:="R1C1"
    
    ' DONE
    
    ' END MAIN CODE
    
    ' REPEATER
    Next
    ' DONE
    
   
   
   
   
   
    'Add in Fix Tab Names
    
    
    For Each ws In Worksheets
    If ws.Name Like "######*" Then
    wsName = ws.Name
    wsName = Left(wsName, 6)
    ws.Name = wsName
    End If
    Next
    'Done
    'Add in Title From Worksheet
    
    For Each ws In Worksheets
    ws.Range("A1").Value = ws.Name
    Next
    
    'Done
   
 
    
    End Sub
 
Upvote 0
Ok - I got it figured out...

Thanks for the help!

Here's the fix:

Code: 
[COLOR=red]For Each ws In Worksheets
[/COLOR]ws.Select

Thanks for giving me the ws.Select!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Creating a Macro/Script where it automatically deletes All Rows With a Blank Cell
Replies
4
Views
213
Joe4
Joe4
J
  • Question Question
Using Sheets().Select in a Worksheet_Activate() macro
2
Replies
10
Views
471
jdun
J
M
  • Solved
Macro stops after message box appear
Replies
3
Views
535
bobsan42
bobsan42
M
  • Question Question
Macro - Merge all files into one sheet
Replies
2
Views
939
MountainFog
M
N
  • Solved
Can I repeat macro attached to data validation button 1000 times and then stop?
Replies
2
Views
336
ned lud
N

Forum statistics

Threads
1,224,559
Messages
6,179,517
Members
452,921
Latest member
BBQKING

Share this page

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