Copy and Paste columns every n-th column

[vadius]

Hi there,

I have a quick questionon the way to copy some columns and then paste each column every n-th on a new sheet.

In one sheet, the data file, I have 10 columns of data, and I want to copy paste them every 3 columns in a new sheet to have something like this : Column A (data, corresponding to the first column of my data file), Column B (empty), Column C (empty), Column D (data, corresponding to the second column of my data file) .

Have you guys any ideas how to code this ?

Thanks

 

[AlphaFrog]

Sub Copy_and_Insert_Columns()
    Dim i As Long
    Application.ScreenUpdating = False
    ActiveSheet.Copy After:=Sheets(Sheets.Count)
    For i = 2 To 27 Step 3
        Columns(i).Resize(, 2).Insert
    Next i
    Application.ScreenUpdating = True
End Sub

 

[vadius]

Thank for this.

I now need to feed the empty columns with columns from a different sheet. Below is an attempt but I have an error message. The code trioes to get the column in the sheet "cleanshs" and copy the column in the "Final index selection list". For example th code takes the "B" column of cleanshs and tries to copy it in colomn "E", then column "C" to be pasted in column "F" etc etc...

Thanks!


Sub Copy_and_Insert_Columns()
Dim i As Long
Application.ScreenUpdating = False
Worksheets("Final Index Selection List").Copy After:=Sheets(Sheets.Count)
For i = 2 To 60 Step 3
Columns(i).Resize(, 2).Insert
Next i
Application.ScreenUpdating = True

Dim j As Long
Application.ScreenUpdating = False
For j = 2 To 60
Worksheets("Cleanshs").Columns(j + 3).Copy
Worksheets("Final Index Selection List").Columns(j).Paste
Next j
Application.ScreenUpdating = False

End Sub

 

[vadius]

I actually have changed it a bit but I still have a problem.
Column B from "cleanshs" will be copied to column E in the "index selection list" , but Column C will be copied to column F and I want it to be copied to column G. Then column D will copied to column I etc etc..

Thanks !

=======================================================
Sub Copy_and_Insert_Columns()
Dim i As Long
Application.ScreenUpdating = False
Worksheets("Final Index Selection List").Copy After:=Sheets(Sheets.Count)
For i = 2 To 60 Step 3
Columns(i).Resize(, 2).Insert
Next i
Application.ScreenUpdating = True

Dim j As Long
Application.ScreenUpdating = False
For j = 2 To 60

Worksheets("Cleanshs").Columns(j).Copy
Worksheets("Cleanprice (5)").Columns(j + 3).Select
ActiveSheet.Paste

Next j
Application.ScreenUpdating = False

End Sub

 

[AlphaFrog]

Forum Tip: Pasting VBA code in the forum editor
It would be best if you surround your VBA code with code tags e.g [CODE]your VBA code here[/CODE]
It makes reading your VBA code much easier.
When you're in the forum editor, highlight your pasted VBA code and then click on the icon with the pound or number sign #.

Sub Copy_and_Insert_Columns()
    
    Dim i As Long, wsNew As Worksheet
    Application.ScreenUpdating = False
    
    Worksheets("Final Index Selection List").Copy After:=Sheets(Sheets.Count)
    Set wsNew = ActiveSheet
    wsNew.Columns("B:C").Insert
    
    For i = 2 To 40 Step 2
        Worksheets("Cleanshs").Columns(i).Resize(, 2).Copy
        wsNew.Columns((i / 2 - 1) * 3 + 5).Insert Shift:=xlToRight
    Next i
    
    Application.CutCopyMode = True
    Application.ScreenUpdating = True
End Sub

 

[vadius]

Hi

Thanks but this is not exactly what I am looking for. I would like to attach my spreadsheet here but the fiture is disabled. Can the administrator help on this ?

Below is what I would need once my spreadsheet will be uploaded...

Thanks

Attached is the excel files I will be refering to.
The first three tabs are, for each of the 20 stock names :
"Cleanprice": Stock prices
"Cleanshs": Nb of shares
"Cleanff" : Free Float

I want to create a file called Index Selection List where I will find, for each stock, the daily price, daily number of shares and the daily free float factor. A fourth column with be the product of all those three figures namely price*shares number*free float. I also want the macro the calculate the average of this last column.

So I need a macro that can help me to extract the data contained in the first three tabs (first tab is the prices for all the stocks, second is the number of shares and third is free float factor) and organize everything into one final tab as explained above (see example in the excel attached, the tab called "Index Selection List")

Can you please help on this? I think it's more clear now with the example attached.

Thank you very much

 

[frankbrett]

Hi, similar to the above, I'm looking for code to copy 'n' rows of data, and paste special transpose into another sheet but for each value to have one column between them ie Cells A2, a3, a4....to be copied into a1, a3, a5 etc of sheet two. The number of rows will be unknown each time so the 'copy' code will incorporate 'xldown' and the paste is each column until all data is copied. Is there a simple way to modify the above code to achieve this?

 

[frankbrett]

Sorry, I meant cells a2,a3,a4 copied to a1, c1,e1 etc