Array or copy area dilema

[UnsolvedCoding]

I have been tasked with automating a process that, to be kind, has proven quirky. The problem that is giving me the biggest annoyance is transfering information from the main sheet to a different workbook.

The main sheet is broken out by season (column A) and then subtotaled in three different columns for each change in season.

The part of the job that is giving me trouble is taking the relevant information from the entire season and transfering it to the new worksheet.

I thought of copying it over as a season, but that raises questions about identifying start and end locations. If it is copied line by line that is highly inefficent but possible by using the subtotal lines as markers.

Arrays have also been explored, but combining the statments into one string won't break it out into columns like it needs to be when its put into the new sheet. The thought of using several arrays with each holding the information from a column has been explored, but again, the problem is the code gets clunky fast.

Multi-dimensional arrays have been pursued but that is proving to be somewhat difficult because of needing 13 columns of information and not knowing how many rows will be used. There can be in excess of 5,000 rows per season.

Anyone have any ideas or examples on how to put or copy the information where it needs to go efficently?

 

[PHV]

What is your input data ?
What is your expected result ?
What is the code you've tried so far and where in it are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

hi,

Your source table, containing subtotals, is NOT the table you want to use. You need to use the source table for the subtotaled table.

I thought of copying it over as a season, but that raises questions about identifying start and end locations.

What do you mean bu this? A properly designed table would facilitate this issue with no problems at all!

Using a properly designed source table, a solution could include a query, more than likely.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[UnsolvedCoding]

This turned out to be a bigger mess then I thought. Because we have to sort things out by season and the season are not alpha numeric in sequence we had to create a way to sort.

Once we got the basic sort completed we ran into a problem of some of the seaons had to be broken out based on specific sequences within the id number and some had to be broken out based on the id description.

I wanted to change the table but can't do that to the orginal table because we get it sent to us from a outside company that is controled by a client. In other words we have no control of how or what format the orginal information get sent to us.

What we ended up having to do is create the sort to work with seasons, then sort based on ID, pull that information, and then sort based on description and pull that informaiton.

The information that has been pulled is put into a seperate workbook that acts as a report. The report workbook has the info for each season on its own tab. Then the info for the last accounting month broken out by season, with two broken out by id and one by description are summed on a tab. The last accounting month format is repeated for the tab that holds the sums for the life of the project.

PHV and SKIP - thanks to both of you for responding.

 

[SkipVought]

The report workbook has the info for each season on its own tab.

IMNSHO, this is a grave mistake, unless you now or never plan to do any kind of analysis/calculation/reporting on more than one season, and my point is can you really say "Never!"???
[tt]
What, never?

No, never!

What, never?

Well, hardly ever.
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Burser]

Have a look at AdvancedFilter for filter/copy in one go.

It has it's quriks too, but you'll get round them one way or another. It sounds like exactly what you want to do.