如何在VBA中为Excel表格添加以当前日期为标题的新列

It looks like your original code got cut off mid-line, which is why it wasn’t running properly. Let’s fix that and create a reliable macro that adds a new column with the current month-year as the header right after the last existing column in your "Facebook" sheet.

What Was Off About Your Initial Code? #

• The code was incomplete (cut off at ws.Range("2" & ws.Columns.Coun...) so Excel couldn’t parse it fully.
• Using column as the macro name risks conflicts with Excel’s built-in terms—better to use a specific, unique name.
• The logic to find the last used column wasn’t fully implemented correctly.

Working Macro Code #

Here’s a polished, functional version that does exactly what you need:

Sub AddMonthColumn()
    Dim ws As Worksheet
    Dim lastCol As Long
    Dim monthHeader As String
    
    ' Target your "Facebook" worksheet
    Set ws = ThisWorkbook.Sheets("Facebook")
    
    ' Find the last used column in row 2 (where your headers are stored)
    lastCol = ws.Cells(2, ws.Columns.Count).End(xlToLeft).Column
    
    ' Format current date to "MMM-YY" (e.g., May-24)
    monthHeader = Format(Now(), "MMM-YY")
    
    ' Insert a new column right after the last existing one
    ws.Columns(lastCol + 1).Insert Shift:=xlToRight
    
    ' Set the header text for the new column
    ws.Cells(2, lastCol + 1).Value = monthHeader
    
    ' Optional: Make the header stand out with formatting
    With ws.Cells(2, lastCol + 1)
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
    End With
    
    ' Optional: Auto-fit the column to fit the header text
    ws.Columns(lastCol + 1).AutoFit
End Sub

How to Turn This Into a Clickable Button #

1. Open the Developer tab in Excel (if you don’t see it, enable it via File > Options > Customize Ribbon).
2. Click Insert in the Controls group, then select Button (Form Control).
3. Draw the button on your worksheet where you want it to appear.
4. In the "Assign Macro" window, select AddMonthColumn and click OK.
5. Right-click the button, choose Edit Text, and rename it to something like "Add Month Column".

Quick Customization Tips #

• If your headers are in a different row (not row 2), replace the 2 in ws.Cells(2, ...) with your header row number.
• To change the date format (e.g., "May 2024" instead of "May-24"), swap "MMM-YY" for "MMMM YYYY" in the Format function.
• Remove the optional formatting lines if you don’t want bold/centered headers or auto-fitted columns.

内容的提问来源于stack exchange，提问作者Meels93