copy specific columns in series and paste side by side in another

[prashantkochar263]

I have a sheet of 324 columns and 72 rows. I want to copy specific columns from sheet and paste them side by side in following manner
1,12,24,36,48.....(multiple of 12..... upto 324) side by side in new sheet1.
then 2, 13, 25, 37,49(multiple of 12+1)...upto 324

side by side in new sheet2.
then 3,14,26,38,50..(multiple of 12+2)...upto 324 side by side in new sheet3.

Please give VBA. Its urgent.
thanks​

_{^{<strike>
</strike>}}​

 

[Joe4]

Can you verify your sequences? They do not look correct:

1,12,24,36,48.....(multiple of 12..... upto 324) side by side in new sheet1.
then 2, 13, 25, 37,49(multiple of 12+1)...upto 324side by side in new sheet2.
then 3,14,26,38,50..(multiple of 12+2)...upto 324 side by side in new sheet3.

For example, if it was multiples of 12, the first one should be: 1, 13, 25, 37, 49, etc
Otherwise, the gap in your first one would be 11, not 12.

By the way, you actually do not really need VBA to do this. You can do this just using Excel formula using INDIRECT or OFFSET formulas.

If you really do want VBA, do you already have all the other worksheets set-up, or do you need VBA to add them?
After the initial data sheet, you would need 12 sheets, right?

 

[prashantkochar263]

you are right.. it's 1 ,13,25,37,49
then 2,14,26,38,50..
then 3,15,27,39,51,....
so on ..
yes i need 12 sheets.

 

[Joe4]

yes i need 12 sheets.

Are the sheets pre-created, or does the VBA code need to create them?
How are the sheets to be named?
What is the name of the main sheet holding all the data?
Are there any header rows/columns, or does the data start right in cell A1?

 

[prashantkochar263]

new sheets are to be created by VBA and names could be any thing (but in series )that's not a issue... newsheet1, newsheet2.... like that....

 

[Peter_SSs]

Assuming ..
- Original data is on a sheet names 'Data' (Easy edit in code if something else)
- You don't have formulas or formatting that need copying over. That is, you are only interested in the values.
.. then try this in a copy of your workbook.

Sub CopyDisjointColumnValues()
  Dim i As Long
  Dim aRws As Variant, aCols As Variant
  
  aRws = Evaluate("row(1:72)")
  For i = 1 To 12
    aCols = Filter(Application.Transpose(Evaluate("if(mod(row(1:324),12)=" & i Mod 12 & ",row(1:324),""x"")")), "x", False)
    Sheets.Add After:=Sheets(Sheets.Count)
    Range("A1:AA72").Value = Application.Index(Sheets("Data").Cells, aRws, aCols)
  Next i
End Sub

 

[Joe4]

Here is a dynamic solution that does not depend on knowing the exact number of rows and columns ahead of time:

Sub MyCopy()

    Dim lRow As Long
    Dim lCol As Long
    Dim s As Long
    Dim m As Long
    Dim i As Long
    Dim mws As Worksheet
    Dim cws As Worksheet
    Dim c As Long
    
    Application.ScreenUpdating = False
    
'   Capture current worksheet (with all the data)
    Set mws = ActiveSheet
    
'   Find last row and column with data on main sheet (using first row/column)
    lRow = Cells(Rows.Count, "A").End(xlUp).Row
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    
'   See if number of columns is an exact multiple of 12
    m = Round(lCol / 12, 0)
    If (m * 12) <> lCol Then
        MsgBox "The number of columns is not a multiple of 12", vbOKOnly, "ERROR!"
        Exit Sub
    End If
    
'   Insert 12 sheets
    For s = 1 To 12
'       Insert new sheet
        Sheets.Add After:=Sheets(Sheets.Count)
'       Name sheet
        ActiveSheet.Name = "NSheet" & s
'       Capture sheet
        Set cws = ActiveSheet
'       Copy data
        For i = 1 To m
            c = 12 * (i - 1) + s
            mws.Activate
            mws.Range(Cells(1, c), Cells(lRow, c)).Copy cws.Cells(1, i)
        Next i
    Next s
    
    Application.ScreenUpdating = True
    
    MsgBox "Process complete!"
    
End Sub

 

[prashantkochar263]

its just creating the new sheets not pasting the data

 

[Joe4]

its just creating the new sheets not pasting the data

Who are you replying to, Peter or me?
What range exactly is your initial data in?
If the data all hard-coded or formulas?