VBA: Formatting Cells on Two Sheets

[nickshep85]

I have a code with which I am trying to format Cells A1:I2 on two sheets. I have got the code to add in the formulas and values into the cells, but the code only formats the cells in the first worksheet. Can anyone tell me where I am going wrong?

Sub Transfer_Data()

'Add New Workbook
Workbooks.Add template:=xlWorksheet
'Add New Sheet
ActiveWorkbook.Sheets.Add
'Name Sheets
Sheets("Sheet1").Name = "Tyres"
Sheets("Sheet2").Name = "Mechanical"

Dim ws As Worksheet
On Error Resume Next
For Each ws In Worksheets
    With ws
    .Range("A1").FormulaR1C1 = "=MID(CELL(""filename""),SEARCH(""["",CELL(""filename""))+1, SEARCH(""]"",CELL(""filename""))-SEARCH(""["",CELL(""filename""))-5)"

    .Range("B1").FormulaR1C1 = "=today()"
    .Range("B1").Select

    .Range("B1").Copy
    .Range("B1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    .Application.Goto Reference:="R1C1"
    .Application.CutCopyMode = False

    .Range("A2").Value = "CODE"
    .Range("B2").Value = "DESCRIPTION"
    .Range("C2").Value = "XXX"
    .Range("D2").Value = "XXX"
    .Range("E2").Value = "XXX"
    .Range("F2").Value = "XXX"
    .Range("G2").Value = "PRICE"
    .Range("H2").Value = "XXX"
    .Range("I2").Value = "XXX"

    .Range("A1:I2").Select
    With Selection
      .Font.Size = 14
      .Font.Bold = True
      .Font.Color = vbWhite
      .Columns.AutoFit
      .Interior.Color = vbBlue
    End With
    
    End With
    
    Next ws
    
        
Range("A1").Select


End Sub

 

[VoG]

Try

        Sub Transfer_Data()

        'Add New Workbook
        Workbooks.Add template:=xlWorksheet
        'Add New Sheet
        ActiveWorkbook.Sheets.Add
        'Name Sheets
        Sheets("Sheet1").Name = "Tyres"
        Sheets("Sheet2").Name = "Mechanical"

        Dim ws As Worksheet
        On Error Resume Next
        For Each ws In Worksheets
            With ws
            .Range("A1").FormulaR1C1 = "=MID(CELL(""filename""),SEARCH(""["",CELL(""filename""))+1, SEARCH(""]"",CELL(""filename""))-SEARCH(""["",CELL(""filename""))-5)"

            .Range("B1").FormulaR1C1 = "=today()"
            .Range("B1").Select

            .Range("B1").Copy
            .Range("B1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            .Application.Goto Reference:="R1C1"
            .Application.CutCopyMode = False

            .Range("A2").Value = "CODE"
            .Range("B2").Value = "DESCRIPTION"
            .Range("C2").Value = "XXX"
            .Range("D2").Value = "XXX"
            .Range("E2").Value = "XXX"
            .Range("F2").Value = "XXX"
            .Range("G2").Value = "PRICE"
            .Range("H2").Value = "XXX"
            .Range("I2").Value = "XXX"

            With .Range("A1:I2")
              .Font.Size = 14
              .Font.Bold = True
              .Font.Color = vbWhite
              .Columns.AutoFit
              .Interior.Color = vbBlue
            End With
            
            End With
            
            Next ws
            
                
        Range("A1").Select


        End Sub

 

[nickshep85]

Thanks for this VoG, perfect as always

Again I see the error of my ways.