Splitting of columns in different worksheets

paddy1992 · Nov 22, 2009

I have N no. of columns in the 1st worksheet which can be named as Master or source sheet and the requirement is to split the columns as per required into the different worksheets of the same workbook.

Example: I should be able to split it like 2 or 3 columns in worksheet 2, 5 or
6 columns in worksheet 3 and so on and so forth.

Can someone please let me know how to do i do this and if possible,
can someone please provide me with the code in excel macros.

drsarao · Nov 23, 2009

paddy1992 said:
I have N no. of columns in the 1st worksheet which can be named as Master or source sheet and the requirement is to split the columns as per required into the different worksheets of the same workbook.

Example: I should be able to split it like 2 or 3 columns in worksheet 2, 5 or
6 columns in worksheet 3 and so on and so forth.

Can someone please let me know how to do i do this and if possible,
can someone please provide me with the code in excel macros.

Welcome to MrExcel.
As per your description, it seems to be a simple copy-paste operation.
You have to be more specific with which columns to copy and which worksheet to copy to. eg Column A and C to be 'split' into Sheet2 and so on. Also would you like to delete the original columns?
Alternatively you can do sample copy-paste operations with Macro Recorder switched on. And make the macro yourself.

paddy1992 · Nov 23, 2009

Hi, Sorry for not being specific about the requirement.I need to basically fulfill
the requirements as follows.

The data is entered in N number of columns in the Master sheet.
These data needs to split into column wise as follows.

For example:Sheet 1 (Master sheet): column A to D to be copied into Column A to D of sheet 2.
Column E to K to be copied into column A to G of sheet 3.
Column L to S to be copied into column A to H of sheet 4 so on.

Now the copy paste operation is not possible as the next step is to design a tool which
will take in the excel sheet filled with m aster data and the output will be the columns
split into various sheets of the same workbook.

Note: I would like to retain the original data in the master sheet.

drsarao · Nov 23, 2009

This should do it:

Sub CopyColumns()
Sheets("Master").Range("A:D").Copy Destination:=Sheets("Sheet2").Range("A1")
Sheets("Master").Range("E:K").Copy Destination:=Sheets("Sheet3").Range("A1")
Sheets("Master").Range("L:S").Copy Destination:=Sheets("Sheet4").Range("A1")
'and so on...
End Sub

Add other columns as required.

paddy1992 · Nov 23, 2009

Hi ****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5Cpdiwak%5CLocal%20Settings%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CDOCUME%7E1%5Cpdiwak%5CLocal%20Settings%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CDOCUME%7E1%5Cpdiwak%5CLocal%20Settings%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style>  </style>[FONT=&quot]drsarao,

Thank you so much for the reply. It works well. I would like some more help from you, as I am a beginner in this assignment involving VB and Excel macros.

Now, I need the flow to be generic. I need an input from the user for no. of columns in the master sheet and the no. of sheets it needs to be split into. So that the output in each sheet will be no. of columns divided by no. of sheets, so that they are equally divided and the remainder no. of columns will be included in the last sheet.

Also, in the previous code, i had to include sheet 4 n 5 manually in the excel workbook to get the output. Is there any function which will automatically create the No. of sheets as required by the user and then the splitting process is executed.

I hope this requirement is clear now. If possible can you share your contact number, so that I can communicate well with you as you are situated in India.

Thank you again.
Waiting for your reply.

[/FONT]

paddy1992 · Nov 23, 2009

Addition to the Query: The number of sheets to which the columns need to be split is 3. So all the columns in master sheet would need to be split equally into the three sheets.

Another link where I have posted this query is http://www.thecodecage.com/forumz/w...-columns-different-worksheets.html#post566647

Kindly help me with this.

Waiting for the response.

drsarao · Nov 24, 2009

This should work:

Code:

Const SHT = "Split "  'change as required
Sub CopyColumns()
On Error Resume Next
nCols = InputBox("How many columns in Master?", , "10")
If IsNumeric(nCols) Then
    nCols = CInt(nCols) 'convert to integer
Else
    MsgBox "Invalid input"
    Exit Sub
End If
If Worksheets(SHT & "1").Name = "" Then 'does SHT 1 already exist?
    For i = 1 To 3  'add Split 1 to 3 sheets
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = SHT & i
    Next i
Else
    doWhat = MsgBox("The Sheet " & SHT & "1 already EXISTS!!" & Chr(10) _
           & "Do you want to overwrite?", vbYesNo + vbCritical)
    If doWhat = vbNo Then Exit Sub
    For i = 1 To 3  'clear the sheets
        Worksheets(SHT & i).Cells.Clear
    Next i
End If
ColsPerSht = nCols \ 3
With Sheets("Master")
    LRow = .Range("A" & Rows.Count).End(xlUp).Row
    .Range(.Cells(1, 1), .Cells(LRow, ColsPerSht)).Copy Sheets(SHT & "1").Range("A1")
    .Range(.Cells(1, ColsPerSht + 1), .Cells(LRow, ColsPerSht * 2)).Copy Sheets(SHT & "2").Range("A1")
    .Range(.Cells(1, ColsPerSht * 2 + 1), .Cells(LRow, nCols)).Copy Sheets(SHT & "3").Range("A1")
End With
End Sub

This will ask for number of columns to copy from Master.
Create 3 worksheets based on name given in the Constant SHT. But ONLY if they did not exist already.
If sheet exist then ask whether to overwrite and clear if yes.
Copy a 3rd of given columns into the 3 worksheets. Remainder columns go into the last sheet.

This whole thing looks quite pointless and suspiciously like an academic project. If so, you should spend a little more time and effort in the project before becoming desperate for solution.

paddy1992 · Nov 24, 2009

Thank you drsarao for the response. Well I got the output as desired.
Anyways I would like to tell you that its no academic project, but its some requirement in
the company i am working. Anyways Thanks for the code.

drsarao · Nov 24, 2009

paddy1992 said:
Thank you drsarao for the response. Well I got the output as desired.
Anyways I would like to tell you that its no academic project, but its some requirement in
the company i am working. Anyways Thanks for the code.

You are welcome.

Splitting of columns in different worksheets

paddy1992

New Member

Excel Facts

drsarao

Well-known Member

paddy1992

New Member

drsarao

Well-known Member

paddy1992

New Member

paddy1992

New Member

drsarao

Well-known Member

paddy1992

New Member

drsarao

Well-known Member

Similar threads

Forum statistics

Share this page

Splitting of columns in different worksheets

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock