shahdelsol
Active Member
- Joined
- Jul 21, 2009
- Messages
- 276
- Office Version
- 365
- Platform
- Windows
I will greatly appreciate anyone who can help me with a macro that does the following actions:
I need to transfer data from 4 books (that all are open and I call them Book1, Book2 and so on) to one book that is activebook ( since there are 5 books open, I am not sure you could tell which one is active, if this is a problem, then I would name this book ”mainbook”). This is how I think it should be done but if someone thinks it can be done in much more efficient way, I would appreciate it. The activebook (mainbook) has 6 tabs that are called: Daily, Day1, Day2, Day3, Day4, Day5.
The inputbox would ask the user for what day it is then if the answer is 1, I would need the value of the B80 of activesheet of book1 go to C3 of “Daily” tab of the activebook (mainbook) and then continue with
Book1 B81 goes to D3 of activebook(mainbook)
Book1 B82 goes to E3 of activebook (mainbook)
Book1 C80 goes to C4 of activebook(mainbook)
Book1 C81 goes to D4 of activebook(mainbook)
Book1 C82 goes to E4 of activebook(mainbook)
If the answer is 2, then
Everything is the same, only the location of cells in activebook (mainbook) changes to :
C8,D8,E8
C9,D9,E9
If the answer is 3, the locations are :
C14,D14,E14
C15,D15,E15
For 4:
C20,D20,E20
C21,D21,E21
And for 5:
C26,D26,E20
C27,D27,E27
This was all for Daily tab. Now for Day1 tab of activebook (mainbook), I would need the following data from book2, book3, and book4 and I think this needs to be done one at a time. And again if the answer is 1, the values of B12 to B82 of book2 (and since the cells are merged, I am only interested in even cells which are B12, B14, B16 and so on) go to A2 and down of “Day1” tab of the activebook (mainbook) and this process should stop when it reaches to an empty cell in the even cells of B12:B82. Keep in mind that all odd cells are empty. Again if the answer for input box is still 1, then this time the value of the 8th cell to the right of B12 of book2 goes to C2 of the activebook (mainbook) and if that value is bigger than 0:00 , Make C2 equal to Y and if it is equal to 0:00 C2 = N and this goes until A2 and down has data.
The same thing would repeat for book3 and book4. The data of book3 (B12:B82) will continue on the last empty cell of the column A of Day1 and so Book4 (B12:B82).
If the answer from input box is 2, everything is the same, the only difference is you go to 11th cell to the right of B12 of Book2,Book3,Book4. And if the answer is 3, you go to 14th cell and if it is 4, you go to 17th and if it is 5, you go to 20th cell.
If you have read so far, I am already much grateful. The explanation may be too long but the code itself might not be.. maybe I am wrong. Thanks and I hope it is clear to understand but please ask if you have any questions.
I need to transfer data from 4 books (that all are open and I call them Book1, Book2 and so on) to one book that is activebook ( since there are 5 books open, I am not sure you could tell which one is active, if this is a problem, then I would name this book ”mainbook”). This is how I think it should be done but if someone thinks it can be done in much more efficient way, I would appreciate it. The activebook (mainbook) has 6 tabs that are called: Daily, Day1, Day2, Day3, Day4, Day5.
The inputbox would ask the user for what day it is then if the answer is 1, I would need the value of the B80 of activesheet of book1 go to C3 of “Daily” tab of the activebook (mainbook) and then continue with
Book1 B81 goes to D3 of activebook(mainbook)
Book1 B82 goes to E3 of activebook (mainbook)
Book1 C80 goes to C4 of activebook(mainbook)
Book1 C81 goes to D4 of activebook(mainbook)
Book1 C82 goes to E4 of activebook(mainbook)
If the answer is 2, then
Everything is the same, only the location of cells in activebook (mainbook) changes to :
C8,D8,E8
C9,D9,E9
If the answer is 3, the locations are :
C14,D14,E14
C15,D15,E15
For 4:
C20,D20,E20
C21,D21,E21
And for 5:
C26,D26,E20
C27,D27,E27
This was all for Daily tab. Now for Day1 tab of activebook (mainbook), I would need the following data from book2, book3, and book4 and I think this needs to be done one at a time. And again if the answer is 1, the values of B12 to B82 of book2 (and since the cells are merged, I am only interested in even cells which are B12, B14, B16 and so on) go to A2 and down of “Day1” tab of the activebook (mainbook) and this process should stop when it reaches to an empty cell in the even cells of B12:B82. Keep in mind that all odd cells are empty. Again if the answer for input box is still 1, then this time the value of the 8th cell to the right of B12 of book2 goes to C2 of the activebook (mainbook) and if that value is bigger than 0:00 , Make C2 equal to Y and if it is equal to 0:00 C2 = N and this goes until A2 and down has data.
The same thing would repeat for book3 and book4. The data of book3 (B12:B82) will continue on the last empty cell of the column A of Day1 and so Book4 (B12:B82).
If the answer from input box is 2, everything is the same, the only difference is you go to 11th cell to the right of B12 of Book2,Book3,Book4. And if the answer is 3, you go to 14th cell and if it is 4, you go to 17th and if it is 5, you go to 20th cell.
If you have read so far, I am already much grateful. The explanation may be too long but the code itself might not be.. maybe I am wrong. Thanks and I hope it is clear to understand but please ask if you have any questions.
