+ Reply to Thread
Results 1 to 74 of 74

Coping certian columns from numerous sheets in workbook to a new sheet

  1. #1
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Coping certian columns from numerous sheets in workbook to a new sheet

    Hi

    I would like to copy columns A,B,D,E,F,G and H from worksheets 1 - 31 into a new sheet called DRIVERS ANALYSIS leaving out blank rows and sorting by driver name (Row D). Can anyone help please? I have attached a sample of the file.
    Last edited by arlu1201; 08-15-2012 at 06:31 AM.

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

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Your workbook does not have sheets 1 to 20. How do you want to tackle that? Check if the sheet exists, if not, go to the next sheet?
    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]

  3. #3
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Hi I couldn't post all the sheets as it was to big, but the original does have these sheets.

    Sorry

  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: Coping certian columns from numerous sheets in workbook to a new sheet

    Try this code
    Please Login or Register  to view this content.
    Last edited by arlu1201; 07-20-2012 at 03:38 AM.

  5. #5
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Hi the new sheet can not be called trucks as I already have a sheet called that. I need it to be called driver analysis

  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: Coping certian columns from numerous sheets in workbook to a new sheet

    OK, i have edited the code to reflect the change.

    Is it working fine at your end?

  7. #7
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Hi yes it seems to work fine I just need to get it to sort the list by driver. It is also making the date column into numbers instead of dates and the diesel consumption format is general instead of numbers. Is there away that it can group each section by driver and give me a totals line. I also need to get rid of the div/0 issues and rather have a blank here.

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

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    To retain the formats, here is the updated code -
    Please Login or Register  to view this content.
    I have changed the sheetname from Driver Analysis to Driver_Analysis, since the space in between the name was creating an issue. I hope that is fine at your end.

    Currently, your driver column looks blank, but i can provide you the code for that. For which field(s) do you need the totals line?

    To remove the #DIV/0 errors, i can remove them from this page through code, but i did see these errors in your entire workbook. You can remove them from the 1-31 worksheets and they will move off from this page too.

    To remove the #DIV/0 errors from the 1-31 sheets all at once, select all the sheets and edit each formula as shown below. The changes will be reflected in each selected sheet and this is a 1-time activity.

    Change the formula from
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    to this
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Drag down where required or do copy pastespecial -formulas.

    If you have more questions, let me know.

  9. #9
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Hi

    It is giving me a run time error '9' Subscript out of range. No idea what this means.

    I would just need totals on the Km and diesel consumption columns per driver.


    Thanks for the div/0 errors help as the other way suggested by someone removed all errors which was not ideal.

  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: Coping certian columns from numerous sheets in workbook to a new sheet

    On which line is it giving you the error?

  11. #11
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Worksheets("Driver_Analysis").Range("A4:G4") = Split("Date,Trucks,Driver,KM,Diesel Req No,Diesel Filled (l), Diesel Consumption", ",")

    When you say debug it highlights this line in yellow

  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: Coping certian columns from numerous sheets in workbook to a new sheet

    Is your sheetname Driver_Analysis or something different?

  13. #13
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Quote Originally Posted by arlu1201 View Post
    Is your sheetname Driver_Analysis or something different?
    I am not sure I understand what you mean as I thought you had given it that name. It's a new sheet in my workbook

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

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    I just wanted you to check if the case was proper as well. In the sense, it should be "Driver_Analysis" and not Driver_analysis or any other form. The subscript out of range is related to the sheetname itself. Nothing else.

  15. #15
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    No idea how do I check. The sheet is created when i run the macro
    Last edited by TERRI LEE; 07-20-2012 at 11:15 AM.

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

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Just type in the sheet name here how it looks in your file.

  17. #17
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    It creates a sheet called driver analysis but with nothing in it. If you go to run the macro there it is called consolidate_trucks. I don't know if that's the issue.

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

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    I am sorry, i missed out on changing one line of code - delete that sheet and try this updated code -
    Please Login or Register  to view this content.

  19. #19
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Thanks it worked now but it still does not sort by driver. Is that possible? is it also then possible to do totals as discussed by driver section?

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

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Yes, since you were facing issues with the sheetname, i didnt create that code for you.

    I will provide it to you shortly, add it to the end of the earlier code, before the line Application.ScreenUpdating = True

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

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Ok, got a final code for you

    Please Login or Register  to view this content.
    Last edited by arlu1201; 08-16-2012 at 06:11 AM.

  22. #22
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Arlette

    Wow!!! You have no idea how much time this saves and it works so well!!! Thanks a million. I wish I was this capable.

    Terri

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

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Am glad it worked.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

  24. #24
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Re: Coping certian columns from numerous sheets in workbook to a new sheet



    Hi Arlu

    I tried running this Macro on a full month now and there is an issue in that is starts to sort the entire document and give totals per driver but somewhere along the line it just stops. So only half the documents is sorted with totals and the rest is not. It is also not pulling thru the detail for column B. It just leaves blank cells.


    Please, please help. I need to finish this today.

    Thanks
    Terri
    Last edited by TERRI LEE; 08-15-2012 at 05:30 AM.

  25. #25
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Quote Originally Posted by TERRI LEE View Post
    Re: Coping certian columns from numerous sheets in workbook to a new sheet



    Hi Arlu

    I tried running this Macro on a full month now and there is an issue in that is starts to sort the entire document and give totals per driver but somewhere along the line it just stops. So only half the documents is sorted with totals and the rest is not. It is also not pulling thru the detail for column B. It just leaves blank cells.


    Please, please help. I need to finish this today.

    Thanks
    Terri
    Please can somebody help?

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

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Should i use the same sample file as before?

  27. #27
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Is there a way that i can post the full schedule. It is quite big then you can see what it does.

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

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    As long as there is no sensitive data and it will help us work out a solution for you, you can zip the file and upload.

  29. #29
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    No there isn't so i will try to do that. Is there a way that i can remove my previous attachment as there was in that one. Stupid me.

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

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    If you see the Edit Post option, then you can click on that, go to go advanced and click on manage attachments and when the popup window appears, you can click on remove.

    If you are not able to see the option, just give me the post number and i can remove it for you.

  31. #31
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Post number is 1.
    I have no idea how to zip a file so once i have figured it out i will post it

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

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    I have removed the attachment.

    You can try removing the sheets which are not required for this question from your sample file. If it still does not reduce the file size to 1000kB which is the max allowed on the site, then you can try using a zipping utility like Winzip to zip your file.

  33. #33
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Here is my attachment
    Attached Files Attached Files

  34. #34
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Re: Coping certain sheets in workbook into one sheet

    Hi could you also remove the attachment on post 3 of the above.

  35. #35
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Hi

    Did you manage to sort this out.

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

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Quote Originally Posted by TERRI LEE View Post
    Re: Coping certain sheets in workbook into one sheet

    Hi could you also remove the attachment on post 3 of the above.
    There is no attachment in post 3. Do you mean post 33?

  37. #37
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Hi

    Sorry I have caused confusion. The post in 33 is the new one for you to work on.

    Post 3 is under the thread of Re: Coping certain sheets in workbook into one sheet

    Terri

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

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    I have removed the attachment from that thread post 3. By the way, is that thread solved?

    Will review this attachment now.

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

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    I have edited the code in post 21. There was 1 line missing in the code, hence the data was not getting sorted and subtotalled properly.

  40. #40
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Hi

    Thanks for removing the thread. Yes it has been solved thanks so much.

    Terri

    ---------- Post added at 08:35 PM ---------- Previous post was at 08:33 PM ----------

    Hi

    Thanks I will try it tomorrow in the morning and let you know how it works.

  41. #41
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Hi

    I just tried it and it does not sort or group with totals at all now.

    Terri

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

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    I put the code in a standard module of the file you gave me in post 33 and ran the macro from post 21.

    See attached - it gives the subtotals -
    Attached Files Attached Files

  43. #43
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Hi

    I took the macro in your work book and used it and now it seems to work thanks. Is it possible to make it able to give totals on the diesel filled and consumption columns as well. Sorry it also does not pull thru the truck registration numbers.

    Thanks Terri

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

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    The updated code is here -
    Please Login or Register  to view this content.
    Which is the field containing the truck registration number? As per your instructions, i am currently pulling in columns A-B, D-H into the Driver Analysis tab.

  45. #45
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Its column B I think it has something to do with the formula I have here.

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

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Updated code -
    Please Login or Register  to view this content.

  47. #47
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Thanks It is sorted now.

    I just posted a new thread on password protecting if you can help.

  48. #48
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Hi

    If I want to do a bit of tidy up work on the worksheet that this Macro makes, is it possible for me to add this to the Macro and where do i do it. Eg I would like to show borders thru out the work sheet. I would also like the Totals row to be in a different colour and Bold. Can we also change the Macro to give an average total in the diesel consumption column instead of just a total. This needs to read of from the total row as I don't want it to average rows with nothing in them. I also then want to add a column that works out the % over the fixed average of "2" per row.

  49. #49
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Hi

    Is there a way that if I double click on a cell within the sheet created that it will take me to the cell that the information originally came from?

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

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Quote Originally Posted by TERRI LEE View Post
    Hi

    If I want to do a bit of tidy up work on the worksheet that this Macro makes, is it possible for me to add this to the Macro and where do i do it. Eg I would like to show borders thru out the work sheet. I would also like the Totals row to be in a different colour and Bold. Can we also change the Macro to give an average total in the diesel consumption column instead of just a total. This needs to read of from the total row as I don't want it to average rows with nothing in them. I also then want to add a column that works out the % over the fixed average of "2" per row.
    Can you upload a sample of how you want your data formatted? You can also add pointers so it can speed up the work for us.

    Quote Originally Posted by TERRI LEE View Post
    Hi

    Is there a way that if I double click on a cell within the sheet created that it will take me to the cell that the information originally came from?
    You can do this using a pivot.

  51. #51
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Hi

    I have attached a copy of the new columns I need (just battling with the calculations a bit).

    In column G - total to be an average of the cells with numbers in.

    Add in columns H - M as per schedule. It must automatically work.

    Then over all I would like the document to have the total lines in Bold Red as I have done here manually. I would like the font to all be sized to "8" as well as the borders done as I have here.

    Under each name section I would like to have another total cell that is a the total of kilometers added only where fuel is actually put into the vehicle. So this means on the first name section i.e Alphabert ,the amounts that will be excluded would be cells D6 & D8. Maybe an extra row per name section should be added that reflects this amount .


    I hope this can be achieved.

    Thanks
    Terri
    Last edited by TERRI LEE; 09-10-2012 at 04:34 AM. Reason: info incorrect

  52. #52
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    New file attached
    Attached Files Attached Files

  53. #53
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Hi

    Please find attached the revised schedule. I would appreciate it if anyone can help me with all this as per above.

    Thanks
    Terri
    Attached Files Attached Files

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

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Have a few questions -

    In column G - total to be an average of the cells with numbers in.
    The subtotals should be removed for column G and averages put in?

    Under each name section I would like to have another total cell that is a the total of kilometers added only where fuel is actually put into the vehicle. So this means on the first name section i.e Alphabert ,the amounts that will be excluded would be cells D6 & D8. Maybe an extra row per name section should be added that reflects this amount .
    The KM subtotal is not a part of the regular subtotals right?
    So to exclude D6 & D8, the macro will have to check column G. If its blank, exclude the cell in column D?

  55. #55
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Quote Originally Posted by arlu1201 View Post
    Have a few questions -

    The subtotals should be removed for column G and averages put in? Yes

    The KM subtotal is not a part of the regular subtotals right? I would like it to be.
    So to exclude D6 & D8, the macro will have to check column G. If its blank, exclude the cell in column D?
    You can leave this one for now.

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

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    You can leave this one for now.
    Sorry, i dont understand.

    Do you want the additional code to do the formatting?

  57. #57
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    I meant don't worry about the added total excludingD6 & D8. Yes I would like the additional code to do the formatting.

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

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    I meant don't worry about the added total excludingD6 & D8
    This can be done by a macro, as long as you explain the conditions. For e.g. check column "A" - if its blank, then exclude, else include, etc.
    (Column A is an example here).

  59. #59
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Okay cool. I need to find time to do a macros course!!!!

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

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    So what are the conditions ?

  61. #61
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    check column "F" - if its blank, then exclude, else include,

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

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Sorry, i missed this one. Have you solved this yet?

  63. #63
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    No not yet. I need alot of help with this schedule so I am considering trying to find someone who can come to site and work with me on it. Any ideas will help.

  64. #64
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Is there a way to copy only the total lines to a new work sheet?

  65. #65
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Hi

    Can someone help modify the macro so that in column G instead of the total per driver we have the equation =D26/F26.

    I will need this to happen on each drivers total line in column G

    Thanks

  66. #66
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    I suggest you start a new thread with this new question ( eventually referring to this one) I'm afraid this thread is getting too long winded to be noticed by members

  67. #67
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    okay will do thanks

  68. #68
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    How would I attach the code in a new post?

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

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Check my signature on how to put the code tags.

  70. #70
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Sorry I have no idea what you mean???

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

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    When you are posting code in your new thread, select the code and copy it. Then use [noparse][code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] after it. I have this in my signature below each post.

  72. #72
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Okay I will Try thanks

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

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    I guess the only parts pending for this thread are the formatting and the conditions for populating the subtotals right?

  74. #74
    Forum Contributor
    Join Date
    05-31-2012
    Location
    South africa
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Coping certian columns from numerous sheets in workbook to a new sheet

    Yes I was going to copy it over to the new thread.

+ 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