Adding a column

[AllyB]

Hi there,
I'm looking for vba code that will automatically insert a blank column (D:D) into my spreadsheet on the first day of every month on the opening of my spreadsheet and in cell D1 the code would insert the month in the format mmm_yy
Not sure how much further information is required.

I'm all new to this and any help would be greatly appreciated.

Ally B

 

[JoeMo]

Hi there,
I'm looking for vba code that will automatically insert a blank column (D:D) into my spreadsheet on the first day of every month on the opening of my spreadsheet and in cell D1 the code would insert the month in the format mmm_yy
Not sure how much further information is required.

I'm all new to this and any help would be greatly appreciated.

Ally B

Do you mean upon opening the workbook (file), or just when activating a sheet in the workbook? If the former, do you want to do this on the sheet that is active when the workbook is first opened or some other sheet? If some other sheet, what is the tab name on that sheet?

 

[AllyB]

Joe,
Sorry for not giving the full details!!
I would like the workbook to open on Sheet1 and to insert the column D:D on Sheet1

 

[AllyB]

Thank you for any help.

Yours with all appreciation

AllyB

 

[JoeMo]

Joe,
Sorry for not giving the full details!!
I would like the workbook to open on Sheet1 and to insert the column D:D on Sheet1

This is code for a Thisworkbook module for your workbook. Follow the instructions below to install the code in your workbook. Whenever the workbook is opened your desired date will appear in D1 on Sheet1.
To install the code:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and double-click the 'Thisworkbook' icon.
3. Copy the code below from your browser window and paste it into the white space in the VBE window.
4. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
5. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Workbook_Open()
Sheets("Sheet1").Activate
If Day(Date) = 1 Then
    Range("D1") = Format(Date, "mmm_yy")
ElseIf Range("D1") = "" Or Left(Range("D1"), 3) <> Left(MonthName(Month(Date)), 3) Then
    Range("D1") = Format(DateSerial(Year(Date), Month(Date), 1), "mmm_yy")
End If
End Sub

 

[AllyB]

Joe!!!!

Woohooo!!! Yipeeeee!

Thank you SO much!!!

It works perfectly!!

Ally B )))

 

[JoeMo]

Joe!!!!

Woohooo!!! Yipeeeee!

Thank you SO much!!!

It works perfectly!!

Ally B )))

Glad to hear that, but I just noticed that I neglected to add a new col D each month. If that's a problem, let me know and I'll fix it.

 

[AllyB]

Thank you Joe, the date at the top was the important part. I recorded a macro for the insert.

Again; thanks for your help.

Ally B

 

[JoeMo]

Thank you Joe, the date at the top was the important part. I recorded a macro for the insert.

Again; thanks for your help.

Ally B

You are welcome - thanks for the reply.