Macros to run for mutiple workbooks
I have multiple workbook on same type and i have placed in one folder.I want to run one macro for that folder
See also questions close to this topic
Excel VBA: Create Array from Filter Field Items?
A report I am creating in Excel involves several very similar pivot tables needing to be specifically filtered many times (i.e. a Year-to-Date table, a Quarter-to-Date table, etc, all needing to be filtered the exact same way before exported, then filtered again, then exported, etc)
So I looked into VBA as a way of accepting a few filter criteria, then filtering multiple tables that way, before looping.
However, I'm having a very tough time properly targeting PivotTables and specific fields, as it appears an integrated Value field is targeted and filtered via code differently than, say, a "filter' field I have attached to the top of the PivotTables, where they can accept no "begins with", "contains", etc, strings. They are just checkboxes, and one or multiple can be selected.
So it's one thing for me to tell it via VBA to select one item, and having it select all but one item. The latter requires the code to target every single possible value, but not the one that I want excluded.
My idea for this, then, is to create an array from every possible existing value in this filter field, then going through a loop where each value is added to my code as a value to check.
I have some code so far:
ActiveSheet.PivotTables("QTD_Pivot_By_Category").PivotFields( _ "[Range].[Address_1].[Address_1]").VisibleItemsList = Array( _ "[Range].[Address_1].&", "[Range].[Address_1].&", "[Range].[Address_1].&" _ , "[Range].[Address_1].&[INC]", "[Range].[Address_1].&[KRT]", _ "[Range].[Address_1].&[LTD]", "[Range].[Address_1].&[RPO]", _ "[Range].[Address_1].&[ INC]", "[Range].[Address_1].&[CORP]", _ "[Range].[Address_1].&[INC.]", "[Range].[Address_1].&[LTD.]", _ "[Range].[Address_1].&[LTEE]", "[Range].[Address_1].&[PAWS]", _
Now, if I just record this macro from actions in Excel, and do "select All", then de-select the one I don't want, it will error. It errors because it's selecting ~300 values, and while it's 'writing' this code, it errors when it hits the limit of "_" delimited breaks in one straight line of VBA code.
If my field is called "Address_1" as above, part of the range..."Range" (not sure where that's defined or why, but it works), can I get some help as to the most efficient way to define said ".VisibleItemList" as all POSSIBLE items in the list from a dynamic array rather than needing to be selected manually? This list will be different day-to-day so it can't just be a hardcoded flat list.
Ideally, also in a way that circumvents the max limit on "_" line breaks in a line of code in VBA for Excel.
If it's of any use for context, my table looks like this. See that checkbox drop-down? I want a snapshot of every updated value sitting in there to be put into an array and then iterated upon being added in a way similar to my example code:
Edit: Since that filter field's values are being pulled from a local datasource, I decided to just grab those and make an array that way! So I'm starting my code this way:
Dim OGDataRange As Range, OGDataLastRow As Long Dim ValueArray As Variant OGDataLastRow = Worksheets("DATA QTD").Range("U2").End(xlDown).Row Set OGDataRange = Worksheets("DATA QTD").Range("U2:U" & OGDataLastRow) ValueArray = OGDataRange.Value
"ValueArray" is now my array. So I need help one-by-one pulling the values of this array, and adding them to my VisibleItemList as seen above.
Thank you so much for any assistance.
VBA Excel - Sort a daily delivery manifest into groups based on complex rules
I am trying to come up with a way to sort my Excel-Table. I wrote a very basic VBA to do this when it was a random sort only. Now there's complex parameters/rules I have to meet and it's way outside my skillset.
The problem: I receive a daily file with a list of items via shipments like the example below. The list can have as few as 1 and as many as 24 items. I have to sort these by restaurant.
Example Original List ITEM SHIPMENT Oranges 1 Apples 1 Grapes 1 Pears 2 Pork 3 Chicken 4 Rice 5 Peas 5 Beans 5 Water 5 Corn 5 Milk 5 Eggs 5 Salmon 6 Tofu 7 Juice 8 Cheese 8 Salt 8 Pepper 9 Onions 10 Oats 11 Barley 11 Kale 11 Chips 12
The items need to be sorted out to 6 restaurants and there are complex rules:
- No Restaurant can have more than 1 item from a shipment
- No Restaurant can have more than 4 items
- Restaurant 1 always gets the first two items (Items 1-2)
- Restaurant 2-5 evenly gets the next 16 items (Items 3-18)
- Restaurant 6 gets the next 2 items (Items 19-20)
- Restaurant 1 and 6 then evenly get the last 4 items (Items 21-24)
- If there are more than 6 items in a shipment (more items than restaurants) the extra items stay in the warehouse.
- The Overall Rules override the sorting rules. For example in our example list Restaurant 1 cannot have both Oranges and Apples since they are from the same shipment so the sort changes.
Example Sort Restaurant 1 Shipment Oranges 1 Pears 2 Rice 5 Kale 11 Restaurant 2 Apples 1 Peas 5 Salmon 6 Salt 8 Restaurant 3 Grapes 1 Beans 5 Tofu 7 Pepper 9 Restaurant 4 Pork 3 Water 5 Juice 8 Onions 10 Restaurant 5 Chicken 4 Corn 5 Cheese 8 Oats 11 Restaurant 6 Milk 5 Barley 11 Chips 12 Warehouse Items Eggs 5
Looking at it as a whole now I'm not even sure this is possible and I have no idea how to go about doing it. If anyone has any input I'd love to hear it. Thank you so much for your help.
R: rulled data frame to automate changes on it
I would like to create a sort of data.frame where I can add rules on it just like excel tables. For instance, if I work with symetric 2x2 matrices, I would like that if I change the value at [1,2] then the value at [2,1] will be automatically changed to the same value. I have search for this but I cannot find anything related. Any help is appreciated.
Excel Macro to Split Cell in Multiple Cells
I'm new in vba and I'm trying to create a macro to slip the name of a directory and then create a filter. The names of the directorys are in a column and I want to split the last name.
I want to take
and then split it in to multiple cells
011.201 2016 CTE AditivoDeConvenio 01 R00 FFF
And then create a filter
Increment Value On Basis of Count with Button Click |Excel|VBA|
I want the Value should be Auto Printed Number of Times When Count is Given
[My Image 1]: https://i.stack.imgur.com/Svm8q.png
If you see my image there is Count and Value as some Data like 'DEMO' When i enter count like 5 then the Data in Value = Demo should be printed that many times as Count is mentioned Under Column => H
Like Below Output Image [My Image 2]: https://i.stack.imgur.com/kKRS2.png
I can Drag the cell upto particular Count... this may work for me But when the count is 5 or 10 ... what if the Count is 100 or 200 or more We have to drag the cell upto 100 count and so on
i want to auto increment without Dragging the cell upto the Particular count
I have tried this one But not Working ... Suggest me How do i achieve this
Sub Increment() Dim startValue startValue = Cells(15, 5).Value For i = 1 To 10 Step 1 Cells(i, 5).Value = startValue startValue = startValue + 1 Next i End Sub
here is the Google Drive file link would you be able to illustrate on this and share https://drive.google.com/file/d/1D5GblPrcnaGQqKUwhj5YIthhgQxkHldl/view?usp=sharing
How to send email using MS Outlook Template (.oft), combined with variable values inserted into body of email (through Excel or otherwise)
I am not savy at these things so I'll explain the best I can...
Simple example, A Interview confirmation email in Outlook
Your interview with [B] is confirmed for [C]. The address is [D].
A-First Name B-Company Name C-Date & Time D-Physical Address
I've seen people use excel for this but I want to be able to just enter A, B, C and D values somewhere, click send and that's it.
All the code I've come across can achieve variable factors but hardly any had codes for the body of the email and if they did NONE of them incorporated .oft files.
So I'm a little lost. Much help is appreciated!!