Splitting Macro

[Bobfred]

Hi I am looking for advice on how to create the following macro.

I have a spreadsheet of data that has columns A-Q.

The column headings are always the same name. I am wanting the Macro to split the data in column G and create new worksheets that displays that data. The number of Rows of data will always be different and is not a set amount

Column G usually has 4 different things in it. Red, Yellow, Blue, Black.

So after it is split I would want to see the following worksheets. Sheet1(Containg all data) Red(Contain all Red lines of data), Yellow(Contain all Yellow lines of data), Blue(Contain all Blue lines of data), Black(Contain all Red lines of data),

Ideally I would like to be able to split the data in other column headings. So whatever cell I select for example it will split it and create worksheets for those values. but if this is not possible then column G is fine but it would be good if I could do this on any column header and it would split by the different values in that column not including the header title

 

[hiker95]

Bobfred,

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

To start off, and, so that we can get it right on the first try:

Can you post a screenshot of the actual raw data worksheet?

And, can you post four screenshots of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot (NOT a graphic, or, picture, or, PNG file, or, flat text) try one of the following:

MrExcel HTML Maker
If you do not know how to install and how to use HTML Mr.Excel Maker
https://www.youtube.com/watch?v=JycvgGppxt0&feature=youtu.be

Excel Jeanie
Download

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


The following is a free site:

Or, you can upload your workbook to (the BLUE link-->) Box Net ,

sensitive data changed

mark the workbook for sharing

and provide us with a link to your workbook.

 

[Bobfred]

Excel 2012

	A	B	C	D	E	F	G	H

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]Date[/TD]
[TD="align: right"]Time[/TD]
[TD="align: right"]Yes/No[/TD]
[TD="align: right"]Price[/TD]
[TD="align: right"]Colour[/TD]
[TD="align: right"]Amount[/TD]
[TD="align: right"]Number[/TD]
[TD="align: right"]Day[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]16/09/2014[/TD]
[TD="align: right"]10:18:54[/TD]
[TD="align: right"]No[/TD]
[TD="align: right"]23415[/TD]
[TD="align: right"]Red[/TD]
[TD="align: right"]41995[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]Monday[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]16/09/2014[/TD]
[TD="align: right"]10:18:53[/TD]
[TD="align: right"]No[/TD]
[TD="align: right"]23410[/TD]
[TD="align: right"]Yellow[/TD]
[TD="align: right"]20967[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]Monday[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]16/09/2014[/TD]
[TD="align: right"]10:18:54[/TD]
[TD="align: right"]No[/TD]
[TD="align: right"]23410[/TD]
[TD="align: right"]Yellow[/TD]
[TD="align: right"]20967[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]Tuesday[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]16/09/2014[/TD]
[TD="align: right"]10:18:55[/TD]
[TD="align: right"]No[/TD]
[TD="align: right"]23415[/TD]
[TD="align: right"]Red[/TD]
[TD="align: right"]27563[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]Wednesday[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]16/09/2014[/TD]
[TD="align: right"]10:19:02[/TD]
[TD="align: right"]No[/TD]
[TD="align: right"]23410[/TD]
[TD="align: right"]Yellow[/TD]
[TD="align: right"]26952[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]Monday[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]20/07/2015[/TD]
[TD="align: right"]13:25:17[/TD]
[TD="align: right"]No[/TD]
[TD="align: right"]23415[/TD]
[TD="align: right"]Red[/TD]
[TD="align: right"]36031[/TD]
[TD="align: right"]2.2[/TD]
[TD="align: right"]Monday[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]20/07/2015[/TD]
[TD="align: right"]13:25:17[/TD]
[TD="align: right"]No[/TD]
[TD="align: right"]23430[/TD]
[TD="align: right"]Blue[/TD]
[TD="align: right"]19281[/TD]
[TD="align: right"]2.4[/TD]
[TD="align: right"]Tuesday[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]20/07/2015[/TD]
[TD="align: right"]13:25:18[/TD]
[TD="align: right"]No[/TD]
[TD="align: right"]23410[/TD]
[TD="align: right"]Yellow[/TD]
[TD="align: right"]20967[/TD]
[TD="align: right"]2.6[/TD]
[TD="align: right"]Wednesday[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]20/07/2015[/TD]
[TD="align: right"]13:25:19[/TD]
[TD="align: right"]No[/TD]
[TD="align: right"]23415[/TD]
[TD="align: right"]Red[/TD]
[TD="align: right"]36031[/TD]
[TD="align: right"]2.8[/TD]
[TD="align: right"]Monday[/TD]

</tbody>

All​

So this is an example of a data table am am using. I want the Macro to be flexible. So if Select the Colour cell(E1). I want it to create 3 worksheets Red, Yellow and Blue and contain the rows of data for that colour.

I then may want to use it on the Day cell(H1) It would then create separate worksheets for Monday, Tuesday and Wednesday.

So whatever Column header I select I want it to create a worksheet that splits up the values of that column

............
This is would be the example once run. I am currently on the Blue worksheet

Excel 2012

	A	B	C	D	E	F	G	H
Date	Time	Yes/No	Price	Colour	Amount	Number	Day
No	Blue	Tuesday
No	Blue	Tuesday
No	Blue	Wednesday
No	Blue	Wednesday
No	Blue	Monday
No	Blue	Wednesday
No	Blue	Wednesday
No	Blue	Monday
No	Blue	Monday

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]########[/TD]
[TD="align: right"]0.559225[/TD]

[TD="align: right"]23430[/TD]

[TD="align: right"]19281[/TD]
[TD="align: right"]2.4[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]########[/TD]
[TD="align: right"]0.559271[/TD]

[TD="align: right"]23433[/TD]

[TD="align: right"]19281[/TD]
[TD="align: right"]3.2[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]########[/TD]
[TD="align: right"]0.55934[/TD]

[TD="align: right"]23430[/TD]

[TD="align: right"]19281[/TD]
[TD="align: right"]3.4[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]########[/TD]
[TD="align: right"]0.559387[/TD]

[TD="align: right"]23433[/TD]

[TD="align: right"]19281[/TD]
[TD="align: right"]4.2[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]########[/TD]
[TD="align: right"]0.559456[/TD]

[TD="align: right"]23430[/TD]

[TD="align: right"]19281[/TD]
[TD="align: right"]4.6[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]########[/TD]
[TD="align: right"]0.559502[/TD]

[TD="align: right"]23433[/TD]

[TD="align: right"]19281[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]########[/TD]
[TD="align: right"]0.559572[/TD]

[TD="align: right"]23430[/TD]

[TD="align: right"]19281[/TD]
[TD="align: right"]5.8[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]########[/TD]
[TD="align: right"]0.559618[/TD]

[TD="align: right"]23433[/TD]

[TD="align: right"]19281[/TD]
[TD="align: right"]6.2[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]########[/TD]
[TD="align: right"]0.559688[/TD]

[TD="align: right"]23430[/TD]

[TD="align: right"]19282[/TD]
[TD="align: right"]6.8[/TD]

</tbody>

Blue​

 

[hiker95]

So whatever Column header I select I want it to create a worksheet that splits up the values of that column

Bobfred,

1. Your selection will be for one of ALL the tiles (Date, Time, Yes/No , Price, Colour, Amount, Number, and, Day, in row 1?

If the above is correct, then:

2. What will be the worksheet name text string used for Date?

3. What will be the worksheet name text string used for Time?

4. What will be the worksheet name text string used for Amount?

 

[Rick Rothstein]

Bobfred,

1. Your selection will be for one of ALL the tiles (Date, Time, Yes/No , Price, Colour, Amount, Number, and, Day, in row 1?

If the above is correct, then:

2. What will be the worksheet name text string used for Date?

3. What will be the worksheet name text string used for Time?

4. What will be the worksheet name text string used for Amount?

To add to hiker95's questions... what will the worksheet name text strings used for the other 9 columns you say you have that you did not show us in your example?

 

[Bobfred]

Hi guys. Sorry that I'm not explaining myself very well here.

I don't want to split all the columns at once. What I want is to have a macro that when you select a column heading say Date(a1) it will look at all the rows of data. Say there are 5 entries for 01/08/15, 3 for 05/08/15 ans 1 for the 17/08/15.

The macro would create new worksheets named after the 3 dates so 01/08/15, 05/08/15 and 17/08/15. The whole data from cell a to h would then be copied onto the worksheet with the relevant date.

So the actual heading names wouldn't matter I would want the macro to do it in any work sheet.

for example if you had a worksheet of a sports league that contained 10 teams all their results for a full season. If you had a column header for Team and I used the macro on that header then I would want it to create 10 new worksheets each with the teams name and bring the scores for that team into the correct worksheet

 

[Bobfred]

I do currently have a macro that I am using to do this. However this was created by someone else who has password protected it and since left the company so I am unable to find out the code in the background. I am wanting it to combine with another macro.

if someone could explain to me how I can show this file on here for people to download and see what I mean then I am willing to do that if they can work out the code in the background

 

[hiker95]

if someone could explain to me how I can show this file on here for people to download and see what I mean

Bobfred,

The following is a free site:

You can upload your workbook to (the BLUE link-->) Box Net ,

sensitive data changed

mark the workbook for sharing

and provide us with a link to your workbook.

 

[kalak]

Hi Bobfred,

Here's a VBA that splits up any column into worksheets.
Currently setup to run off column 5 (red line in code).
If run on your post#3 data, does it give something like you want?

Sub splitz()

Dim cl As Long
Dim lr&, lc&, s&, i&
Dim hdr, q As String, d As Object, sh As Worksheet

cl = 5    'change this to whatever you like

Application.ScreenUpdating = False
Set d = CreateObject("scripting.dictionary")
For Each sh In Worksheets
    d(sh.Name) = 1
Next sh
lr = Cells.Find("*", , , , xlByRows, xlPrevious).Row
lc = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
s = 2

Set ash = ActiveSheet
With Sheets.Add(after:=ash)
    ash.Cells(1).Resize(lr, lc).Copy .Cells(1)
    hdr = .Cells(1).Resize(, lc)
    .Cells(1).Resize(lr, lc).Sort .Cells(cl), Header:=xlYes
    a = .Cells(cl).Resize(lr + 1)
    For i = 2 To lr
        If a(i, 1) <> a(i + 1, 1) Then
            q = CStr(a(i, 1))
            If Not d(q) = 1 Then
                Sheets.Add(after:=Sheets(Sheets.Count)).Name = q
            Else
                Sheets(q).UsedRange.ClearContents
            End If
            .Cells(s, 1).Resize(i - s + 1, lc).Copy Sheets(q).Cells(2, 1)
            s = i + 1
            Sheets(q).Cells(1).Resize(, lc) = hdr
        End If
    Next i
Application.DisplayAlerts = False
    .Delete
Application.DisplayAlerts = True
End With
ash.Activate
Application.ScreenUpdating = True

End Sub

 

[Bobfred]

Hi kalak

Yeah that does work but is there any way we can improve it further.

The current macro I use is saved as an .xla file.

So regardless of what file I am in I can just open the add in.

I then select the column header I want e.g colour. So I have selected E1

I then go to run the macro. Nothing displays in the Macro list and the run box is greyed out. However when I type in 'splitme' the run box becomes live.

I then click run and it produces the same results as you have done. I have the original worksheet and then worksheets for blue, yellow, black ect..

So is there a way instead of having 'cl = 5 ' and having to change every time I want to do a different column that instead can it somehow be done to say the cell you have currently selected??