Help to transfer data from one sheet to another

[C J W]

Hi All

I am hoping that you can help me find a macro for the following, to help make a long process nice and simple. I need to be able to take data from one sheet and copy and paste it to another - but with a catch. the first sheet contains a list of names and the days of the week (Monday to Sunday) against each name is the person availability with unavailable days enter as an 'U'.

What I would like to be able to do is take this information and copy it across to another sheet which lists all the same names and the days they are unavailable for the following six months. in a ideal world when this information is copied across only the 'U' would be copied across and any data already entered onto the calendar would be left in place and not overwritten (is this last bit possible?)

Thanks for any help you will be able to give

Chris

[Maudibe]

Hi CJ,

Consider this code that allows the user to select the days unavailable ("U") on the Details sheet for a person. The person's schedule on the Availability sheet will automatically be updated as the user enters the data. Unavailable days will be transferred across while not overwriting data if it exists. Removing an unavailable day on the Details sheet will remove all the unavailability's for that weekday across the board for that person. Also added is the conditional formatting on both sheets.

Please Login or Register  to view this content.

[C J W]

Thank you - that is very clever just what I was after. (I would have never gotten that!) Although is it possible when checking what day of the week it is that instead of using "Mon", "Tue" as the reference ,can we use the value from a formula, as ideally the availability sheet will use a formula (weekday) to show what day of the week it is.

Thanks again

[Maudibe]

Sure, change the following line in the code:

Please Login or Register  to view this content.

Maud

[C J W]

thanks for the quick reply, I have replaced the line of code and amended the datasheet to the formats I need to use, but I get a debug error. I have attached a copy of what I have done - can you advise?

thanks once again.

[Maudibe]

CJ,

On the Availability sheet, the header was changed to formulas and formats. Row 1 header used to be the days of the week (Sun, Mon, Tue, etc.) and is now converted to a date (1/1/2017, 1/2/2107, etc.) which displays a weekday with a "ddd" format. So when the code searches for "Sun", it can't find it (ERROR). Rows 2,3,and 4 are different formats of row 1 to show the day ("dd"), month ("mmm"), and the year ("yyyy").

To correct the problem, replace row 1 with the text of the weekdays ("Mon", "Tue", etc) going across. In row 2 you can enter the full date starting at 1/1/2017 like you did in row 1 and drag across. Since it is formatted as "dd", it will appear as you want. Now have header rows 3 and 4 equal to row 2 (instead of 1) and they will appear as you formatted them.

So essentially, row 1 is texted weekdays and rows 2-4 are formatted rows the equals the date in row 2. Since rows 2-4 are not used in the search, their formats/formulas are irrelevant

...is it possible when checking what day of the week it is that instead of using "Mon", "Tue" as the reference ,can we use the value from a formula?

the changed formula, AvailDay = Choose(Weekday(date), "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat") was entered in the code literally. "date" must be replaced by a value, variable, or cell that evaluates to some date. If you leave the word date, it will be interpreted as Date function which will always be the current date and the formula will output the weekday of the current date. Tomorrow it will be different

The reason that I wrote the code they way I originally did was so that whatever weekday that you marked as unavailable for a person on the Details sheet would carry over and mark all that weekdays unavailable on the Availability sheet throughout the year. With the new change, if the weekday is not evaluated by the column you entered the "U", the code will act independently of which column you entered and use a date that you sent to macro. So if you select a "Mon" on the Details sheet, the code may mark every "Sat" on the Availability sheet because that what was sent to the code

I hope that explains it better. If these are must have changes, then I am willing to help you further. Please give me more detail on how the date (weekday) formula will be determined and sent to the macro.

Maud

[C J W]

thank for explanation - you've been more than helpful and what you have provided will work great.

[C J W]

I have posted a follow up to this original query here:

Please Login or Register  to view this content.