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

bnbcat

New Member
Joined
Apr 27, 2016
Messages
41
Office Version
  1. 365
Platform
  1. Windows
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.

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!
 

Attachments

  • Capture.PNG
    Capture.PNG
    20.5 KB · Views: 13

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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)?
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,774
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top