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
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?