shorter format code

[tpkelley_no]

can this code be shorter?

Sheets("Original_Cost").Select
Columns("C:C").Select
    Selection.EntireColumn.Hidden = True

Sheets("New_Cost").Select
Columns("C:C").Select
    Selection.EntireColumn.Hidden = True

Sheets("Original_Budget").Select
Columns("C:C").Select
    Selection.EntireColumn.Hidden = True

Sheets("Approved_Changes").Select
Columns("C:C").Select
    Selection.EntireColumn.Hidden = True

Sheets("Current_Projections").Select
Columns("C:C").Select
    Selection.EntireColumn.Hidden = True

Sheets("Cost_to_Date").Select
Columns("C:C").Select
    Selection.EntireColumn.Hidden = True
' This will put a header on the Original_Cost Sheet

Sheets("Original_Cost").Select

ActiveCell.FormulaR1C1 = "Cost Code"
    Range("A1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
    End With
    Selection.Font.Bold = True
    
Range("B1").Select
    ActiveCell.FormulaR1C1 = "Type"
    Range("B1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
    End With
    Selection.Font.Bold = True
    
   Range("D1").Select
    ActiveCell.FormulaR1C1 = "Description"
    Range("D1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
     End With
    Selection.Font.Bold = True

    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Original Budget"
    Range("E1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
End With
    Selection.Font.Bold = True

 Range("F1").Select
    ActiveCell.FormulaR1C1 = "Approved Owner Changes"
    Range("F1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
    End With
    Selection.Font.Bold = True
    
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Revised Budget"
    Range("G1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
    End With
    Selection.Font.Bold = True

   Range("H1").Select
    ActiveCell.FormulaR1C1 = "Current Projection"
    Range("H1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
    End With
    Selection.Font.Bold = True
    
   Range("I1").Select
    ActiveCell.FormulaR1C1 = "Variance"
    Range("I1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
     End With
    Selection.Font.Bold = True
    
    Range("J1").Select
   ActiveCell.FormulaR1C1 = "Total Job Cost to Date"
   Range("J1").Select
   With Selection
       .HorizontalAlignment = xlCenter
       .VerticalAlignment = xlCenter
        .WrapText = True
     End With
    Selection.Font.Bold = True

    Range("K1").Select
    ActiveCell.FormulaR1C1 = "Total Committed"
    Range("K1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
    End With
    Selection.Font.Bold = True
    
   Range("L1").Select
    ActiveCell.FormulaR1C1 = "Committed Cost JTD"
    Range("L1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
     End With
    Selection.Font.Bold = True

 Range("M1").Select
    ActiveCell.FormulaR1C1 = "Committed Cost Remaining"
    Range("M1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
    End With
    Selection.Font.Bold = True
    
     Range("N1").Select
    ActiveCell.FormulaR1C1 = "Non Commited Projection"
    Range("N1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
     End With
    Selection.Font.Bold = True
    
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "Non committed Cost JTD"
    Range("O1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
    End With
    Selection.Font.Bold = True
    
   Range("P1").Select
    ActiveCell.FormulaR1C1 = "Non Committed Cost Remaining"
    Range("P1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
    End With
    Selection.Font.Bold = True
    
End Sub

 

[John_w]

Yes, the pairs of Selects and Selections cancel each other out, so you can do:

    Sheets("Original_Cost").Columns("C").Hidden = True

    With Sheets("Original_Cost").Range("B1")
        .FormulaR1C1 = "Type"
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Font.Bold = True
    End With

    'etc.

Do you get the idea?

 

[Jerry Sullivan]

In addition to John's comment, the code can be streamlined by applying values and formats to the header range in one step instead of separately for each cell.

Sub AddHeaders()
 Dim wks As Worksheet
 
 For Each wks In Sheets(Array("Original_Cost", "New_Cost", "Original_Budget", _
  "Approved_Changes", "Current_Projections", "Cost_to_Date"))

  wks.Columns("C:C").EntireColumn.Hidden = True
 Next wks
 
 With Sheets("Original_Cost")
   .Select
   With .Range("A1:P1")
      .Value = Array("Cost Code", "Type", "", "Description", "Original Budget", _
         "Approved Owner Changes", "Revised Budget", "Current Projection", _
         "Variance", "Total Job Cost to Date", "Total Committed", "Committed Cost JTD", _
         "Committed Cost Remaining", "Non Commited Projection", _
         "Non committed Cost JTD", "Non Committed Cost Remaining")
         
      .HorizontalAlignment = xlCenter
      .VerticalAlignment = xlCenter
      .WrapText = True
      .Font.Bold = True
   End With
 End With
End Sub

This also formats Cell C1 and gives it a blank value. The code can be tweaked if the existing value or formatting of C1 needs to be maintained.