Inserting 2 new columns between every column

[mzalikhan]

Hi Everybody

I have got a spreadsheet with say 1000 columns of data. What I want to do is to insert 2 new columns after each column. how can i do that quickly? i mean other than inserting columns one by one?

Peace
Muhammad Zeeshan

 

[daverunt]

A macro should do it.
It assumes the sheet with data is sheet1 and there is data in every column to be separated by 2 new columns.
Starts from Cell A1. Counts the number of full colimns to the right at the start.

Sub Add_Columns()
 
lastCol = ThisWorkbook.Sheets("Sheet1").Range("a1").End(xlToRight).Column
 
Sheets("Sheet1").Range("A1").Activate
 
For i = 1 To lastCol
 
'Insert column to the right of the active cell
ActiveCell.EntireColumn.Offset(0, 1).Insert
ActiveCell.EntireColumn.Offset(0, 1).Insert
ActiveCell.Offset(0, 3).Activate
 
 Next
MsgBox ("Macro Finished")
End Sub

 

[Sektor]

Sub AddTwoColumns()
 
 Dim lastColumn As Long, i As Long
    
    lastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
 
    For i = lastColumn To 1 Step -1
        Cells(1, i).EntireColumn.Insert
        Cells(1, i).EntireColumn.Insert
     Next

End Sub

 

[mzalikhan]

Thanks Sektor and Daverunt. But the problem is that i have never used Macros

Could you please explain me how to use it??

Peace
Muhammad Zeeshan

 

[daverunt]

Make a copy of the workbook to practice on.
Open the copy
Press ALT+F11 - this opens the Visual Basic Editor
Right-Click on the VBAProject('workbookname')
Select - Insert Module.
Paste the code above into the blank module - your choice which.
Save and Close just the Editor - It can be run from the editor but we'll do it the 'normal' way.
On the workbook select the View Tab then Macros -(may be different depending on version)
The highlighted macro is listed - select Run.


In the same window you can see edit, which is another method into the Editor.

If there is a problem post back.