Hi,
I've got a massive report that has locations by time period going across the top. It is a huge manual task when I need to add a new time period. As shown in my image below, I'm trying to add a new column for 2025, and then want to copy and paste whatever is in the 2024 column to the new 2025 column, for each location.
The furthest i got was trying to insert a new column using the below code. I played with the different values, but I couldn't get it to ignore the row header columns and space out the new columns properly.
Any guidance would be helpful. Thank you!
I've got a massive report that has locations by time period going across the top. It is a huge manual task when I need to add a new time period. As shown in my image below, I'm trying to add a new column for 2025, and then want to copy and paste whatever is in the 2024 column to the new 2025 column, for each location.
The furthest i got was trying to insert a new column using the below code. I played with the different values, but I couldn't get it to ignore the row header columns and space out the new columns properly.
VBA Code:
Sub InsertEveryOtherColumn()
Dim colNo, colStart, colFinish, colStep As Long
Dim rng2Insert As Range
colStep = 2
colStart = Application.Selection.Cells(1, 1).Column + 1
colFinish = (ActiveSheet.UsedRange.SpecialCells( _
xlCellTypeLastCell).Column * 2) - colStart
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For colNo = colStart To colFinish Step colStep
ActiveSheet.Cells(1, colNo).EntireColumn.Insert
Next
Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic
End Sub
Any guidance would be helpful. Thank you!