Using macros to split an Excel worksheet into useful chunks??

[ccat]

Dear all,

Dear all,

This is my first time working with macros in Excel. Please help!

I’m trying to build a macro that will split a worksheet with approx. 9500 rows into 28 separate worksheets based on relevant sections. My pseudo-code would be something like this:

‘Assuming that each section starts with a word, “Actual”, in column B.

Find the first cell with the name “Actual”. ActiveCell.

For each cell under the ActiveCell, copy and paste whole row into “Sheet b” ‘including blanks

‘Designate b as 1 for (sheet ‘one to start off with, then increment upon each loop)

Loop until next cell containing “Actual”
Increment b by 1 to copy the next section into a new worksheet.

Continue outer loop until b=28

Hmm…I know it would work logical like this, but I’m very unfamiliar with the syntax. Any help would be much appreciated.

Thanks in Advace,
Cccat

 

[DaleWatson123321]

Cccat,

From your description, it appears that Excel's "database extraction" function could well be your BEST option.

This allows you to specify "criteria" (such as the name "Actual&quot and then extract the data "selectively" to separate sheets.

Certainly one option is to extract the data to the 28 separate sheets you mentioned.

However, with this extaction option being so fast, another option is to set up a "macro button" whereby the user can simply click the button and specify which data is to be extracted to a separate sheet - but use just the "one" extraction sheet (instead of having 28 separate sheets).

This extraction process can easily set up the extracted data for printing, where you could have the data automatically sent to the printer. Or, alternatively, you could first &quot;preview&quot; the data, and then print it by just clicking the &quot;print&quot; icon or using <Control> P <Enter>.

With this option, you could maintain &quot;one database&quot; where you would process all your updates, and then whenever you want a &quot;report&quot; on any of the 28 &quot;data types&quot;, you would simply click the button and extract the data.

This &quot;database functionality&quot; is QUITE useful and FAST. Not so long ago, I posted a solution to another similar Tek-Tips problem. Another Tek-Tips member posted a &quot;looping&quot; option which I tested. It took over 37 minitues, after which time I &quot;gave up&quot;. My solution took EXACTLY 2 SECONDS.

If you would like to consider this option, I would recommend that you email me your file - but only with a small number of records. If you have sensitive data, simply replace it with fictitious data that still reflects the type of data being used.

I'll then insert the &quot;macro button(s)&quot; and VBA code and return the file to you. The file will provide a good example, with notes included, regarding how to set up and use Excel's powerful &quot;database functionalitiy&quot;.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca