Help VBA save a copy of all workbook

sanacenter

New Member
Joined
Jan 17, 2017
Messages
1
Hi, I have a excel file with 6 sheets: customers | invoice| chitanta (receipt) |chitanta diferente (receipt due) | Registru facturi (list of invoices) | Valuta (curency)

I have some macro code in it and is working fine but I have no Idea how to make a code to save a copy of all sheets in the same time with the invoice sheet.

Right now it save the Invoice sheet in a specify folder... here is the code:
Code:
Sub PostToRegister()    Dim WS1 As Worksheet
    Dim WS2 As Worksheet
    Dim WS3 As Worksheet
    Dim WS4 As Worksheet
    Dim WS5 As Worksheet
    Dim WS6 As Worksheet
    Set WS1 = Worksheets("Invoice")
    Set WS2 = Worksheets("Registru Facturi")
    Set WS3 = Worksheets("Customers")
    Set WS4 = Worksheets("chitanta")
    Set WS5 = Worksheets("chitanta diferenta")
    Set WS6 = Worksheets("valuta")
    
    ' Vezi care este urmatorul rand
    NextRow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
    
    'Valorile importante din Registru facturi F2 -data, F3-nyumar factura, B10 -client, B11-reg com, B12-CUI, B13 adresa, B14 telefon, B15 email, B16 cont bancar, C36  suma achitat, C37 rest plata, D32 valuta facturii
    
    WS2.Cells(NextRow, 1).Resize(1, 14).Value = Array(WS1.Range("F2"), WS1.Range("F3"), WS1.Range("B10"), Range("InvTot"), WS1.Range("B11"), WS1.Range("B12"), WS1.Range("B13"), WS1.Range("B14"), WS1.Range("B15"), WS1.Range("B16"), WS3.Range("C36"), WS3.Range("C37"), WS3.Range("D32"), WS3.Range("C38"))
    
End Sub
Sub PDFActiveSheet()
'www.contextures.com
'for Excel 2010 and later
Dim wsA As Worksheet
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler


Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
strTime = Format(Now(), "yyyymmdd\_hhmm")


'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"


'replace spaces and periods in sheet name
strName = Replace(wsA.Name, " ", "")
strName = Replace(strName, ".", "_")


'create default name for savng file
strFile = strName & "_" & strTime & ".pdf"
strPathFile = strPath & strFile


'use can enter name and
' select folder for file
myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strPathFile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and FileName to save")


'export to PDF if a folder was selected
If myFile <> "False" Then
    wsA.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    'confirmation message with file info
    MsgBox "PDF file has been created: " _
      & vbCrLf _
      & myFile
End If


exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler
End Sub




Sub NextInvoice()
    Range("F3").Value = Range("F3").Value + 1
    Range("A18:A32").ClearContents
    Worksheets("Customers").Range("C5:C36").ClearContents
    Worksheets("Customers").Range("C38").ClearContents
End Sub


Sub SaveInvWithNewName()
    Dim NewFN As Variant
    Dim WS1 As Worksheet
    Set WS1 = Worksheets("Invoice")
    PostToRegister
    
    ' Convert all Formulas that Point to Other Sheets to Values
    WS1.Range("B10:B16").Value = WS1.Range("B10:B16").Value
    WS1.Range("C18").Value = WS1.Range("C18").Value
    WS1.Range("D18").Value = WS1.Range("D18").Value
    WS1.Range("F34").Value = WS1.Range("F34").Value
    WS1.Range("B39").Value = WS1.Range("B39").Value
    
    
    ' Copy Invoice to new workbook
    ActiveSheet.Copy
    NewFN = "C:\Artemis\Inv\Inv" & Range("F3").Value & ".xlsx"
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
    ActiveWorkbook.Close
  
    
    ' Back in the original Invoice worksheet, re-create the formulas
    WS1.Range("B10:B16").NumberFormat = "General"
    WS1.Range("$B$10").Formula = "=Customers!C23"
    WS1.Range("$B$11").Formula = "=Customers!C24"
    WS1.Range("$B$12").Formula = "=Customers!C25"
    WS1.Range("$B$13").Formula = "=Customers!C28"
    WS1.Range("$B$14").Formula = "=Customers!C29"
    WS1.Range("$B$15").Formula = "=Customers!C30"
    WS1.Range("$B$16").Formula = "=Customers!C31"
    WS1.Range("$C$18").Formula = "=Customers!E16"
    WS1.Range("$D$18").Formula = "=Customers!C32"
    WS1.Range("$F$34").Formula = "=Customers!D32"
    WS1.Range("$B$39").Formula = "=valuta!C4"
    
    ' Update the Invoice Number
    NextInvoice
End Sub


File you can download here

Thank you
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Forum statistics

Threads
1,215,563
Messages
6,125,568
Members
449,237
Latest member
Chase S

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top