PDA

View Full Version : Applying Format To ALL sheets in Workbook



Mattster2020
10-09-2009, 03:59 AM
Morning all,

I wish to format all sheets in a workbook using the below code:

Range("E4").Select
ActiveCell.FormulaR1C1 = "Total Time (Mins)"
Range("E4").Select
Selection.Copy
Range("F4").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Total Time (Actual)"
Range("F5").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(RC[-2]=0,RC[-1]=0),"""",TEXT(RC[-1]/60/24,""hh:mm""))"
Range("F5").Select
Selection.AutoFill Destination:=Range("F5:F135"), Type:=xlFillDefault
Range("F5:F135").Select
ActiveWindow.SmallScroll Down:=-114
Range("G5").Select
ActiveCell.FormulaR1C1 = "=IF(OR(RC[-3]=0,RC[-2]=0),"""",RC[-3]/RC[15])"
Range("G5").Select
Selection.AutoFill Destination:=Range("G5:G135"), Type:=xlFillDefault
Range("G5:G135").Select
ActiveWindow.SmallScroll Down:=-120
Range("V5").Select
ActiveCell.FormulaR1C1 = "=IF(OR(RC[-18]=0,RC[-17]=0),"""",RC[-17]/60)"
Range("V5").Select
Selection.AutoFill Destination:=Range("V5:V135"), Type:=xlFillDefault
Range("V5:V135").Select
ActiveWindow.SmallScroll Down:=-102

How can I go about doing this?

Regards,

Matt

Bob Phillips
10-09-2009, 04:12 AM
Dim i As Long

For i = 1 To Worksheets.Count

With Worksheets(i)

.Range("F4").Value = "Total Time (Mins)"
.Range("E4").Value = "Total Time (Actual)"
.Range("F5:F135").FormulaR1C1 = _
"=IF(OR(RC[-2]=0,RC[-1]=0),"""",TEXT(RC[-1]/60/24,""hh:mm""))"
.Range("G5:G1355").FormulaR1C1 = _
"=IF(OR(RC[-3]=0,RC[-2]=0),"""",RC[-3]/RC[15])"
.Range("V5:V135").FormulaR1C1 = _
"=IF(OR(RC[-18]=0,RC[-17]=0),"""",RC[-17]/60)"
End With
Next i