Hi My name is Marcus Small and I run created a website called theSmallman.com. It is one of the largest Excel websites in the world today and has hit millions of visitors since 2012. It has been a labour of love and I hope to share some of my insights on this channel.
NGL...this is what I needed to see after hours of trying to figure it out with other videos. Only problem now is to figure out how to create new calculated measures!
Hey Marcus, I have a code to create a file based on cell value, however I want to have a directory create by same cell value and the file in the directory. How would I integrate directory creation into the code. Private Sub CommandButton2_Click() 'Show the Save As dialog to allow folder to be chosen Dim FileName As Variant Dim ValCellB2 As String Dim Path As String ValCellB2 = Range("B2").Value Path = "B:\Blend Chemist Data\Profile Approval\" FileName = Application.GetSaveAsFilename(Path + ValCellB2 + ".xlsm", _ "Excel Workbook,*.xlsm", 1, "Confirm or Edit filename and folder!") 'Oops, the user backed out of this so don't save the file If TypeName(FileName) = "Boolean" Then MsgBox "You didn't save your file!" Else 'Ok, all is good, we have the details, so go ahead and save the workbook ActiveWorkbook.SaveAs FileName MsgBox "File Saved!" End If End Sub
Hi Jason I had a play round with it and got it working OK. What I did was put the new folder name and the new file name in cell B2. B2 is: MyFile Name\My Excel File So for example this might be: January\Jan Budget Data Then I just run the following and it produces a new folder called January with a file inside called Jan Budget data.xlsx All ever so good. Good luck with it - worked nicely at my end. Sub CreateFolder() Dim Path As String Dim Fname As String Dim ar() As String ar = Split([b2], "\") Path = "B:\Blend Chemist Data\Profile Approval\" Fname = Path & ar(0) If ar(0) <> "" And Not FolderExists(Fname) Then MkDir Fname End If ActiveWorkbook.SaveAs Path & ar(0) & ar(1) MsgBox "File Saved!" End Sub Function FolderExists(ByVal Path As String) As Boolean On Error Resume Next FolderExists = (GetAttr(Path) And vbDirectory) = vbDirectory On Error GoTo 0 End Function
Is there any way to do this so a slicer filters 2 different tables of data - not pivot tables (they both have the same data in but I want to be able to filter different parts of my dashboard by different things)
You can clearly see I have 2 different tables of data and use the joining method to make the two tables talk to one another through a third table. You have to do as I do to get the same result as I have achieved. Hope that helps.
I was not able to see the pivot table from the other data source even after linking in the data model. Is there something I can do to refresh or make it appear?
@thesmallman 0 seconds ago Please follow the steps exactly as I do them. If we are using Office 365 our versions of Excel are the same so it it does not work for you then you have not done exactly the same steps as what I have done. Re do the video and follow the steps precisely. 😀
Ik heb het gevonden op een ander manier, maar van mij moest heel de tabel gesorteerd worden. Alleen de eerst kolom gaat mee en dat mag niet. Ik bekijk nog eens je filmpje.
Depends what your data looks like. If you don't have data in row 7 then the current region method I shared will work. If you have data in row 7 you don't want included that becomes more involved. You could use the FIND in VBA to locate something unique in the bottom of your dataset and trap that row via a find and that way you never have to change your code provided you always have that unique item in the dataset.
You were almost there. You have to trap the value when you are working out what A will be equal to. Sub test() Dim A As Variant A = [C3].Value MsgBox A End Sub Give the above a try. Should work.
You can't set a range with the UsedRange method. You have to refere to the activesheet in the following way Activesheet.UsedRange. Which looks like you have recorded the macro. The above is more elegant in my opinion.
You and I are both using the same version of Excel so the reason it is not working is your did not follow the instructions carefully enough. It would not have worked for me if I had done what you did. Watch again and come back on here and tell others what you did wrong so everyone learns.
You have not followed the instructions exactly. I'm using office 365 if we are both using the same version of Excel if you copy everything that I do you will get the same result.
That is correct I did not say I was giving this file away at any point. It was for demonstration purposes, to inspire others to go out and build it themselves. You learn nothing from taking someone else's IP. My website thesmallman.com has plenty of free example Excel dashboards.
Great video! Just one question. Can I change the order of the regions? I mean, it is possible to have europe at the top of the slicer, then America, etc? Thank you
Yes. Slicers will order items ascending, descending or from a custom list. Make a custom list and the slicer will order the items based on your custom list. My blog post shows you how. www.thesmallman.com/excel-custom-sort-with-vba
Sorry some files take me months to make, there are dozens of free files that have similar functionality on my site and this can be recreated from scratch.
Love the way you explain things in details through out your videos! So much easier to gain the essence and concept behind the code, not missing out on mentioning what keyboard keys you use and why. Did also watch the video on how to create directory with VBA. Excellent! Thanks, keep up the good work! ;)
Hi Good morning Sir..would just like to ask your assistance on how I'll be able to count the events from a single cell? basically, i have a single cell that changes from "0" to "1" vice versa. (a data from PLC that is connected to excel thru (DDE ) dynamic data exchange. and I want to record how many times it changes from 0 to 1 ...thaNk you
I can't think why that would be an issue. I never connect charts to pivot tables only to Excel ranges. I only use pivot tables for quick summary information or to inform a slicer that I use to inform Excel calculation tables.
You can send a dynamic range between variables as follows Sub SendTO() Dim lr As Long lr = [A1048576].End(xlUp).Row Other lr End Sub Sub Other(TheLR) MsgBox TheLR End Sub This procedure sends the Last Row (LR) from SendTo to the Other procedure. This is how you get a variable to transfer between macros. Hope that helps.
I need further help. I have created both relationships, my slicer has both report connections ticked and yet selecting an option on my slicer filters nothing.
Assuming you are using the same dataset, if you are getting a different result you need to look over the video again. If my end result and your end result are different there is a step you have missed.
Awesome presentation, thank you. I've been working this for a couple of days and in a short time you've taught me how to do it right as well as tools to help in troubleshooting. Thank you.
You just refer to the sheet you want to put the output on before the range reference. In vba it is workbook.worksheet.range.action. I have provided range.action. You just add worksheet.range.action and t hat will get your data on the other sheet.
That's Great. Well explained. Being an Excel VBA beginner I have a confusion. We are trying to load a excel range into memory so we can manipulations it faster. Dictionary is already a memory portion. why first we are transferring our range into an array before adding it to dictionary? secondly what would be fastest lookup Array or Dictionary or Collection?
Hi Imran - a dictionary will only store unique keys so you can't just put an entire dataset into the dictionary. You need to put it into an array then push only the unique items into the dictionary. You determine the breadth of those items that go into the dictionary. Secondly a dictionary is faster than an array and I am not sure what is faster the collection or the dictionary. Hope that helped.
Hi Marcus, everything went fine until the last step. The thing is that only one pivot table(of the two that i had created) is being filtered at my end. I selected both the pivot tables in the report connection dialog box of the slicer but still only one pivot table is being filtered.
Sub Filter_Criteria() Dim Data_sh As Worksheet Dim Filter_Criteria_Sh As Worksheet Dim Output_sh As Worksheet Set Data_sh = ThisWorkbook.Sheets("Data") Set Filter_Criteria_Sh = ThisWorkbook.Sheets("Filter_Criteria") Set Output_sh = ThisWorkbook.Sheets("Output") Output_sh.UsedRange.Clear Data_sh.AutoFilterMode = False Dim Emp_list() As String Dim n As Integer n = Application.WorksheetFunction.CountA(Filter_Criteria_Sh.Range("A:A")) - 2 ReDim Emp_list(n) As String Dim i As Integer For i = 0 To n Emp_list(i) = Filter_Criteria_Sh.Range("A" & i + 2) Next i Data_sh.UsedRange.AutoFilter 2, Emp_list(), xlFilterValues Data_sh.UsedRange.Copy Filter_Criteria_Sh.Range("c1") Data_sh.AutoFilterMode = False End Sub
Hi Cindy - you can use wildcard characters in your code for instance if you wanted all fields with Data it would look like this. [A1:A100].AutoFilter 1, "*" & "Data" & "*" This would trap any line with Data in any part of the field. Hope this helps.
Hi Marcus, I would like to implement this on multiple columns. How can i make it possible? In addition, i want to do it over one sheet on excel workbook. Could you please share me full code from beginning to end point? I really need this code. Thanks
There are plenty of examples of precisely this on my website. See if the following works for you. www.thesmallman.com/copy-sheet-and-save All the very best.
How can you move them back and forth? I want one variable to go into one macro, run a calculation and spit out the output variable back into the first macro. Any insights would be appreciated!
I could be wrong but I don't think that is possible. Your workaround would be to use the second macros result and perform the necessary actions after the result is held in memory. Please post for others if you have a better workaround.
