Видео

I'm glad it got you closer, thanks for your feedback.

Yes works on any type of computer.

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

Yes as you add new data the Current Region will grow. So it is fully dynamic.

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.

Most welcome.

@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.

Dat is geweldig maat. Ik ben zo blij dat je een manier hebt gevonden. Het allerbeste met je leerproces.

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.

Of course it should be on the website now - just under the video is the exact file I used. All the very best.

You can go onto my website thesmallman.com my contact details are in the footer.

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.

Thanks for the response.. But what I am asking is how to make the address of a cell as variable. Not saving the value of a cell to a variable

@@not1AM I do not understand what you are asking. How do you know where the cell is unless you declare it?

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.

That is a rock star comment. So grateful to hear and happy you like them. Thanks mate!!!

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.

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

It is the very bottom cell in Excel. Start at the very bottom and come up to the last used cell.

You are most welcome.

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.

Probably the nicest comment I have ever read about my videos. Thanks ever so much. I will keep trying to provide good quality solutions. Take care.

I have made many videos about how to make dashboards exactly like this. They are all on my website.

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.

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.

Yes it is possible to connect a table to a slicer. Slicers provide the option to connect to a Pivot table or an Excel table.

This is awesome. Glad I could help.

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.

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.

I have this issue too.

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.

You're very welcome!

Much appreciated!

My pleasure!!!!

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.

Glad it helped!

Excellent! Glad it helped.

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.

With a great deal of patience and persistence.