Moving 2 sheets to a new (non-macro document), and clearing the buttons/code 1

[davedave24]

Hi all

I'm trying to move 2 sheets from the master document into a new document, save as, and close, with no input from the user (file names, folder etc.)

The 2 sheets have several buttons with code on. When I try to copy the sheet over, it retains the buttons and code. I've attempted to use DrawingObjects.Delete to remove the buttons, but the code remains.

Is there an easy way to do this, while retaining all the source formatting, removing the buttons and code, and saving without the user being prompted?

Thanks

 

[SkipVought]

HI,

What version of Excel?

Please post the code you're using.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[davedave24]

Hi Skip

I'm in Excel 2013, though some computers may be on 2010

This was the basic code I was using (what I have left of it)

    Dim wb As Workbook
    Set wb = Workbooks.Add

    ThisWorkbook.Sheets(JobNo).Copy Before:=wb.Sheets(1)
    wb.SaveAs "C:\Users\Dave\Desktop\Old Jobs\" & IndexNo & ".xlsx"

However this copies the CommandButtons, and all the code, which then shows a MsgBox asking if we want to save as a Macro workbook or not (changing to .xlsm doesn't avoid this).

I was thinking of just having a template xl file then copying the raw data over.

Thanks

 

[davedave24]

Skip - I just found this article - Link - that suggests removing the VBA code, though I'm a bit unsure of where I would execute that code.

 

[SkipVought]

Dave,

First, you need to redo your SaveAs. Merely changing .xlsm to .xlsx doesn't cut it! Turn on your macro recorder and record saving this workbook OR check VBA HELP on SaveAs

I'd code it something like this, being sure to change the SaveAs...

'
    With Workbooks.Add
        ThisWorkbook.Sheets(JobNo).Cells.Copy
        With .Sheets(1)
            .Cells(1, 1).PasteSpecial xlPasteValuesAndNumberFormats
            .Cells(1, 1).PasteSpecial xlPasteFormats
            .Name = JobNo
        End With
        
        .SaveAs "C:\Users\Dave\Desktop\Old Jobs\" & IndexNo & ".xlsx"
        .Close
        
        End With
    End With

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[davedave24]

Thanks Skip that worked great
This was the final code

Dim wb As Workbook
    Set wb = Workbooks.Add

    ThisWorkbook.Sheets(JobNo).Cells.Copy
    
    With wb.Sheets(1)
        .Cells(1, 1).PasteSpecial xlPasteValuesAndNumberFormats
        .Cells(1, 1).PasteSpecial xlPasteFormats
        .Name = "J-TEST"
    End With
    
    wb.SaveAs Filename:="C:\Users\LINDA\Desktop\Old Jobs\" & IndexNo & ".xlsm", _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    wb.Close