Excel - Separating data into worksheets based on a column value

[KiwiRiccardo]

There might be another way of going about this but I am having a mental block.

I have a worksheet that contains data for five teams. The team is indicated by a column in the worksheet.

What I need to do is sepatate out and group each team data onto individual worksheets within the workbook.

I need this to be able to be included within a macro.

Obviously this could be done with a filter by team name but I am hoping that there will be a smarter way of doing this and there will not always be data for every team so I believe that this will cause a problem when running the macro.

Grateful for any advice.

Cheers

KiwiRiccardo

 

[rdodge]

To some extent, I agree with Ken, such as VBA isn't ran automatically unless it's tied to some event like the change event. But here's why I stated as I did:

If the results are formula based and the Application Calculation mode is set to Automatic:

Each and every time the user makes any sort of a change to any open workbook within the instance of Excel that has the Auto-Calc mode, calculation will take place after each and every change. Normally, you may think, 2 or 3 seconds isn't very much, but if you are doing data entry directly in the worksheet, let's say 100 total cell entry, that 2 seconds per entry has just become a 200 second calculation time, which is 3 minutes and 20 seconds.

Of course, at that point, you could either change the Calculation mode to Manual or use VBA resolution, but either way, still similar type results.

Now when you do that same data entry, it may take only 1 minute and 30 seconds, which then you either calculate the formula based, or run the VBA code.

Issues that I have with formula based.

F9 function key doesn't always calculate everything in all open workbooks within that instance of Excel like it is suppose to, thus has proven to be unreliable. Only way to get around this is to use the Shift-F9 on a worksheet by worksheet basis. Would you like to do that by hand, or would you rather let code handle that?

Most of your Excel users are your standard non-power users. If they are going to be entering the information into the workbook, they may call back and ask why isn't it working or why are their other workbooks not calculating like they are expecting them to, so from a practical stand point of view, this only adds to the reason why I don't use complex formula based results when expecting other more common Excel users to be working with the workbook.

I do have s lot of things going on in Excel, of which currently, I'm in the process of seeing about getting that information switched over from Excel to Access given the various complexities involved, and Excel is not meant to handle the type of processes and data storage that I been having it do. Initially, we had a manufacturing DB system, but given it's various issues and it's high cost to renew, we just didn't renew, and I had to create at a bare minimal, a temp program that would capture all of the information.

Yes, even my production reports are still in Excel. Main thing about it though, I have it all setup via code to be ran on the click of a command toolbar button. Granted, this is pushing the limitations of Excel with regards to how it gathers data (through user form and stores on the local workbooks which are saved on the network file server), saves and processes the information (a separate file which is ran on my system at the bare minimal, once a week). Each individual machine center workbook retains it's records for a period of 45 days, and my summary files archives the most detail level of each type of mode and reason code by day and shift (each row contains the date, shift, and how much total time for that shift on that date in setup, setup problem, setup idle, run, run problem, run idle, and how much total time for each of the different reasons why for the problem/idle times.

Something like this is really more so meant in a DB program, but at the time, I only had 3 weeks to come up with a temp program, got it into full test mode within 2 weeks, and I knew I didn't have time to learn all of the objects in VBA coding for Access given the time critical issue. Tell you the truth, I did that cause I was not about to get stuck doing nothing but validating data that they provide on paper and data entry all day long. After my previous experience with the extensive data entry that I had done working at the IRS, I don't want to go back to those days and possibly have to deal with CTS (Carpal Tunnel Syndrome). After all, not only do I have to use common sense, but I also have to listen to my body. The other reason why I did it, it's been proven over and over that live time reporting is a very good majority of the time much more accurate than keeping paper logs for 2 reasons, one it knocks out a lot of the manual tasks that would have to be done manually when doing paper logs (I.e. calculations or recording of start and end times), and 2, it also reduces the human error factor rather significantly.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[xlhelp]

Off-track a little.... but pukka is a hindi (Indian) word for Perfect.

http://www.geniusmedia.ca

 

[KenWright]

Hi Ron - just an aside. As you say, F9 is unpredictable, and SHIFT+F9 works but has to be done on each sheet. CTRL+ALT+F9 will force a Global recalculation, and for all intents and purposes should have the same effect that was desired from F9 alone.

Regards
Ken................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[rdodge]

But then, if I recall correctly, that only works in Excel 2000 and later.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[KenWright]

Ron, you mean there are versions prior to that????

Seriously though, my apologies as I hadn't actually realised that, so thanks!!

Regards
Ken................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[KiwiRiccardo]

Thank you all for your excellent input.

Cheers

Kiwi

 

[Adams]

I am trying to run this Function that I found in this forum. Could you tell me how to run this......
Thanks

Function SheetExists(SheetId As Variant) As Boolean

' This function checks whether a sheet (can be a worksheet,
' chart sheet, dialog sheet, etc.) exists, and returns
' True if it exists, False otherwise. SheetId can be either
' a sheet name string or an integer number. For example:

' If SheetExists(3) Then Sheets(3).Delete

' deletes the third worksheet in the workbook, if it exists.
' Similarly,

' If SheetExists("Annual Budget") Then Sheets("Annual Budget").Delete

' deletes the sheet named "Annual Budget", if it exists.



Dim Sh As Object
On Error GoTo NoSuch
Set Sh = Sheets(SheetId)
SheetExists = True
Exit Function
NoSuch:
If Err = 9 Then SheetExists = False Else Stop

End Function

 

[SkipVought]

Adams

Please post your question in a NEW THREAD.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884