[VBA] Create new column every nth column and copy and paste the respective previous column into the new one

[bnbcat]

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.

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!

 

[Rhodie72]

Your question is not totally clear and leaves vast room for anwer misunderstanding of your intent. From your screen shot I woould have just inseted column before G:G and then updated with information as required but you say this is a huge task. Unfortunately I don't see the size of the tast in what you have demonstrated.

Adding columns to a worksheet is easy. creating a table with additional coluns without affecting tables in the same columns is a lttle more intricate.

So here is a question for you:

Are you wanting to add columns to the entire worksheet or just to specific table(s)?