CODEWITHSUNDEEP
vbaexcel
Nautilus
Nautilus

Reputation: 115

Excel VBA execute code in newly created workbook

I'm trying to save one worksheet in my excel file to a new file, but without the formula's.

I have this code that is working to get the file saved:

Sub SaveInvoice()

 'Create a filename based on invoicenumber
 Dim FileName As String
 FileName = Sheets("Sale").Range("C3").Value

 'Copy the "Print" sheet
 Worksheets("Print").Copy

 With ActiveWorkbook
    'Save the file as new
    .SaveAs FileName:="C:\" & FileName
 End With

End Sub

This works like a charm, however I need to strip out the formula's so I googled and fount this piece of code:

ActiveSheet.Copy
Cells.Copy
Range("A1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

And this works as well, however once I merge the two pieces of code together the whole function breaks.

 With ActiveWorkbook
    'Transform cells to values
     ActiveSheet.Copy
     Cells.Copy
     Range("A1").PasteSpecial Paste:=xlPasteValues
     Application.CutCopyMode = False

    'Save the file as new
    .SaveAs FileName:="C:\" & FileName
 End With

This results in my base worksheet beeing stripped from formula's.

I need to know how I can call the function on the newle created workbook.

Upvotes: 0

Views: 903

Answers (3)

EEM
EEM

Reputation: 6659

When copying a worksheet with no Destination excel creates a New Workbook, and the New Workbook with its only Worksheet are active.

EDIT: I just realized that in the final code these lines from the original code:

'Copy the "Print" sheet
 Worksheets("Print").Copy

Were moved inside the

With ActiveWorkbook

That was previously referring to the New Workbook created by the Worksheet.Copy and that now refers to the Source Workbook

So let see what the Op's final code is actually doing:

Here the ActiveWorkbook is the [Source Workbook] and the ActiveSheet must be [Print]

With ActiveWorkbook

This copies the ActiveSheet creating a New Workbook with only one sheet

     ActiveSheet.Copy

These lines are affecting the ActiveSheet [Print] in the [Source Workbook]. Not because of the With statement but because it's the one active

    Cells.Copy
    Range("A1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False

The commands within a With intended to affect object that it refers to must start with a dot [.]; however these lines are invalid because Cells and Range are not Methods nor Properties of the Workbook Object, thus en error would have been triggered.

With Statement

Executes a series of statements on a single object or a user-defined type.

(from the msdn.microsoft.com help)

This saves the Workbook referred by the With statement, which still has the formulas

   'Save the file as new
   .SaveAs FileName:="C:\" & FileName

End With

Try this code:

Sub SaveInvoice_TEST_1()
'Create a filename based on invoicenumber
Dim FileName As String
    With ThisWorkbook
        FileName = .Sheets("Sale").Range("C3").Value
        'Copy the "Print" sheet
        .Worksheets("Print").Copy
    End With
    
    With ActiveWorkbook
        Rem Replace Formulas with Values
        .Sheets(1).UsedRange.Value = .Sheets(1).UsedRange.Value2
        'Save the file as new
        .SaveAs FileName:="C:\" & FileName
    End With
    End Sub

Suggest to read the following pages to gain a deeper understanding of the resources used:

With Statement

Upvotes: 2

Nautilus
Nautilus

Reputation: 115

Thanks to @EEM the solution was found.

Here's the code I used:

Sub SaveInvoice()
    Dim FileName As String

    With ThisWorkbook
        'Create a filename based on invoicenumber
        FileName = .Sheets("Sale").Range("C3").Value

        'Copy the "Print" sheet
        .Worksheets("Print").Copy
    End With

    With ActiveWorkbook
        'Replace Formulas with Values
        .Sheets(1).UsedRange.Value = .Sheets(1).UsedRange.Value2

        'Save the file as new
        .SaveAs FileName:="C:\" & FileName
    End With
End Sub

Upvotes: 0

HarveyFrench
HarveyFrench

Reputation: 4568

Try something like the following. Read the help page for 'ActiveSheet.Copy' - note that it creates a new workbook and activates it

Dim MyWkbk as workbook 

set MyWkbk = ActiveWorkbook
ActiveSheet.Copy

With ActiveWorkbook
    Cells.Copy
    Range("A1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False

   'Save the file as new
   .SaveAs FileName:="C:\" & FileName
   '.close
End With


MyWkBk.activate

Upvotes: 0

Related Questions