+ Reply to Thread
Results 1 to 16 of 16

VBA. One workbook, 9 sheets, how to transfer data from all sheets to one sheet?

  1. #1
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    VBA. One workbook, 9 sheets, how to transfer data from all sheets to one sheet?

    Hi I wonder is someone can help me
    I have attached example workbook. It might be useful to open this at some point to see the structure in more detail.

    I have a workbook where I have 9 sheets as below:
    Please Login or Register  to view this content.
    The 10th sheet ("Data_compiled") is where I would like to populate the output.

    On each of these 9 sheets the data is related to each other in terms of cell position
    Example:
    cell I2 on sheet "Data_shift" = DS (it means Day Shift)
    cell I2 on sheet "Data_name" = Alan
    cell I2 on sheet "Data_time" = 07:00-19:00
    etc, etc


    I will give layout examples of two sheets

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    First I would love the code to use sheet "Data_shift" as a master trigger point. Another words it should scan/loop range I2:IV (last row as per col F) of sheet "Data_shift". It should scan the range cell by cell e.g. I2, J2, K2, etc. If the scanned cell is blank then do nothing and move to the next cell. If there is a cell that is not blank (e.g. I2) then take the date information of the respective row (e.g. sheet cells B2:F2) and paste it to sheet "Data_compiled" to first available row as per col F (e.g. row 2 is available, so B2:F2 will be populated. Then the code would take the values from each sheet (e.g. cell I2 from all 9 sheets) and start to populate them from column G onwards (e.g. G2, H2, I2, J2, K2, etc).
    So when its finished with I2 of each sheet, it would check next cell "J2" on sheet "Data_shift", if its not blank, then repeat the procedure:
    E.g. J2 is not blank so it takes the date information of the respective row (e.g. sheet cells B2:F2) and paste it to sheet "Data_compiled" to first available row as per col F (which now would be row 3), so B3:F3 will be populated. Then the code would take the values from each sheet (e.g. cell J2 from all 9 sheets) and start to populate them from column G onwards (e.g. G3, H3, I3, J3, K3, etc).

    Below is an example of output (sheet "Data_compiled")




    Please Login or Register  to view this content.
    NB. If the code finds non blank cell in sheet "Data_shift", it will mean that there will most definitely be data of corresponding cell on all sheets with exception of sheet "Data_name" (with sheet "Data_name" the cell may or may not contain a value). This is normal. This would simply mean that on the output sheet there may be few blank cells. Please see the example below and notice the two blank cells in "Name" column.

    Please Login or Register  to view this content.
    I have attached a spreadsheet that contains "example" sheet of how sheet "Data_compiled" should look after the VBA. I compiled this manually, which as you can imagine took a lot of time. I have also done some color coding in the hope it makes things easier to understand.


    If anyone can help me out I would be very thankful. Unfortunately I'm not smart enough to figure this out myself.
    Cheers
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: VBA. One workbook, 9 sheets, how to transfer data from all sheets to one sheet?

    ***bump***

  3. #3
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: VBA. One workbook, 9 sheets, how to transfer data from all sheets to one sheet?

    ***bump***

  4. #4
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: VBA. One workbook, 9 sheets, how to transfer data from all sheets to one sheet?

    The way this file has been designed is very complicated. It takes some time to actually understand how the data elements are structured. Nevertheless, will work something out for you shortly.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  5. #5
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: VBA. One workbook, 9 sheets, how to transfer data from all sheets to one sheet?

    Thank you.
    If there is anything that I can explain further or there are any questions, I will do my best to answer them. I genuinely appreciate any help.
    Data between columns B:F will always be fixed as in the example (e.g. it wont move). Data between column I to column IV can be bit random.

  6. #6
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: VBA. One workbook, 9 sheets, how to transfer data from all sheets to one sheet?

    Data between columns B:F will always be fixed as in the example (e.g. it wont move). Data between column I to column IV can be bit random.
    In the data_shift tab right?

  7. #7
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: VBA. One workbook, 9 sheets, how to transfer data from all sheets to one sheet?

    Hi
    I thought maybe it makes sense to almost like talk through the first 2 cycles/loops of how I imagine how the code would work (example is as per the file attached on first post).

    Lets use the below as an example. Note that this is just 1 sheet out of the whole 9 sheets.
    Please Login or Register  to view this content.
    1) In the beginning the code decides on the scan range which is Sheets("Data_shift"). Range("I2:IV(last row as per column F)"). Maybe something as per below:
    Please Login or Register  to view this content.
    2)It then starts to check the sheets cell by cell
    3)If cell (I2 - in GREEN) is blank then do nothing and loop/move to the next cell (i.e. J2). If cell (I2) is not blank then remember the reference cell location (i.e. R2C9) and then proceed.
    4)Using the reference cell location (i.e. R2C9) the code will use the row number (i.e 2) and take information from sheet "Data_shift" column B to column F (i.e. R2C2:R2C6) and store this in the memory. Example - the blue selection below is copied and stored in memory.
    Please Login or Register  to view this content.
    5) Now the code will find the first available blank cell on sheet "Data_compiled" using col B as a reference. In our example the first row is used for headers, so first available cell is column B Row 2 (i.e. R2C2).
    Please Login or Register  to view this content.
    6) The code would then paste the previously obtained information (Blue section as per point 4) to column B to column F by using the obtained first available row as (see point 5) the reference. So in our example it would paste the info to Sheet "Data_compiled" R2C2:R2C6 so the outcome would be as below:
    Please Login or Register  to view this content.
    7)Then the code would start to copy the reference cell I2 (i.e. R2C9) of each sheet (all those 9 sheets) and paste it to the sheet "Data_compiled" to relevant hard coded column as per below.
    Please Login or Register  to view this content.

    CONTINUES ON NEXT POST
    Last edited by rain4u; 05-07-2012 at 12:03 PM.

  8. #8
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: VBA. One workbook, 9 sheets, how to transfer data from all sheets to one sheet?

    So after point 7 it would look like below:
    Please Login or Register  to view this content.

    After this the it would be a rinse and repeat with steps 2 - 7 until all cells in range Sheets("Data_shift"). Range("I2:IV(last row as per column F)") have looped through.
    I will go through one more example cycle/loop so hopefully it starts to make bit more sense:

    2)We move/loop to the next cell
    3)If cell (J2 - in GREEN below) is blank then do nothing and loop/move to the next cell (i.e. K2). If cell (J2) is not blank then remember the reference cell location (i.e. R2C10) and then proceed.
    4)Using this new reference cell location (i.e. R2C10) the code will use the row number (i.e 2) and take information from sheet "Data_shift" column B to column F (i.e. R2C2:R2C6) and store this in the memory. Example - the blue selection below is copied and stored in memory.
    Please Login or Register  to view this content.
    5) Now the code will find the first available blank cell on sheet "Data_compiled" using col B as a reference. In our example the first row is used for headers, second row is used because of the first loop, so the first available cell is column B Row 3 (i.e. R3C2). like below.
    Please Login or Register  to view this content.

    6) The code would then paste the previously obtained information (Blue section as per point 4) to column B to column F and use the obtained first available row as (see point 5) the reference. So in our example it would be - Sheet "Data_compiled" R3C2:R3C6 so the outcome would be as below:
    Please Login or Register  to view this content.
    7)Then the code would start to copy the reference cell J2 (i.e. R2C10) of each sheet (all those 9 sheets) and paste it to the sheet "Data_compiled" to relevant hard coded column as per below.
    Please Login or Register  to view this content.
    So the outcome would look like below:
    Please Login or Register  to view this content.
    I hope this helps and does not make things more confusing.
    Last edited by rain4u; 05-07-2012 at 12:19 PM.

  9. #9
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: VBA. One workbook, 9 sheets, how to transfer data from all sheets to one sheet?

    Quote Originally Posted by arlu1201 View Post
    In the data_shift tab right?
    On all 9 sheets the data in columns B:F is exactly the same.

  10. #10
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: VBA. One workbook, 9 sheets, how to transfer data from all sheets to one sheet?

    After all your detailed explanation, i really owe you this one. Try this
    Please Login or Register  to view this content.
    Copy the Excel VBA code
    Select the workbook in which you want to store the Excel VBA code
    Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
    Choose Insert | Module
    Where the cursor is flashing, choose Edit | Paste

    To run the Excel VBA code:
    Choose Tools | Macro | Macros
    Select a macro in the list, and click the Run button.
    Last edited by arlu1201; 05-08-2012 at 10:49 AM. Reason: Missed out 1 declaration.

  11. #11
    Registered User
    Join Date
    02-01-2011
    Location
    California, Sacramento
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: VBA. One workbook, 9 sheets, how to transfer data from all sheets to one sheet?

    arlu1201,

    Just for info, I am using Excel 2002, and I had to modify your code to make it work. I'm still learning so I was wondering if you overlooked it or something different between xl2002 and 2003?

    Please Login or Register  to view this content.
    BTW the way along with the other moderators and members you do Awesome work.
    Thank you.

    Mike

  12. #12
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: VBA. One workbook, 9 sheets, how to transfer data from all sheets to one sheet?

    Oops i missed that declaration. Will update my code right away. The area you marked in red in your post does not have anything to do with 2002 or 2003. The Option Explicit at the top of the code would have triggered an error msg.

    Thanks for your appreciation !!!

  13. #13
    Registered User
    Join Date
    02-01-2011
    Location
    California, Sacramento
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: VBA. One workbook, 9 sheets, how to transfer data from all sheets to one sheet?

    arlu1201,

    Thank you for the clarification. I didn't think it had to do with the version, but as a novice I wanted to make sure I wasn't missing anything.

    Mike

  14. #14
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: VBA. One workbook, 9 sheets, how to transfer data from all sheets to one sheet?

    Hi
    I just got home. This code works amazingly.
    arlu1201 you really made my day. It's mind boggling that you were able to do it essentially with only 20 lines or so. I thought its going to be a long long code. I will most definitely try to learn the whole code. I want to get better and use this knowledge in future. Its also great to see that other people might find this code useful or at very least can learn from it.
    This will now allow me to go on with my project and generate some nice charts and statistics.


    Thanks again. This is much appreciated.

  15. #15
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: VBA. One workbook, 9 sheets, how to transfer data from all sheets to one sheet?

    You are welcome, glad it worked. Please mark it solved

  16. #16
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: VBA. One workbook, 9 sheets, how to transfer data from all sheets to one sheet?

    Done!
    Thanks again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1