Copy Paste Macro in Excel – using a macro in Excel to copy and paste data in a new worksheet and also change the worksheet name
This week’s hint and tip is on how to create a copy paste macro in Excel. Macros are a very useful and powerful feature in Excel that can have many uses. They can be used to do simple tasks or something a bit more complicated. Both are to help save you time in your spreadsheets and automate certain tasks you do regularly. We cover macros in our Master Class Excel Silver Training course but we also decided to do a hint and tip on it to show this different example. We are going to go through it now below.
Copy Paste Macro in Excel with VBA Rename
In this blog we’re looking at a way of both copying and pasting the data on an active sheet and then renaming the worksheet to take the content of a selected cell. The video further below in the post shows this demonstration in practise but here is the methodology behind it.
Objective
The first screenshot is of the list for the data validation drop down list on B1. The second screenshot is of the worksheet with the example in it.
By firstly selecting a month from the list drop down in cell B1 and clicking on the button, a macro is run to create a copied worksheet with the month selected in B1 and then that same worksheet is renamed to the month name in B1
VBA code
Main lines of code explained:
- First 2 lines simply copy the cells in the range on the Data Sheet
- Third line create a new sheet before Data Sheet
- Automatic widening of columns to source widths
- Paste of data range of cells to new sheet
- Rename new sheet taking the value of cell B1 contents
For more techniques and a thorough introduction to VBA programming see our Master Class Excel Silver course contents on our website here.
Worked example video
The video below shows you how you can use this feature in the spreadsheet in the video. We hope that you find the video useful and enjoy learning about it!
Take a look below at the video to find out more and then try it out on your own computer!
We hope you have enjoyed this hint and tip on copy paste macro in Excel. Why not take a look at our previous video hint and tip on conditional formatting with dates in Excel?