Copy/Paste worksheet to new workbook without that worksheet's VBA code

Question

What I want to do is to create a normal Excel workbook (.xlsx) where I summarize the inputs/outputs of some simulations by copying some sheets from a Macro-enabled workbook (.xlsm) and pasting in a normal Excel workbook. The original sheets have macros, shapes, named ranges, dropdown lists, and some formatting. I want some properties of the sheets to come over to the new workbook (named ranges, row and column formatting, cell formatting) but not others (shapes, dropdown lists, macros). I also have to distribute the .xlsm workbook to other users, so I want a solution that doesn't require the user to grant permissions.

If I copy/paste like below, then I get all the properties of the worksheet to come over to the new workbook. I have figured out how to delete shapes and remove dropdown list formatting from the sheet in the new workbook, but I can't remove the worksheet's VBA code without modifying the VBA references.

If I paste special, then I can avoid bringing over the worksheet's VBA code, but I can't bring over the named ranges.

CopySheetToWB(sht as string, wb_New as workbook)

    Dim sht_Name as string, rng As Range, shp as shape
    Dim ws As Worksheet, wb As Workbook, ws_New As Worksheet

    ' set sheet in CURRENT wb
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets(sht)

    ' copy/paste sheet to NEW wb
    ws.Copy after:=wb_New.Sheets(wb_New.Sheets.Count)

    ' delete shapes from NEW ws
    Set ws_New = wb_New.Worksheets(sht)
    For Each shp In ws_New.Shapes
        shp.Delete
    Next shp

    ' remove dropdown lists from copied sheet
    ws_New.Cells.Validation.Delete

End Sub

Can you save the ..xlsm file as tmp.xlsx (removing the macros) and then copy your sheets from it before deleting it. — CDP1802
I think this would work. If you save the workbook as soon as you create it and then copy the sheets to the new workbook, then the sheets should transfer without their VBA code. I also posted another solution where I save the workbook after copying the sheets. My old way of saving was creating workbooks that I couldn't open. My new way of saving gives me what I wanted. — Physco11

Physco11 Physco11 · Accepted Answer · 2020-03-09T00:16:12

The answer to my problem turned out to be quite simple. My old code that wasn't working and my new code that is working. If you save the file type correctly, then the macros in the worksheets are not a problem as they are removed.

wb_New.SaveAs FileName:=str_fName, FileFormat:=xlWorkbookNormal   'old code
wb_New.SaveAs FileName:=str_fName, FileFormat:=51                 'new code

Also make sure to include the file extension in str_fName. VBA doesn't automatically append the correct file extension based on the FileFormat you choose.

Copy/Paste worksheet to new workbook without that worksheet's VBA code

1 Answers