I am needing to create a macro that will sum up a sheet based on number that only start with 30, 39, 40, 48, & 35. This sheet will need to reflect the entire row that falls within those numbers. The column with those numbers is "C".Sample File.xlsxAttached is a sample of the Data I am working with. Any help is appreciated!
Will it be for Columns H, I and J? Do you want a total for each number or a total for all?
Just for I is what I am wanting to sum up, but for each warehouse. (EX LB has 5000, LC has 7500...)
How did you come up with 5000 for LB? What I have to offer is returning $75,0361.00 for Column I.
The 5000 was just an example, not a realistic number in this sheet. Sorry for the confusion. I return the 750,360lbs for all the numbers that fall in the criteria.
What I am trying to accomplish on this sheet it to get the amount (Lbs) in each Warehouse for each part number.
How many warehouses will you have. This is what I used to come up with that number? Also where would you want the results too go?
Please Login or Register to view this content.
There are approximately 71 warehouses. I will run it. If they could be created on a new sheet on the workbook that would be fine. Thanks for you help!
Can you provide a list of warehouses?
That worked for getting the total of all. Attached is the modified workbook. The macro I am wanting will take everything other than the part numbers I am wanting. Then I can sort in a pivot table, unless a macro can do that as well...Sample File.xlsx
here is the list of WH'sList of WH's.xlsx
Try:
Note: Added a Sheet called warehouses and a Sheet called totals -Please Login or Register to view this content.
Thanks John! Would there be a way instead of deleting the rows, to just move the needed data to the Formatted tab?
I started to copy the Raw Data sheet to the Formatted Tab and then run the macro off of that. Would that be of use? Anyway I'll have to revisit in morning, I'm out for today.
Ok, thanks John! Its not big deal if not. I tried plugging in the macro on my actual sheet and it is still running. It has around 40,000 lines on it. Its been running for about 8 minuets now. Maybe there would be a quicker way to go about this? Not sure, but thanks for all your help! Have a great evening!
Try:Originally Posted by mort.marshall.20
Please Login or Register to view this content.
Thanks John! Getting an "Expected End Sub" & "For control Variable already in use" error when trying to run.
Also is the Warehouse and Total page needed to perform this?
Add End Sub at the very bottom. I think I missed it when I copied and posted it.
Actually I missed a couple of lines. Try.
Please Login or Register to view this content.
Thanks John! That works great! It runs it very quickly but now it is bogging down once it is complete. Any way to fix that?
The warehouse total count was requested. You don't need pages but the lists of warehouses need to be stored somewhere, and the resultant count needs to be placed somewhere.
What do you mean bogging down once it is complete?
Well after its complete, switching from tab to tab takes forever, but if I save it and reopen it I have no issue's until I run the macro again.
Do you mean manually activating Tabs is slow? What happens if you just save it?
Yes, manually clicking on the tabs. After I saved it nothing changed, but after I close and re open there are no issue's. Also sorry to ask, but can you change the code back to where there is no formatted page? I am sorry to keep changing it, 1 of these days I will get this figured out...haha
You mean you want the rows deleted instead?
Yes, sorry. I will not need the other data, just the info that falls into that criteria. Thanks!
Does the code in Post #12 work then?
Yes, but it is really slow, takes 24 minuets to run.
Is this any better?
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks