asked on

Macro to re-arrange the data in columns and insert column "Month" in YYYYMM format

How do I change the following code :

Sub InsertMonth() 'insert MONTH corresponding to the date
Dim LastRow As Long

      LastRow = Range("A" & Rows.count).End(xlUp).Row
      
      Range("B1").EntireColumn.Insert Shift:=xlToRight

      Range("B1").Value = "Month"
 
      With Range("B2:B" & LastRow)
            .Formula = "=TEXT(A2, ""mmm"")"
            .Value = .Value       ' replace formulas with values - optional
      End With

End Sub

Open in new window

to get Excel to insert the column "YYYYMM" in the first column A regardless of the position of the "Date" Column ?

In the above script, one would need to place the "Date" Column in Column A , and then it will insert Month in Column B, but the month is in text format.

Note:
-Date is in the format of mm/dd/yyyy
- there are data from row 2 onwards
- objective is re-arrange the data in the desired columns (position) and the have the column "Month" inserted.

Thanks,

Subodh Tiwari (Neeraj)

Why not upload a small sample file which would help to visualize the end result you are trying to achieve? If required, mock up the desired output you are trying to achieve on another sheet and name it "Desired Output".

bluue s

ASKER

EE_Insert-Month-Re-aarange.xlsx

See attached, thanks.

Subodh Tiwari (Neeraj)

Thanks for the sample file but unfortunately it is of no use without any data in it.
Dates are tricky to handle and that's why I requested you to upload a sample file.
Please populate the Current Sheet with some sample data (max 5 rows) and mock up the Desired output Sheet manually to show us the end result you are trying to achieve and upload the file again.

bluue s

ASKER

See attached with data, thanks.
EE_Insert-Month-Re-aarange.xlsx

Subodh Tiwari (Neeraj)

Please give this a try...

Sub InsertMonthColumn()
Dim wsData  As Worksheet
Dim lr      As Long

Application.ScreenUpdating = False

Set wsData = Worksheets("Current")                  'Sheet with Data, Change the Sheet Name if required
lr = wsData.Cells(Rows.Count, "K").End(xlUp).Row    'Finding the last row with data in Date Column

wsData.Range("K1:K" & lr).Cut
wsData.Range("A1").Insert shift:=xlToRight

wsData.Columns(1).Insert

wsData.Range("A1").Value = "Month"

With wsData.Range("A2:A" & lr)
    .Formula = "=TEXT(B2,""YYYYMM"")"
    .Value = .Value
End With
wsData.UsedRange.Columns.AutoFit
Application.ScreenUpdating = True
End Sub

Open in new window

In the attached, click the button called "Insert Month Column" on Current Sheet.
Don't forget to change the name of the Data Sheet in the code, please refer to the comment added in the code. Right now the code assumes that the name of the Data Sheet is Current.
EE_Insert-Month-Re-aarange.xlsm

bluue s

ASKER

Thanks, sorry I forgot to remove the header fields in the "current" tab.
They do not have header fields.
Thus is it possible to tweak it so that the header fields are also in row 1 with correct sequence at the Desired Output ?

Thanks.

Subodh Tiwari (Neeraj)

Like this?
EE_Insert-Month-Re-aarange-v2.xlsm

bluue s

ASKER

Please see attached file again for the tab "Current" and "Desired Output" (both tabs highlighted in red).

Thanks.
EE_Insert-Month-Re-aarange-v2--Revi.xlsm

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bluue s

ASKER

Thanks for the patience and support as always.

Subodh Tiwari (Neeraj)

You're welcome!

Macro to re-arrange the data in columns and insert column &quot;Month&quot; in YYYYMM format

Macro to re-arrange the data in columns and insert column "Month" in YYYYMM format