Hello Excellers and welcome back to another blog post in my #macromondays 2019 blog post series. Today lets look at how to really quickly copy some filtered data to a new Excel workbook with the click of a mouse. Of course, we are going to write a small bit of VBA code to do this. So, let get on with it and get coding and write this Excel macro.
Have you been working with a set of data that you have displayed using the Excel AutoFilter function?. Have you wanted to keep and use that data on another new Excel workbook?. You can copy and paste the data manually but we can write a small macro we can use over and over again so why not?.
Preparing To Write The Macro
First, you will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result. You then have a choice, you can either create a module to store your code either in your Personal Macro Workbook or in your current workbook. What’s the difference?. If you save the macro in your Personal Macro workbook it will be available for use in any of my Excel workbooks. If you store it in the current workbook then use is restricted to that workbook.
As you can see this macro will be useful to reuse in any workbook with links that require analysis. Therefore I will create and save this macro for future use in my Personal Macro Workbook.
Learn More About Your Personal Macro Workbook (PMW)
If you want to read more about your Excel PMW then check out my blog posts below.
Macro Mondays -Creating and Updating Your Personal Macro Workbook
Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Starting The Macro
We need to start off the macro by inserting a New Module. Do this by selecting the Personal.xlsb workbook, then Insert Module. Type Sub then the name of your macro. In this example, I have called it simply Sort_Worksheets. Notice that Excel will automatically enter the end text End Sub to end the Sub Routine. We simply need to enter the rest of the code between these two lines.
[stextbox id=’info’]
Sub CopyFilterData()
End Sub
[/stextbox]
Check For The AutoFilter
First, we check to see if Excel has the AutoFilter turned on. If it is not then there will be no filtered data to copy. The code will then stop and the macro exited at this stage if the AutoFlter is off.
[stextbox id=’info’]
If ActiveSheet.AutoFilterMode = False Then Exit Sub
End If
[/stextbox]
Copy The Filtered Data To A New Workbook
The data in the filtered range is then copied to a newly added workbook and pasted into that new workbook.
[stextbox id=’info’]
ActiveSheet.AutoFilter.Range.Copy
Workbooks. Add Worksheets(1).Paste
[/stextbox]
Ending The Macro
Once all of the filtered data has been pasted the routine now ends.
[stextbox id=’info’]
End Sub
[/stextbox]
Test Your Excel Macro
Now all you need to do is test your Excel Macro. This is my favourite part!.
What Next? Want More Tips?
So, if you want more tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Macro Monday series. Click on the link below
Macro Mondays Blog Posts.
Do You Need Help With An Excel Problem?.
Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST. All you need to do is choose the Excel task that most closely describes what you need to be done. Above all, there is a money back guarantee and similarly Security and Non-Disclosure Agreements. Try It!. Need Help With An Excel VBA Macro?. Of course, you don’t need to ask how to list all files in a directory as it is right here for free.
