Hi Excellers, welcome back to another instalment of #macromonday. Let’s look at how to save a tonne of time by writing a simple Excel VBA Macro that will copy an entire worksheet from one Excel workbook to another. I find this macro useful on a regular basis. Every Monday, I copy a specific worksheet from one of my master reports to another new Excel workbook. This is specifically for users to have access to in another folder on my OneDrive.
I have a regular Weekly Sales Report, which is a standard Pivot Table, updated every Monday morning. I need to send this to a sales team at some point Monday mornings. They only need a copy fo the First Worksheet with the Pivot Table (worksheet named ‘Sales Summary’) to a new Excel workbook which I then save and send to the sales team that need it. This is really one handy macro, so let’s get coding with an example and some sample sales data.
Let’s Write The Macro.
This really is what I call a ‘One Liner’, well technically its a few more lines, but you get the picture. First of all, you need to decide where you are going to save the Macro. This is important as you need to establish if your Macro is to be used only with Excel workbook, or you may want to make it available to ANY workbook that you may need to use this process. I know that I use the code in MANY workbooks.
I want to create a New Module to store the code in my Personal Macro Workbook. This allows me to run the code when Excel is open. If you want to read more creating, maintaining and using your Personal Macro Workbook then feel free to read my corresponding blog posts below.
Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook
Macro Mondays -Creating and Updating Your Personal Macro Workbook
Step1. Create a new module. I have created one in my Personal Macro Workbook. (see above). You can easily do this by either hitting ALT+F11 to open the Developer Tab or select Developer Tab | Visual Basic
Step 2. Start by typing Sub then the name of your Macro. I have named mine CopyActiveSheet_NewWorkbook. It describes exactly what the code will do. This is useful to come back to and reuse if the name if descriptive of what the code does.
Step 3. Once you hit enter after typing the Macro name Excel automatically, puts End Sub at the end of your code. All you need to do is enter the following line.
ActiveSheet.Copy
Yes, that is is it guys, once single line of code.
Step 4. Test your Macro. This is my favourite part. once you run the code, the sheet you are in on your original workbook (the active sheet), is simply copied to a new workbook. You can now name and save this workbook. In my case, I rename it with the current date and send to my team. Easy!
How simple is that?
What Next? Want More Tips?
So, if you want more top 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.
Do You Need Help With An Excel Problem?.
I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST.
