processing a long list and moving content to seperate sheets

**tsrwebman** · 06-29-2011, 10:09 AM

I receive a report in CSV form, an indeterminate number of rows and columns A to AF.
(absolute max - 2000rows)
When I open it in XL non of the column widths are sensible - as expected.

I want to create an analysis SS. My thought is to create this analysis seperately. and use it as a template with columns correct width and un-needed cols hidden as required.
I open the csv - select the data and paste it into a sheet on my analysis SS - so far so good.

My Analysis SS contains 7 sheets. 'Import' and X1, X2, X3, X4, X5 and X6
X sheets will have formulae in rows 1-9 and col AH
Import sheet starts with row 1 as titles and data in A2 to AF??

I would like to have an 'automatic' feature so that when the imported data is pasted into the import page column P is examined (it contains text) and depending on the first 2 characters the whole row is copied to the appropriate sheet. i.e if P2 contains X6-10:15
then the whole of IMPORTS:a2-af2 is copied to the next available row on sheet X6 starting in col A row 11
Rows that do not have X as the first char in col P can be ignored.

Once copied each X sheet needs the copied data to be sorted, ascending on cols P, M and A.

I work in XL 2007, but this has to work in 2007 and XP.
( I am a volunteer web master at Telford Steam Railway, a registered charity. This is intended as tracking/monitoring for our Xmas Santa Special tickets, whilst I have a rudimentary XL background this is beyond me)

Attached is an sample of what I had in mind - mostly without formulae just done manually
except col AH which has a formula.
I would appreciate any help - is anyone prepared to help me out with this

**tsrwebman** · 06-29-2011, 11:39 AM

Just to prove I arent completely dumb - I did soem research and came up with

Please Login or Register  to view this content.

Which is almost correct - yeah
Now how do I change it so that instead of copying the whole row containing 'rngCell' it just copies colums A:AF?

**tsrwebman** · 06-29-2011, 02:42 PM

OK - I wrote a process to cycle through all the X sheets and clean out any data - seems to work.
Then I run the process above to split the imported data onta seprate sheets - seems to work - with the exception that it copies whole rows instead of cols A;AF - I need help with that please.
Then I wrote a sorter process which should cycle through each sheet and sort the data -

Please Login or Register  to view this content.

but it says "Run time error 1004 - the sort reference is not valid"- and I cant see the bug - new version of SS attached -

I would appreciate it if someone could take a look

Time to go for today - I'm hungry

**tsrwebman** · 06-30-2011, 02:58 AM

Well - - what a load of rubbish I wrote yesterday - clearly a bad day for me -
Mr Moderator - please carry out the computer equivalent of the ceremony of 'screwing it up and throwing it in the bin'.

Lets start afresh.