Excel VBA to Merge and Center specific Range of cells after inserting a new sheet

[astrid22]

Hi, I'm relatively new to VBA. I have the below code to insert a new sheet and some other formatting that I need. What I am trying to do is merge and center cells A1:C1 after inserting the new sheet. And then insert a phrase in the merged cells.

Sub GenerateConf()
  Dim ws As Worksheet
  Dim NameCount As Long
  Dim NameBase As String
  
  NameBase = Format(Date, "mm.dd.yyyy OA ")
  For Each ws In Worksheets
    If ws.Name Like NameBase & "#*" Then NameCount = NameCount + 1
  Next ws
  Sheets.Add(After:=Sheets(Sheets.Count)).Name = NameBase & NameCount + 1
  Sheets(Sheets.Count).Tab.ColorIndex = 50
  End Sub

I tried to use the below code to merge and center A1:C1 but I am getting an error:

With Worksheet.Range("FirstCell:LastCell")
  .HorizontalAlignment = xlCenter
 .VerticalAlignment = xlCenter
 .Merge
End With

Any ideas?

 

[astrid22]

Hi, I have solved this. Thank you.

Sub GenerateConf()
  Dim ws As Worksheet
  Dim NameCount As Long
  Dim NameBase As String
  
  NameBase = Format(Date, "mm.dd.yyyy OA ")
  For Each ws In Worksheets
    If ws.Name Like NameBase & "#*" Then NameCount = NameCount + 1
  Next ws
  Sheets.Add(After:=Sheets(Sheets.Count)).Name = NameBase & NameCount + 1
  Sheets(Sheets.Count).Tab.ColorIndex = 50
  
  Range("A1:C1").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
    Range("A2:C2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
  
End Sub
[\CODE]