Moving column data into the next sheet on condition of column range using vba

0
votes

For a sailing club. I have created columns for the weekdays from Monday to friday on a sheet representing the first week of the month and so I made 4 sheets named week1 to week4 for the whole month using a vba userform I put data into the cells.

      monday       tuesday      wednesday     Thursday      friday

boat1

boat2 . .

My question: I would like to create a macro that if the date is in week2 will rather transfer automatically userform data(textbox "name") into next sheet than current seheet.

I don't know if it makes sense or if I have to give more details Thank you for any help!

1
excelvba
I don't get it :/ so what is your userform asking? Is it some sort of booking system? So if they put "boat1" in to textbox "name", and the "date" in to another text box... you want those values to appear in the appriate worksheet right? – Simon
Hello Simon! that's exactly my concern, – Barnabe Muzima
lets assume the booking of March 2015, if someone takes boat1within dates from 2nd to 6th (only weekdays) his name will appear in week1 worksheet, 9th to 13rd week2 worksheet.......the trick is that someone can book for 3days , 4days or 5 days which will go beyond the active sheet and involve the following one for next week. do u get it now? sorry im new in vba ! Thanks – Barnabe Muzima

1 Answers

0
votes

Start by breaking the problem in to steps: you'll need to:

  1. Call a userform and retrieve values some how (looks like you've done this bit)
  2. I think you should create a loop for each day in your date range
  3. For each "day" in that loop you need functions that gives you the correct sheet for that day
  4. You need a function that gives you the correct cell for that day within that sheet
  5. You need some way of handling if there's already bookings for that day

I'd say the easiest way to match a date with a sheet is a lookup table... use application.worksheetfunction.vlookup within your VBA code, and instead of "exact match" use "TRUE" for final parameter... then have a lookup table which has entries for the first day of the month next to a sheet name for that month. This will work for ALL dates because vlookup uses the "largest value = or less to lookup" if you don't specify exact match