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.
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.
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]
Hi I couldn't post all the sheets as it was to big, but the original does have these sheets.
Sorry
Try this code
Please Login or Register to view this content.
Last edited by arlu1201; 07-20-2012 at 03:38 AM.
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
OK, i have edited the code to reflect the change.
Is it working fine at your end?
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.
To retain the formats, here is the updated code -
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.Please Login or Register to view this content.
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 fromto thisFormula:Please Login or Register to view this content.
Drag down where required or do copy pastespecial -formulas.Formula:Please Login or Register to view this content.
If you have more questions, let me know.
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.
On which line is it giving you the error?
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
Is your sheetname Driver_Analysis or something different?
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.
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.
Just type in the sheet name here how it looks in your file.
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.
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.
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?
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
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.
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
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
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.
Should i use the same sample file as before?
Is there a way that i can post the full schedule. It is quite big then you can see what it does.
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.
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.
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.
Post number is 1.
I have no idea how to zip a file so once i have figured it out i will post it
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.
Here is my attachment
Re: Coping certain sheets in workbook into one sheet
Hi could you also remove the attachment on post 3 of the above.
Hi
Did you manage to sort this out.
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
I have removed the attachment from that thread post 3. By the way, is that thread solved?
Will review this attachment now.
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.
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.
Hi
I just tried it and it does not sort or group with totals at all now.
Terri
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 -
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
The updated code is here -
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.Please Login or Register to view this content.
Its column B I think it has something to do with the formula I have here.
Updated code -
Please Login or Register to view this content.
Thanks It is sorted now.
I just posted a new thread on password protecting if you can help.
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.
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?
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
New file attached
Hi
Please find attached the revised schedule. I would appreciate it if anyone can help me with all this as per above.
Thanks
Terri
Have a few questions -
The subtotals should be removed for column G and averages put in?In column G - total to be an average of the cells with numbers in.
The KM subtotal is not a part of the regular subtotals right?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 .
So to exclude D6 & D8, the macro will have to check column G. If its blank, exclude the cell in column D?
Sorry, i dont understand.You can leave this one for now.
Do you want the additional code to do the formatting?
I meant don't worry about the added total excludingD6 & D8. Yes I would like the additional code to do the formatting.
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.I meant don't worry about the added total excludingD6 & D8
(Column A is an example here).
Okay cool. I need to find time to do a macros course!!!!
So what are the conditions ?
check column "F" - if its blank, then exclude, else include,
Sorry, i missed this one. Have you solved this yet?
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.
Is there a way to copy only the total lines to a new work 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
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
okay will do thanks
How would I attach the code in a new post?
Check my signature on how to put the code tags.
Sorry I have no idea what you mean???
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.
Okay I will Try thanks
I guess the only parts pending for this thread are the formatting and the conditions for populating the subtotals right?
Yes I was going to copy it over to the new thread.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks