Save a worksheet as a new workbook along with VBA codes

I have a worksheet ( Sheet1) which contains an overview table which counts number of green and red colored cells. ( Clicking OVERVIEW button do this)
Save Button saves the Sheet1 as a new workbook in the same workfolder. But the newly saved files shows #NAME? in G2 and G3 as the function and vba code is missing in the newly created workbook.

Is there a way to save the vba codes also to the new workbook ?

Thanks

is it saving to .xlsm? or .xls?
using .xlsx will erase the vb code.

COPY the sheet into new wb will NOT transfer code.

As a reminder when the original VBA procedure is located within the worksheet module
that works obviously just with the single Worksheets.Copy codeline !

Another obvious way as the attachment contains a single worksheet : just use Workbooks.SaveCopyAs rather than Worksheets.Copy …

Originally Posted by Marc L

As a reminder when the original VBA procedure is located within the worksheet module
that works obviously just with the single Worksheets.Copy codeline !

Another obvious way as the attachment contains a single worksheet : just use Workbooks.SaveCopyAs rather than Worksheets.Copy …

Hi Marc ,
The only issue with Workbooks.SaveCopy As is: i have several other worksheets in the workbook ( around 10 worksheets) and i need only specific 3 sheets to be saved a snew workbook. I can defineitly use Delete sheets and then save the workbook as new Workbook. But then i will have to Not save the original Workbook.. a bit triring :P

Originally Posted by ranman256

is it saving to .xlsm? or .xls?
using .xlsx will erase the vb code.

COPY the sheet into new wb will NOT transfer code.

Hi Ranman256
thanks for the tip

I found another workaround for my problem. Instead of using a function to count colored cells. i use this formula. This can be then without any issues copied to a new workbook


Get count of rows where both cell values match -->

Please Login or Register  to view this content.

Get count of rows where both cell values differ -->

Please Login or Register  to view this content.

Closing this thread and thanks for all inputs