macro to lookp through column and seperate values

Hey guys,

I am not sure where to start here

I have 1 sheet that has a column of dates and other values

For each non date, the values below it are associated with it

these values need to be automated so that dates go in the date column on sheet 2 and suit:12345 go into another column.

any ideas?

Here is the sample workbook

Thanks!

And here is your modified sample workbook.

To test run macro "Order"

Alf

Alf,

Thank you!

Sorry for the delayed response. I never got a notification. I am running this now.. seems to work very well!!

Thank you for your time on this!

Alf,

how would it be different if I needed to add 4-5 more columns of data? Of course it would be put into the corresponding worksheet.

Also, we were in Sweden last month. Hung out in Gothenburg a good bit.. really amazing

You are welcome and thanks for feedback.

I'm not sure how much use this could be to you. If fore instance you get bookings daily and you wish to update reservations a macro should check if sheets for that particular suite exist and if so add the new data and sort all data based on date perhaps. Or should all sheets get deleted and new sheets created whenever a macro is run?

Sheet name "SUIT: 12348" is a "forbidden" sheet name by the way on account on the ":". This is an Excel limitation but "SUIT 12348" or "SUIT-12348" are ok sheet name.

Alf

I think I would like to keep the sheets that are originally there.. I have to make them look like a specific receipt. show ill be copying and pasting the dates that correspond with each suit starting at A22 :G22 so you can see there will be multiple columns

how would it be different if I needed to add 4-5 more columns of data?

Upload a workbook with the all the data and I'll have a look at to see what I can do.

Alf

Alf,

Thanks so much!

Sorry to be a pest.

See if this is how you wanted.
Different method.

Please Login or Register  to view this content.

You got a nice solution to you problem from jindon but if you still wish to have each suit on a separate sheet you could add this macro.

Macro is based on data in the "Result" sheet jindons macro create, it autofilters data and activate appropriate sheet and copy the result without "ower writing older results. If sheet is not found macro will creates it before pasting values to it.

Please Login or Register  to view this content.

Alf

Thanks Jindon and Alf(My Man) I am trying each this afternoon.. will let you guys know.. Thanks so much!

Having tested jindon's macro I'm impressed by its flexibility (but then after all he is a Forum Guru) because if you exstend the number of columns from I to M, O or whatever his code will cope with it wetheras mine part will fall flat on it's feet (as far as macros has feets) because I've hardcoded the number of columns in the heading

Please Login or Register  to view this content.

So in order to make mine code as flexcible as his I've rewritten it a bit, new code:

Please Login or Register  to view this content.

Alf

Thanks, Alf and Jindon.

Both work very well..i just got a added step to this thing which is frustrating... For each Suit that has its own information..

I need to take the data and put it iN CELLS B22, C22, E25. instead OF SHEEETS 1,2 3,4 I have had to create sheets based on their suit # (I already have these...

just need to transfer

Date (column B ) starting at cell 22
Code (WO, BF..) Column C starting at cell 22
and Amount column E starting at cell 22

each sheet is set up the exact same

so sheet 201, 202A .. ect.

COlumn, cell
B,C,E 22

I would love to learn if you could put some notes in your VBA code for me to follow.

Can you just upload a workbook showing before/after?

See attached... Main if before

Other Tabs are after

No need to prepare each worksheets before run the code.

Please Login or Register  to view this content.