I need a macro that I can connect to a button.
The macro would need to copy from the previous sheet in the same work book and paste to the worksheet with the button. I have a workbook per month and within each workbook I have a worksheet for each day of the month. So this button will be on each worksheet. So each day they will click the button and the following will occur.
MOVING CURRENT PATIENT INFORMATION FROM PREVIOUS DAY TO CURRENT DAY
copy B7:F26 previous sheet - paste B7:F26 current sheet
copy I7:J26 previous sheet - paste I7:J26 current sheet
copy M7:Q26 previous sheet - paste M7:Q26 current sheet
copy T7:U26 previous sheet - paste T7:U26 current sheet
ADDING PREVIOUS DAY'S ADMISSIONS TO CURRENT DAY
If (on previous sheet) B35 = Barney then copy C35:E35 and paste in first empty row in C7:E26 (This repeats for B35:B39)
If (on previous sheet) B35 = Denis then copy C35:E35 and paste in first empty row in N7:P26 (This repeats for B35:B39)
If (on previous sheet) M35 = Barney then copy N35:P35 and paste in first empty row in C7:E26 (This repeats for M35:M39)
If (on previous sheet) M35 = Denis then copy N35:P35 and paste in first empty row in N7:P26 (This repeats for M35:M39)
MOVING CURRENT DAY'S DISMISSALS OUT OF THE CENSUS AND INTO THE DISCHARGE AREA
On current sheet,
If J7=I1 then copy B7:F7 and paste in first empty row in range B28:F33 then clear contents in B7:F7 This is for the rows J7:J26
If U7=I1 then copy M7:Q7 and paste in first empty row in range M28:Q33 then clear contents in M7:Q7 This is for the rows U7:U26
PUTTING THE CURRENT PATIENTS IN ALPHA ORDER
sort B7:J26 by column C
sort M7:U26 by column N
FYI All cells are locked and protected (w/o a password) except B7:F26, I7:J26, M7:Q26, T7:U26, B28:F33, I28:I33, M28:Q33, T28:T33, B35:F39, I35:I39, M35:Q39, T35:T39.
Excel 2007
<tbody>
</tbody>
The macro would need to copy from the previous sheet in the same work book and paste to the worksheet with the button. I have a workbook per month and within each workbook I have a worksheet for each day of the month. So this button will be on each worksheet. So each day they will click the button and the following will occur.
MOVING CURRENT PATIENT INFORMATION FROM PREVIOUS DAY TO CURRENT DAY
copy B7:F26 previous sheet - paste B7:F26 current sheet
copy I7:J26 previous sheet - paste I7:J26 current sheet
copy M7:Q26 previous sheet - paste M7:Q26 current sheet
copy T7:U26 previous sheet - paste T7:U26 current sheet
ADDING PREVIOUS DAY'S ADMISSIONS TO CURRENT DAY
If (on previous sheet) B35 = Barney then copy C35:E35 and paste in first empty row in C7:E26 (This repeats for B35:B39)
If (on previous sheet) B35 = Denis then copy C35:E35 and paste in first empty row in N7:P26 (This repeats for B35:B39)
If (on previous sheet) M35 = Barney then copy N35:P35 and paste in first empty row in C7:E26 (This repeats for M35:M39)
If (on previous sheet) M35 = Denis then copy N35:P35 and paste in first empty row in N7:P26 (This repeats for M35:M39)
MOVING CURRENT DAY'S DISMISSALS OUT OF THE CENSUS AND INTO THE DISCHARGE AREA
On current sheet,
If J7=I1 then copy B7:F7 and paste in first empty row in range B28:F33 then clear contents in B7:F7 This is for the rows J7:J26
If U7=I1 then copy M7:Q7 and paste in first empty row in range M28:Q33 then clear contents in M7:Q7 This is for the rows U7:U26
PUTTING THE CURRENT PATIENTS IN ALPHA ORDER
sort B7:J26 by column C
sort M7:U26 by column N
FYI All cells are locked and protected (w/o a password) except B7:F26, I7:J26, M7:Q26, T7:U26, B28:F33, I28:I33, M28:Q33, T28:T33, B35:F39, I35:I39, M35:Q39, T35:T39.
Excel 2007
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
5 | INS | PATIENT | UD | ELOS | P | D/C | INS | PATIENT | UD | ELOS | P | D/C | |||||||||
6 | Admit | Int. | Rem | Est Dis | Admit | Int. | Rem | Est Dis | |||||||||||||
7 | 1 | X, Wanda | 2-Jul | need LOS | 1 | PPK | X, Tammy | 5-Jul | 29-Jun | need LOS | |||||||||||
8 | 2 | WP | X, Raymond | 13-Jun | 29 | 9 | 11-Jul | 13-Jul | 2 | OT | X, Carmine | 29-Jun | 26-Jun | need LOS | 4 WKS | ||||||
9 | 3 | X, Willia | 28-Jun | need LOS | ELOS - 7/10 | 3 | X, Leonard | 18-Jun | 16 | 1 | 3-Jul | 5-Jul | |||||||||
10 | 4 | WP | X, Joshua | 14-Jun | 29 | 10 | 12-Jul | 3-4 wks | 4 | X, Franklin | 13-Jun | 20 | 0 | 2-Jul | re eval | ||||||
11 | 5 | BC | X, Priscilla | 2-Jul | need LOS | 5 | X, Joseph | 28-Jun | need LOS | 6-Jul | |||||||||||
12 | 6 | X, Raymond | 25-Jun | need LOS | 4 WKS | 6 | W | X, Dien | 2-Jul | need LOS | |||||||||||
13 | 7 | W | X, Melvin | 25-Jun | need LOS | 2 WKS | 7 | PPK | X, Warren | 2-Jul | need LOS | ||||||||||
14 | 8 | X, Mildred | 15-Jun | 22 | 4 | 6-Jul | 6-Jul | 8 | X, Allen | 19-Jun | 34 | 20 | 22-Jul | reeval | |||||||
15 | 9 | BC | X, Quinton | 5-Jul | 29-Jun | need LOS | 9 | W | X, Ramon | 21-Jun | 23 | 11 | 13-Jul | reeval | |||||||
16 | 10 | X, Jerry | 29-Jun | need LOS | 10 | J | X, Servendo | 22-Jun | 12 | 1 | 3-Jul | 2 wks | |||||||||
17 | 11 | PPK | X, Margarita | 5-Jun | 1-Jun | 23 | -9 | 23-Jun | 2 wks | 11 | CO | X, Daryl | 9-Jul | 25-Jun | need LOS | 6-Jul | |||||
18 | 12 | X, Vicki | 22-Jun | 16 | 5 | 7-Jul | 1-2 wks | 12 | X, Sharon | 19-Jun | 25 | 11 | 13-Jul | reeval | |||||||
19 | 13 | 13 | |||||||||||||||||||
20 | 14 | 14 | |||||||||||||||||||
21 | 15 | 15 | |||||||||||||||||||
22 | 16 | 16 | |||||||||||||||||||
23 | 17 | 17 | |||||||||||||||||||
24 | 18 | 18 | |||||||||||||||||||
25 | 19 | 19 | |||||||||||||||||||
26 | 20 | 20 | |||||||||||||||||||
27 | DISCHARGES | 1-Jan | |||||||||||||||||||
28 | 1 | 1 | CO | X, Daryl | 9-Jul | 25-Jun | need LOS | 3-Jul | |||||||||||||
29 | 2 | 2 | |||||||||||||||||||
30 | 3 | 3 | |||||||||||||||||||
31 | 4 | 4 | |||||||||||||||||||
32 | 5 | 5 | |||||||||||||||||||
33 | 6 | 6 | |||||||||||||||||||
34 | ADMITS | 0-Jan | |||||||||||||||||||
35 | 1 | Barney | 6 | Denis | |||||||||||||||||
36 | 2 | Denis | 7 | Barney | |||||||||||||||||
37 | 3 | Barney | 8 | Denis | |||||||||||||||||
38 | 4 | Denis | 9 | Barney | |||||||||||||||||
39 | 5 | Barney | 10 | Denis |
<tbody>
</tbody>
07-03-12