VBA to unlink data but keep a copy of file!

[Kagzfirearms]

Ok so here it is...

I have a pretty complex set of tables strewn across three different sheets. I use my barcode scanner to scan items into a list on one page, It uses that to populate an invoice with data across 3 pages

Page 1 will reference the scanned barcode to find a match on page 3, It populates Price, UPC etc. to fill out the invoice on page 1.

What i am running into is that i have a macro button as follows...

Sub NextInvoice()
    Range("E4").Value = Range("E4").Value + 1
    Range("sheet3!A2:sheet3!a" & Range("sheet3!A2:sheet3!A518").End(xlDown).Row).Clear
End Sub


Sub SaveInvWithNewName()
    Dim NewFN As Variant
    ' Copy Invoice to a new workbook
    ActiveSheet.Copy
    NewFN = "C:\Users\Kyle\Desktop\PAST INVOICES\Inv" & Range("E4").Value & ".xlsx"
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
    ActiveWorkbook.Close
    NextInvoice
End Sub

What this does is save a copy of the current sheet as an invoice that i can go back and look at later for any reason, then it refreshes the page and the queries i made by scanning barcodes and it also changes the invoice number on the newly refreshed page.

What i need to do is UNLINK the saved worksheet and keep it from seeing the new data that is entered WHILE keeping the information on the sheet that was saved... I know this sounds horrible but i know you brainiacs know what to do! TIA!!!!

 

[Kagzfirearms]

Problem solved. I just made excel export as PDF to save a copy of it and keep the original form as it was. Code pasted below if that will help anyone.

Sub NextInvoice()
    Range("E4").Value = Range("E4").Value + 1
    Range("sheet3!A2:sheet3!a" & Range("sheet3!A2:sheet3!A518").End(xlDown).Row).Clear
End Sub
Sub Exportasfixedformat()
    ActiveSheet.Exportasfixedformat Type:=xlTypePDF, filename:="C:\Users\Kyle\Desktop\PAST INVOICES\Inv" & Range("E4").Value _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=False
        Printout
        NextInvoice
End Sub


Sub Printout()
    ActiveSheet.Printout preview = False, ignorprintareas = False
End Sub

 

[Kagzfirearms]

Problem solved. I just made excel export as PDF to save a copy of it and keep the original form as it was. Code pasted below if that will help anyone.

Sub NextInvoice()
    Range("E4").Value = Range("E4").Value + 1
    Range("sheet3!A2:sheet3!a" & Range("sheet3!A2:sheet3!A518").End(xlDown).Row).Clear
End Sub
Sub Exportasfixedformat()
    ActiveSheet.Exportasfixedformat Type:=xlTypePDF, filename:="C:\Users\Kyle\Desktop\PAST INVOICES\Inv" & Range("E4").Value _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=False
        Printout
        NextInvoice
End Sub


Sub Printout()
    ActiveSheet.Printout preview = False, ignorprintareas = False
End Sub

Where "E4" was the cell containing the invoice number, Sheet3 was the sheet that contained the invoice