splitting a long column of data into separate columns

thanks charlize and Mick G for your help, appreciate both answers, really. I tried both, and Mick G's code caused a Subscript Out Of Range error here

Please Login or Register  to view this content.

which i don't understand so i can't fix it on my own...

charlize your code is simply amazing, you even added code to repeat the whole thing across every sheet. i tip my hat off to you, thanks so much.

how do i exclude the first sheet from this macro? and is there a way to incorporate re-sorting the data into your code? perhaps a modification of:

Please Login or Register  to view this content.

Variation to split up the coding to locate the last row of the active column.

Please Login or Register  to view this content.

Charlize

ps. - use F8 to watch the coding go and look at the values of the variables when they get changed. Just put your mouse above a word and you should see some yellow sticky notes that give some info about the value of a variable.
- what's the value of vletter. The first time it should be C. Change the coding to determine the vletter to this

Please Login or Register  to view this content.

Notice the extra . before Cells. It seems that 2007 needs an explicit declaration of the range. In 2003 it works as showed.

Please Login or Register  to view this content.

by stepping into the code with F8, i discovered that the macro skips through this whole section of code; from

Please Login or Register  to view this content.

it just jumps to Else. thus vletter remains empty.

I've enclosed a copy of the worksheet for you to have a look. had to zip it up cos excelforum doesn't allow *.xlsm uploads. the worksheet "Original" was split up using your split_data_employees macro, i've included split_data_of_day_V2 in the coding but it can't be run.

again, thank you for your help, i don't know how i can repay you for the effort.

try

Please Login or Register  to view this content.

thanks jindon, will try it out after i fix the formats.

for some reason the cells are not formatted properly (my original data) so perhaps that's why all the coding's jumbled up. will update when i get it fixed.

update: got the dates fixed, charlize's code still doesn't work, jindon's code doesn't split it but rather duplicates both columns of date&time over to columns C/D/E/F/G/H. i've uploaded an updated copy of the data file in post #8 of this thread.

thanks for all the help, appreciate all of it.

after replacing lrow, and after stepping into F8, lrow=76

when it reached

Please Login or Register  to view this content.

then it jumped to Else, thus skipping the vletter line altogether. then i get a application-defined or object-defined error at

Please Login or Register  to view this content.

2 lines after Else. At this stage, vletter="" and lrowvletter=1.

Actually, if this gets too complicated or if it's taking too much of your time, i can work with your first code, and i added a line of code to skip the first sheet (see the [If Ws.Name <> "Original" Then] code at the middle):

Please Login or Register  to view this content.

this works perfectly well on my side only that it's reversed (OUT at the top, IN at the bottom, as you said). I tried to convert your Copy_Last_Cells macro to be compatible with this but lol trial and error does not always work in programming... this was your code:

Please Login or Register  to view this content.

and this is what i tried to convert it to, so as to process the data in the attached picture:

Please Login or Register  to view this content.

naturally i came up with tons of errors, so if you'd like, we can skip the whole V2 idea and work from the V1 idea, all i need is to modify your Copy_Last_Cells code to work by columns instead of rows. alternatively we can work with V2 since apparently it works fine on your side, so perhaps it's just a small tweak or two to get it working on mine.

again, a heartfelt thank you.

~Michael

That is really odd,
If the data in col.A are serial date, obviously they are, it should retrurn 7.
try

Please Login or Register  to view this content.

P.S
If still return error then try

Please Login or Register  to view this content.

awesome. version 3 did the job perfectly. absolutely amazing.

charlize, thank you.

thanks also to jindon, your last code came close but somehow the dates got mixed up and stuff... included as an attachment if you'd like to see, as jindon_code_result.jpg. but it's okay now that the problem is solved, thanks also to you.
_________________________________________________________________

now just to tie up the loose string, regarding Copy_Last_Cells, how do i modify it to pick out the last pair of cells, then paste them out at row A61 onwards? i will add the code below to complete the IN-OUT pair at row A60:

Please Login or Register  to view this content.

Original copy_last_cells:

Please Login or Register  to view this content.

What i tried to do, but failed:

Please Login or Register  to view this content.

and the end result should be as in the attachment copy_last_cells_v2.jpg: