loop through column, create worksheets based on data within

**wpryan** · 12-10-2012, 08:24 PM

Hi all. I posted this question on another site, here: http://www.mrexcel.com/forum/excel-q...ml#post3338504.
I have a need to analyze some logfiles from a medical device. The device software records which types of procedures were made with it, and parameter data for each procedure. I would like to automate the process by running some code that will look through the column of data which contains the procedure type, and loop through the records to 1/ create a new worksheet based on the procedure type 2/ cut the data from the original worksheet and 3/ finally pasting it in the newly created worksheet. There are potentially dozens of rows of data with the same procedure type, in some random order. The code I have seen in other posts don't seem to address the fact that the data may be repeated, and so the code will not work if a worksheet is created from one record, and then in another row, the same procedure type appears (because you can't create a new worksheet with the same name as another). I've uploaded a file which has, on the first worksheet, the logfile data (named FlapCutLog_20121130_214240) and the rest of the worksheets are the sorted data, how I'd like the final result to look. The column which has the procedure type is AD. Thanks in advance for your help.

**TMS** · 12-10-2012, 08:47 PM

I would recommend that you first record a macro whilst you use an Advanced Filter to extract a list of unique entries in the Trajectory column.

You can then loop through this list of unique entries to a) create and name a worksheet and b) use Autofilter to select the relevant records. When you have the selected records, you can copy them and paste them to the newly created sheets.

I'm not really sure why you need to create separate sheets. I would have thought it would be easiest to leave the records where they are and use Autofilter to view each subset.

I'm not a great advocate of making multiple sheets with subsets of data as it just makes the workbook much bigger and the data more difficult to manage.

Regards, TMS

**wpryan** · 12-10-2012, 09:22 PM

Thanks for your reply. Hiker95 from MrExcel sent some code, here: http://www.mrexcel.com/forum/excel-q...ml#post3338775.
Normally I agree with you, with respect to having a "bloated" workbook. However, in this case, the next step will be to take the colon separated values from the PathParams column and separate them out, because the parameter definitions are different for all of the procedure types.

**TMS** · 12-10-2012, 09:34 PM

Ok, you're welcome. Glad you got your solution.

**mike7952** · 12-10-2012, 09:43 PM

Try this. FlapCutLog worksheet must be the activesheet when starting macro.

Please Login or Register  to view this content.

**TMS** · 12-10-2012, 09:50 PM

@Mike: Neat. But why not set ws=FlapCutLog?

Then it doesn't matter what the Active sheet is as you do everything within With ... End With.

Regards, TMS

**mike7952** · 12-10-2012, 09:55 PM

@TMShucks

Normally I would but, im assuming that FlapCutLog sheet name changes because the way its named in the workbook FlapCutLog_20121130_214240

**TMS** · 12-10-2012, 11:33 PM

You could use the code name of the sheet then ... Maybe? No, you're probably right.

If you're dependent on someone choosing the sheet, external to the code, perhaps you could do some validation on LEFT(ws.Name, 10)

Regards, TMS

**mike7952** · 12-11-2012, 12:50 AM

@TM

If you're dependent on someone choosing the sheet, external to the code, perhaps you could do some validation on LEFT(ws.Name, 10)

Yea we/OP probably could do something like that.

@wpryan
This will breakout the PathParams column

Please Login or Register  to view this content.

loop through column, create worksheets based on data within

LinkBack

Thread Tools

Rate This Thread

Display

loop through column, create worksheets based on data within

Re: loop through column, create worksheets based on data within

Re: loop through column, create worksheets based on data within

Re: loop through column, create worksheets based on data within

Re: loop through column, create worksheets based on data within

Re: loop through column, create worksheets based on data within

Re: loop through column, create worksheets based on data within

Re: loop through column, create worksheets based on data within

Re: loop through column, create worksheets based on data within

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions