Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

davedave24

Programmer
Aug 26, 2010
113
GB
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
 
HI,

What version of Excel?

Please post the code you're using.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
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)
Code:
    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
 
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.
 
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...
Code:
'
    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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Thanks Skip that worked great
This was the final code
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top