CODEWITHSUNDEEP
vbaexcel
Selrac
Selrac

Reputation: 2293

VBA copy sheets with macros

I have a series of Sheets in Excel which I'm coping to a new File. One of the Sheets has an object button that refers to a macro of the original file. On the new created file the button refers to the macro in the original file.

The code I use to transfer the Sheets is the following

Sheets(Array("A", "B", "C", "D", "E")).Copy
ActiveWorkbook.SaveAs Filename:= "\\XXX\" & Filename & ".xlsb" _
    , FileFormat:=xlExcel12, CreateBackup:=False
ActiveWindow.Close

Is it possible to transfer the macros when copying the Sheets and refer the button to the new file?

Upvotes: 1

Views: 2881

Answers (2)

rejdrouin
rejdrouin

Reputation: 89

Put the code in the sheet(s) instead of a module and it will transfer to the new book.

Upvotes: 1

ArchiCAT
ArchiCAT

Reputation: 119

Try saving the whole workbok as a new file and delete the sheets you don't need. That way you keep the macro and the refrence in place. If the macro in the original file ties to itself by name ( like workbooks(filename) ) you should change that to ThisWorkbook or something similar dinamic reference in the original file.

Upvotes: 2

Related Questions