When copying an entire Excel worksheet to a new one, how do you retain the macros in the new worksheet?
Tuesday, June 30th, 2009Every time I copy a worksheet, I have to go back and copy the macros from the old worksheet to the new worksheet and then link everything back up… is that necessary?
Rachel’s way is a pretty good way of doing it. However, there is an easier way if you are writing macros or if you are copying the sheets a bunch to new workbooks.
Instead of putting your coding in a regular module, put the code inside of the worksheet. Go to Visual Basic Explorer by pressing Alt + F11 while in Excel. Then go to the Project Window in the top left corner and double-click on the worksheet in your workbook where you want to have the code. Then just paste the code from your module into that worksheet’s code window. Should work and copy over whenever you need to copy the worksheet. So you do the operation once and don’t have to drag or save the file as something else and delete each time. Do it once and you’re done.