0

I have a template sheet that I copy with the following code where strNameOfSheetToCreate is a string captured from a cell :

Dim Wb As Workbook: Set Wb = ThisWorkbook
Wb.Sheets.Add.Name = strNameOfSheetToCreate

Dim SheetSource As Worksheet
Dim SheetTarget As Worksheet
        
Set SheetSource = Wb.Worksheets("Project Template")
Set SheetTarget = Wb.Worksheets(strNameOfSheetToCreate)
    
'copy all formulas and conditional formatting from template
SheetSource.Cells.Copy
SheetTarget.Cells.PasteSpecial Paste:=xlPasteAllMergingConditionalFormats

This copies the sheet correctly but doesn't copy over any of the macro code from the template sheet, such as the activate macro as below:

Private Sub Worksheet_Activate()
        Dim ThisSheet As Worksheet
        Set ThisSheet = ActiveSheet
        ThisSheet.Range("H40").Formula2 = "=FILTER(FILTER(AllStaffProjectAllocationTbl,AllStaffProjectAllocationTbl[Project Code Name]=B2), {1,0,0,1,1,1,0,0,0,1,0,0,0,0,0,0,0})"
End Sub

Is there a way to also copy over the macro code to the new sheet?

Automation Monkey
  • 355
  • 1
  • 8
  • Might be something useful here: https://stackoverflow.com/questions/40956465/vba-to-copy-module-from-one-excel-workbook-to-another-workbook – 5202456 Dec 13 '22 at 11:54
  • looks great for copying a module but not sure how I'd use this to copy the activate macro from one sheet to another – Automation Monkey Dec 13 '22 at 12:00
  • How about `SheetSource.Copy Before:=SheetSource` and continue with `Set SheetTarget = ActiveSheet` (or the more explicit `Set SheetTarget = SheetSource.Previous` if you stick with *Before*) and `SheetTarget.Name = strNameOfSheetToCreate`. – VBasic2008 Dec 13 '22 at 12:07
  • Hi @VBasic2008 that works although it's giving me a msgbox with "The name 'DisciplineList' already exists. Click yes to use that version of the name or click No to rename the version of 'DisciplineList' you're moving or copying. I can click yes to all and everything is fine, however the previous way I copied the sheet it didn't create this warning. Is there a way to ignore it somehow as I will not be the end user of the workbook? – Automation Monkey Dec 13 '22 at 12:23
  • fixed with wrapping in Application.DisplayAlerts = False and Application.DisplayAlerts = True – Automation Monkey Dec 13 '22 at 12:27

0 Answers0