Split data into new sheets

I have a (very) long list, sorted by account code. I would like to write a 
macro that splits the list into separate sheets in the workbook, with a 
separate sheet for each account code.

Ideally, I would also like to rename each sheet to show which account code 
the sheet contains.

I have no idea, though, where to start. Any ideas? Thanks in advance.
0
Bernard1 (17)
1/4/2006 9:00:02 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
849 Views

Similar Articles

[PageSpeed] 15

Hi bernard

How many different accounts are in the list (more or less than 250)???

below some code that I use to split files by account numbers where the
user has to select a cell within the column that contains the account
number...

Hope this helps

Regards

Papparotti

Dim bSh As Worksheet    'original sheet -> baseSheet
Dim AccCol As Integer   'column containing the account number
Dim maxRows As Integer
Dim maxCols As Integer
Dim i As Integer
Dim tmpName As String
Dim tmpName2 As String

    Application.ScreenUpdating = False

    AccCol = ActiveCell.Column

    Set bSh = ActiveSheet
    maxRows = bSh.UsedRange.Rows.Count - 1
    maxCols = bSh.UsedRange.Columns.Count

    For i = maxRows To 8 Step -1      'The copy process starts with the
last line

        tmpName = Cells(i, AccCol).Text
        tmpName2 = Cells(i, NameCol).Text

        If Not findSheet(tmpName) Then    'The code for findSheet is
below!

            Worksheets.Add
after:=Worksheets(ActiveWorkbook.Worksheets.Count)
            ActiveSheet.Name = tmpName
            ActiveSheet.Cells.Interior.Color = RGB(255, 255, 255)
            'The following lines copy header information to the newly
created sheet
            bSh.Activate
            bSh.Range(Cells(1, 1), Cells(7, maxCols + 1)).Copy  'AMEND
TO FIT FILE
            Worksheets(tmpName).Activate
            ActiveSheet.Cells(1, 1).PasteSpecial (xlAll)
           'end of header copying
        End If

        bSh.Activate

        Cells(i, 2).EntireRow.Select
        Selection.Copy
        Worksheets(tmpName).Activate
        Rows("8:8").Select          'you'll have to amend this
according to your headers
        Selection.Insert Shift:=xlDown

        bSh.Activate
    Next i


    Application.ScreenUpdating = True

End Sub

Private Function findSheet(ByVal sName As String) As Boolean
Dim s As Variant
    For Each s In ActiveWorkbook.Worksheets
        If s.Name = sName Then
            findSheet = True
            Exit Function
        End If
    Next s
    findSheet = False
End Function

0
1/4/2006 10:57:38 AM
Thanks for this - I think I can see what this is doing (I am still at the 
very early stages with VB!) but I can't quite get it to work. For example, 
Visual Basic has hilgihted the following line in red: 
'after:=Worksheets(ActiveWorkbook.Worksheets.Count)'. Any ideas?

Don't know if it helps at all, but my spreadsheet has 3 columns - Code, 
Description and Amount and the header row is in line 4.

Thanks again.

"Papparotti" wrote:

> Hi bernard
> 
> How many different accounts are in the list (more or less than 250)???
> 
> below some code that I use to split files by account numbers where the
> user has to select a cell within the column that contains the account
> number...
> 
> Hope this helps
> 
> Regards
> 
> Papparotti
> 
> Dim bSh As Worksheet    'original sheet -> baseSheet
> Dim AccCol As Integer   'column containing the account number
> Dim maxRows As Integer
> Dim maxCols As Integer
> Dim i As Integer
> Dim tmpName As String
> Dim tmpName2 As String
> 
>     Application.ScreenUpdating = False
> 
>     AccCol = ActiveCell.Column
> 
>     Set bSh = ActiveSheet
>     maxRows = bSh.UsedRange.Rows.Count - 1
>     maxCols = bSh.UsedRange.Columns.Count
> 
>     For i = maxRows To 8 Step -1      'The copy process starts with the
> last line
> 
>         tmpName = Cells(i, AccCol).Text
>         tmpName2 = Cells(i, NameCol).Text
> 
>         If Not findSheet(tmpName) Then    'The code for findSheet is
> below!
> 
>             Worksheets.Add
> after:=Worksheets(ActiveWorkbook.Worksheets.Count)
>             ActiveSheet.Name = tmpName
>             ActiveSheet.Cells.Interior.Color = RGB(255, 255, 255)
>             'The following lines copy header information to the newly
> created sheet
>             bSh.Activate
>             bSh.Range(Cells(1, 1), Cells(7, maxCols + 1)).Copy  'AMEND
> TO FIT FILE
>             Worksheets(tmpName).Activate
>             ActiveSheet.Cells(1, 1).PasteSpecial (xlAll)
>            'end of header copying
>         End If
> 
>         bSh.Activate
> 
>         Cells(i, 2).EntireRow.Select
>         Selection.Copy
>         Worksheets(tmpName).Activate
>         Rows("8:8").Select          'you'll have to amend this
> according to your headers
>         Selection.Insert Shift:=xlDown
> 
>         bSh.Activate
>     Next i
> 
> 
>     Application.ScreenUpdating = True
> 
> End Sub
> 
> Private Function findSheet(ByVal sName As String) As Boolean
> Dim s As Variant
>     For Each s In ActiveWorkbook.Worksheets
>         If s.Name = sName Then
>             findSheet = True
>             Exit Function
>         End If
>     Next s
>     findSheet = False
> End Function
> 
> 
0
Bernard1 (17)
1/4/2006 11:38:02 AM
Hi bernard

Look here
http://www.rondebruin.nl/copy5.htm



-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"bernard" <bernard@discussions.microsoft.com> wrote in message news:EE41F948-331A-4803-AE1F-BAAFE99E8D00@microsoft.com...
>I have a (very) long list, sorted by account code. I would like to write a
> macro that splits the list into separate sheets in the workbook, with a
> separate sheet for each account code.
>
> Ideally, I would also like to rename each sheet to show which account code
> the sheet contains.
>
> I have no idea, though, where to start. Any ideas? Thanks in advance. 


0
rondebruin (3789)
1/4/2006 1:08:24 PM
Ron

It works a treat! Thanks!

Bernard

"Ron de Bruin" wrote:

> Hi bernard
> 
> Look here
> http://www.rondebruin.nl/copy5.htm
> 
> 
> 
> -- 
> Regards Ron de Bruin
> http://www.rondebruin.nl
> 
> 
> "bernard" <bernard@discussions.microsoft.com> wrote in message news:EE41F948-331A-4803-AE1F-BAAFE99E8D00@microsoft.com...
> >I have a (very) long list, sorted by account code. I would like to write a
> > macro that splits the list into separate sheets in the workbook, with a
> > separate sheet for each account code.
> >
> > Ideally, I would also like to rename each sheet to show which account code
> > the sheet contains.
> >
> > I have no idea, though, where to start. Any ideas? Thanks in advance. 
> 
> 
> 
0
Bernard1 (17)
1/4/2006 4:45:02 PM
Reply:

Similar Artilces:

Data Table
Hello, I have a graph measuring two points of data. But I just want to see one of them in the Data Table and both in the graph. How do I visually get rid of one of the data in the data table but keep it visually in the graph? Thanks in advance. I don't think you can. I think you'd have to just put the "data table" in manually, because, as far as I know, an actual data table is what it is and doesn't give you much room for customization. -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2010? http://www.echosvoice.com/2010.htm Fi...

Getting data from another excel file
Does anyone know how to create an Excel spreadsheet that will rea another Excel spreadsheet and will certain data in the new one? For example: File 1: A1: Name B1: Amount C1: Chargeoff D1: Current Balace A2: Joe Smith B2: 141.28 C2: 5.00 D2: 136.28 A3: Jane Doe B3: 100.00 C3: 100.00 D3: 0.00 A4: Eric Yellow B4: 1.00 C4: 0.00 D4: 1.00 End of File 1 File 2: A1: Name B1: Amount C1: Chargeoff D1: Current Balace A2: Joe Smith B2: 141.28 C2: 5.00 D2: 136.28 A3: Eric Yellow B3: 1.00 C3: 0.00 D3: 1.00 End of File 2 Thanks, Joh -- Union70 Union70 <Union70.1nuy5n@news.officefrustration.co...

Why Color Flled Data Series on Line Chart Slants
I have a modified line chart showing cumulative product cost over a twelve month period. The x-axis shows each month, and the data line moves right and up increasing with the cumulative total for the months. Right now, the first six months are actual and second six months are projected. Each side is color filled (using Format Data Series Fill Effects) and there is a vertical line showing the separation. For some reason the color fill and vertical line are slanting, indicating different month points between the data line and the x axis. Any idea what might be causing this and/or how...

Paste data under existing data
I have a simple form where I enter data into columns. I need to copy this data and transpose it and paste it under existing data in a spreadsheet. I can't get my macro to add this copied data in the next blank row under existing data - it keeps pasting to the same row and overwriting data. I would appreciate any assistance with this. Pam Since you didn't post your macro, maybe .offset(1) or row+1 would help -- Don Guillett SalesAid Software donaldb@281.com "Pam Brenny" <anonymous@discussions.microsoft.com> wrote in message news:043a01c3ce59$bc1bb710$a101280a...

How do I re-position chart data labels?
When creating a 3-D Clustered Bar Chart using the wizard, data labels are positioned at the end of the bars. The 3-D bar shadow makes it difficult to see any values. Rather than doing so manually, is there a quicker way to re-position all data labels to the centre of their bars? PS. A workaround for this problem when using only one series is to select 'Stacked Bar', but this is of no use with >1 series. Thanks. Alistair - Suggestion #1: Use 2D charts. The 3D effects can distort the reader's interpretation of the data, beyond your problem with the label positions. Y...

Outlook 2002: Error -2147024891 Collaboration Data Objects E_ACCESSDENIED (80070005)
When I tried to debug a program written using VB, which intend to send an email attachment, I get the following error message. Error -2147024891 : [Collaboration Data Objects - [ E_ACCESSDENIED (80070005)]] The email attachment is getting saved in the Outbox of Microsoft Outlook thus not allowing the user to select the recipient list from TO, CC & BCC lists. Some parts of the codes are stated below for your information ........... .............. Set objSession = CreateObject("Mapi.Session") ........... .............. objSession.Logon "MS Exchange Settings&qu...

import external data problem
I'm stumped. I have a file I update on a weekly basis by adding a sheet and importing external data into the newly created sheet. The source data is a .xls file. The data is contiguous and the file is roughly 100kb. The target file, holds 13 of the source files and also has a couple extra sheets for summary info. The file size with all of this is approx 2 Megs. Now I'm trying to make a similar tool for a new application. Each source file is 2.5 Megs (.xls) and the destination file is roughly 30 Megs. The data in the source file(s) is not contiguous. There is a partial row an...

Update data function across 2 workbooks
My question is, is it possible for data to be ported over 2 workbook just by clicking on the data on the first one via external links? For example, I have 2 workbooks - -catalogue.xls- and -order.xls-. Wha i want is for my customers to be able to look at the items available i -catalogue.xls-, and when they see an item that they want to order, al they have to do is to click on it, and the data in that highlighted ro will be ported over to -order.xls-. Is such a thing even possible? If it is, how shoud I go abou constructing such a spreadsheet? What values shoudl I define, etc etc? thank you...

Pivot table/chart
Is there a way to categorize data by named ranges of the column headings rather than individual column headings? Excell will not allow me to group the data. I need to be able to pivot between certain ranges rather than eliminate or add one column at a time. Or perhaps someone can suggest a different way to do it. I need to create 1 chart to pivot between the first 5, 7, 9 and 11 data columns. ...

Table-data transformation
I would like to take data from 3 different columns of data and recombine to a different format in another column. The original data is a series of numerical and text codes. first column, second column, third column 12,30,1245CF I would like the new column to contain the data in this form: 12-30-1245CF Can this transformation be automated? Can I set up a form to just collect the individual numbers and make the table transform them to this new form and unique identifier (key) ??? Or perhaps the form transforms and puts the data in the table. I need the data in both formats and don't wan...

How to randomly split a whole dataset into two sub-dataset?
Hi, At your possible convenience, might anyone please kindly answer my question? Thank you very much. How to "RANDOMLY" split the whole data set (n=2000) into two sub dataset (n=1000; n=1000) in SPSS or Excel? Thank you very much. Please take care Caroline zencaroline <zencaroline@gmail.com> writes: > How to "RANDOMLY" split the whole data set (n=2000) into two sub > dataset (n=1000; n=1000) in SPSS or Excel? Create a new variable whose value is randomly distributed. Sort the data on this variable. Take the first 1000 cases ...

Split tasks a splitting headache
Inherited another Project IMS. I am noticing right off that the previous scheduler has been starting tasks without their FS preds completing. Yeah, it does show up as a spit bar in the gantt chart. But, does anyone know a way to filter out just task that started before predecessor completion? I'm dealing with several thousand lines. Thanks. -- trailerpup ------------------------------------------------------------------------ trailerpup's Profile: http://forums.techarena.in/members/116596.htm View this thread: http://forums.techarena.in/microsoft-project/1290853.htm ...

How do I change from all caps to regular for existing data?
I received a spreadsheet from a client, I need to change the data from all caps to regular, I have tried applying a different style, clearing formats .....any suggestions? It depends what you mean by "regular", but your could try the =Proper and =Lower functions. Either that, or create a macro to change the cells to your preference. -- Regards, Fred Please reply to newsgroup, not e-mail "CL Johnson" <CL Johnson@discussions.microsoft.com> wrote in message news:434CF63F-11D0-4E28-A234-4C5F20395F22@microsoft.com... >I received a spreadsheet from a client, I ...

sheet naming
I have a macro that copies a sheet then names it. It goes as follows: Sub Createsheet() Sheets("Sheet1").Select Sheets("Sheet1").Copy Before:=Sheets(1) Sheets("Sheet1 (2)").Select Sheets("Sheet1 (2)").Name = "datasheet1" Range("C4").Select End Sub Is there a way to make it so that when you run the macro again and create a new sheet and it sees that datasheet1 already exists it will name the next sheet datasheet2? And then when you run it again it sees that datasheet1 and datasheet2 exists and it names the next...

New-WSManInstance
I'm trying to use Power Shell to instantiate a new WSMAN object. In this particular case, I was trying to instantiate an AMT_Hdr8021Filter. I'm able to use WinRM to create this object from C#, but not from powershell... When I try to use New-WSManInstance, I get an "Access Denied" error. When I try to use the Invoke-WSManAction method, I get a "No Route" error.. I looked at network traces, and found the XML sent on the wire from powershell to be different than from WinRM in C#. When I use New-WSManInstance, nothing is ever sent on the wire, it just...

Unwanted replicated split screen
I know I could re-install excell but before I do, is there anyone here who can help me disable a setting that I have no idea how it came about. I can only describe it as 2 identical execl grids on the same worksheet. Accompanying image will show you. Help. Thanks. +-------------------------------------------------------------------+ |Filename: excel_display_duplication.GIF | |Download: http://www.excelforum.com/attachment.php?postid=3855 | +-------------------------------------------------------------------+ -- BoyLeroy -----------------------------------...

how to copy cells data in every 7th row?
Hi, I have a column of data from A2 to A65000 and I am only interested in every 7th data point (e.g. A2, A9, A16....). Is there a way to get those selected points and store them in the next column (B) consecutviely (b1, b2, b3, b4, ...)? Thanks, Jeff Jeff, Sub Get7() Dim outrng As Range Dim i As Long Set outrng = Range("b1") Lastrow=cells(rows.count,1).end(xlup).row For i = 2 To lastrow Step 7 outrng = Cells(i, 1) Set outrng = outrng.Offset(1, 0) Next i End Sub HTH "Jeff" wrote: > Hi, > I have a column of data from A2 to A65000 and I am only interes...

New drop down menu
A long time ago I had some code to add a drop down menu to Excel. Can some kind soul please direct me to a location where I can recapture that code.\Thanks See http://www.contextures.com/xlDataVal01.html -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "pcor" <pcor@discussions.microsoft.com> wrote in message news:1C8CEE9C-1AE1-406D-9E5F-2AC48048BED6@microsoft.com... >A long time ago I had some code to add a drop down menu to Excel. Can some > kind soul please direct me to a location where I can recapture that > code.\...

How do you ignore a data point?
Say my data set is 10, 15, 90, 12 I want to plot all the values but not connect/interpolate 90 as it is an outlier. How do I do this, I'm sure it's easy. Many Thanks Julie Make two series, one with the three good points, one with the outlier. Connect the points in the first series. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "ju1eshart" <ju1eshart@discussions.microsoft.com> wrote in message news:B828F236-594F-4749-B406-5388B957EC5F@microsoft.com... > Say...

XL2007 Pivot Charts: Path to data in another workbook keeps chang
I have 2 workbooks in the same folder (My Documents). 1 has several worksheets with raw data (Row 1 = headings, then data) 1 has several worksheets with pivot tables and pivot charts that are based on the data in the first worksheet. Every time I open the workbook containing the pivot tables and charts, the path to the data sources for each pivot chart and table has changed from My Documents to: C:\Documents and Settings\username\Application Data\Microsoft\Excel Even if I edit the data source for each chart/table, and then save the workbook, when I open it again, the paths to the dat...

Math functions on non number data?
is there a way to convert data that is in fraction format to number format to be able to perform math functions without changing the original data type? For instance, change 23 1/2 * 34 3/4 to 23.5*34.75 to come up with 816.625. The result does not need to be in fractional format. thanks for any assistance, Dave DDP I presume you have all of that multiplication in an single cell (otherwise you would simply be able to multiply whatever two cells contain them)? If so, try this formula... =LEFT(A1,FIND("*",A1)-1)*MID(A1,FIND("*",A1)+1,99) -- Rick (MVP - Excel) &qu...

Aquiring phone data from an account from a different form.
When registering a new case I would like to aquire the main phone nr. of the customer automaticaly in a custom field on the case form after I have defined the customer name. Is this possible? ...

Can't find my Front End Folder after database split
I just split my database base to allow for ease of use for multiple users. Ererything, it seemed, went smoothly until i try to find the fron-end folder. It is nowhere to be found. Should it be label ****_fe.mbd like the back end? Please help. Thaanks. It will be called that, unless you typed something else in the save as dialog. I thought it ended up in the same folder as the backend after splitting. Easiest way is to go back to the original and split it again, this time take note of where you save it before you press the button. Jeanette Cunningham "Joe" <Joe@discussi...

Display Data On Split Form
Not sure how to ask this but here goes. In AC 2007 I have a split form that I want to display a value that will show regardless of the row I select in the datasheet. This is the number of vacation days one will have through-out the year and of course it changes as vacations days get used up. Any help will be appreciated, James "JamesJ" <jjy@darwin_roadrunner.com> wrote in message news:6A4DFFD9-3302-4A10-B51A-153373D266FD@microsoft.com... > Not sure how to ask this but here goes. > In AC 2007 I have a split form that I want to display a value > that will show regardl...

Query of External Data
I'm using a ODBC connection to query a oracle database. If I refresh a query and email the file to the intended recipients, I can limit what they can see by changing the query to include/exclude certaing markets. Is there a way that I can set up the query so the recipient cannot edit the query and have access to all the data...? Or is there a way to disconnect the query from the excel file, this way the query could never be refreshed to the database. We could email them the file daily, but they wouldn't be able to refresh or change the query(to view markets they normally ...