Hi Everybody,
I have an issue which seems to have already solved by a google search, however due to my considerable lack of knowledge, my code is not.
I have made a macro using the record function, this macro changes number of different things; date formats, column widths, formula etc.
It works perfectly on the sheet I am on.
However, I would like the macro to produce the same results on all sheets, bar one.
Below is the code from the recorded macro:
While searching for a solution, I found some additional code to wrap around my current.
I understand the theory, however I think my inability to code is a problem here.
From what I can understand, I may be struggling with, at least, defining ranges?
It seems it was not as easy as just inserting my recorded macro code into the googled solution.
I hope this is an easy job?
I have an issue which seems to have already solved by a google search, however due to my considerable lack of knowledge, my code is not.
I have made a macro using the record function, this macro changes number of different things; date formats, column widths, formula etc.
It works perfectly on the sheet I am on.
However, I would like the macro to produce the same results on all sheets, bar one.
Below is the code from the recorded macro:
Rich (BB code):
Sub Format_Sheets()
Rows("1:2").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Columns("C:C").Select
Selection.NumberFormat = "m/d/yyyy"
Range("K4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Range("K4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
Columns("A:K").Select
Columns("A:K").EntireColumn.AutoFit
Range("B1:C2").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
ActiveCell.FormulaR1C1 = "RECONCILIATION SHEET"
Range("B1:B2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
With Selection.Font
.ThemeColor = xlThemeColorAccent4
.TintAndShade = -0.249977111117893
End With
Selection.Font.Bold = True
End Sub
While searching for a solution, I found some additional code to wrap around my current.
I understand the theory, however I think my inability to code is a problem here.
From what I can understand, I may be struggling with, at least, defining ranges?
It seems it was not as easy as just inserting my recorded macro code into the googled solution.
Rich (BB code):
Sub Format_All_Macros()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "HEADER SHEET" Then
Rows("1:2").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Columns("C:C").Select
Selection.NumberFormat = "m/d/yyyy"
Range("K4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Range("K4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
Columns("A:K").Select
Columns("A:K").EntireColumn.AutoFit
Range("B1:C2").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
ActiveCell.FormulaR1C1 = "RECONCILIATION SHEET"
Range("B1:B2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
With Selection.Font
.ThemeColor = xlThemeColorAccent4
.TintAndShade = -0.249977111117893
End With
Selection.Font.Bold = True
End If
Next
End Sub
I hope this is an easy job?