Overwrite existing files and save as PDF XLSM and new sheet

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,658
Hi,
Try the below code:
Rich (BB code):
Sub GemsomPDF()
 
  Dim FileName As String, DestFolder As String, NewShName As String
  Dim Ws As Worksheet
 
  ' Set the destination folder
  DestFolder = "D:\Users\rasmus\Desktop\F" & ChrW(195) & ChrW(165) & "rvangtelt\2019\"
 
  ThisWorkbook.Activate ' It's just for the debugging
  Set Ws = Worksheets("Prisliste")
  NewShName = Ws.Range("D1").Value
  If Len(NewShName) = 0 Then
    MsgBox "D1 is empty", vbCritical, "Exit"
    Exit Sub
  End If
 
  ' Build file name of PDFs and XMLM
  FileName = Ws.Range("D1").Value & Ws.Range("E10").Value & Ws.Range("D4").Value
 
  ' Avoid blinking
  Application.ScreenUpdating = False
 
  ' 1) Copy sheet "prisliste" to a new sheet with the name in "D1"
  Ws.Copy After:=Worksheets(Sheets.Count)
  On Error Resume Next
  Application.DisplayAlerts = False
  Sheets(NewShName).Delete
  Application.DisplayAlerts = True
  Sheets(Sheets.Count).Name = NewShName
  Ws.Activate
  On Error GoTo exit_
 
  ' 2) Save 3 sheets "tilbud", "lejekontrakt", "faktura" as pdf to different folders
  Sheets("Tilbud").ExportAsFixedFormat _
                   Type:=xlTypePDF, _
                   FileName:=DestFolder & "Tilbud\" & FileName, _
                   Quality:=xlQualityStandard, _
                   IncludeDocProperties:=True, _
                   IgnorePrintAreas:=False, _
                   OpenAfterPublish:=False
  Sheets("Lejekontrakt").ExportAsFixedFormat _
                   Type:=xlTypePDF, _
                   FileName:=DestFolder & "Lejekontrakt\" & FileName, _
                   Quality:=xlQualityStandard, _
                   IncludeDocProperties:=True, _
                   IgnorePrintAreas:=False, _
                   OpenAfterPublish:=False
  Sheets("Faktura").ExportAsFixedFormat _
                   Type:=xlTypePDF, _
                   FileName:=DestFolder & "Faktura\" & FileName, _
                   Quality:=xlQualityStandard, _
                   IncludeDocProperties:=True, _
                   IgnorePrintAreas:=False, _
                   OpenAfterPublish:=False
 
  ' 3) Copy sheets "prisliste", "tilbud", "lejekontrakt", and "faktura"
  '    to a new workbook with the name in D1 & E10 & D4
  Sheets(Array("Prisliste", "Tilbud", "Lejekontrakt", "Faktura")).Copy
  'There is now a new active workbook
  With ActiveWorkbook
    'Break link
    .ChangeLink Name:=Ws.Parent.FullName, NewName:=.FullName
    'Save it
    .SaveAs FileName:=DestFolder & FileName, FileFormat:=52
    'Close it
    .Close SaveChanges:=False
  End With
 
  ' Restore initial selection of Ws
  ThisWorkbook.Activate
  Ws.Activate
 
exit_:
 
  ' Restore screen updating
  Application.ScreenUpdating = True
 
  If Err Then MsgBox Err.Description, vbCritical, "Error"
 
End Sub
Regards
 

Rasmusjc

New Member
Joined
Jul 29, 2018
Messages
18
Hi Vladimir

Had a little problems with the link to the folder, Rasmus -> rasmus was case sensitive but i found the error.

One thing is still missing.

The "Safe PDF" makro i use on "Prisliste" from the main workbook are not beeing copied over to the new workbook.

The reason why i need this is if change something in the "prisliste"/pricelist and need to make new pdf files i cant do it in the main workbook (Dont know how to make it). Thats why i make a copy of the sheet "pricelist" - "tilbud" - "lejekontrakt" - "faktura" then i can open the new workbook change what i need to change and push "save to pdf" and then it should overwrite the pdf files.

But im getting this error in the new created workbook "the macro in "test" could not run, it might can be found or are deactivated.. But i can se under macro that it has not been copyied.

So can i copy the macro also?

Best regards
Rasmus
 

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,658
...Had a little problems with the link to the folder, Rasmus -> rasmus was case sensitive but i found the error.
My apologizing, for testing reason I used: DestFolder = "D:\Users\..." but actually it should be: DestFolder = "C:\Users\..."

The "Safe PDF" makro i use on "Prisliste" from the main workbook are not beeing copied over to the new workbook.
...
So can i copy the macro also?
The simplest way is in placing that macro in the code module of "Prisliste" sheet, rather than in the standard code Module1 or elsewhere.
 
Last edited:

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,658
The reason why i need this is if change something in the "prisliste"/pricelist and need to make new pdf files i cant do it in the main workbook (Dont know how to make it)

No need to copy these sheets into a separate workbook.
The below part of the code saves 4 sheets of the main workbook into the single PDF file:
Rich (BB code):
  ' === Create PDF file of 4 sheets in the main workbook ===
 
  ' Select 4 sheets to export it as PDF (single file)
  Sheets(Array("Prisliste", "Tilbud", "Lejekontrakt", "Faktura")).Select
  ' Export as PDF
  ActiveSheet.ExportAsFixedFormat _
                   Type:=xlTypePDF, _
                   FileName:=DestFolder & "MyFolder\" & FileName, _
                   Quality:=xlQualityStandard, _
                   IncludeDocProperties:=True, _
                   IgnorePrintAreas:=False, _
                   OpenAfterPublish:=False
  ' Deselect sheets, restore original selection
  Sheets("Prisliste").Select
Just change the destination "MyFolder" as required and insert that code into GemsomPDF macro
 
Last edited:

Rasmusjc

New Member
Joined
Jul 29, 2018
Messages
18
It's a bit difficult for me to explain how the whole calender works. But i will give it a try.

I have a "pricelist" where i put in data like Name - adress - number - and what the customer wants to rent (tents - chairs - floor ect.)

Then in "tilbud"/offer - "lejekontrakt"/lease - "faktura"/invoice it takes the info from pricelist and place it in here.
Its because im sending out these 3 pdf files to the customer.

In pricelist i write the number of the invoice ind D1. When i push save it saves the 3 sheets as a pdf and then i copies the "prisliste"/pricelist to a new sheet with the number of the invoice in D1.

Lets say the number is 1, then the new sheet in the work book is named 1 and have the exact same data as in pricelist.

Then i go to the calender find the day where the costumer need the tent and i this spot i write 1. then it takes the data from sheet 1 and places it in the calender.

When a new costumer ask for a tent i change the number in "pricelist"D1 to 2, save to pdf - a sheet "2" is created - i type 2 in the calender ect.

The problem now is if the customer with the first invoice wants to change the order and i go into sheet "1" and change it. Then i need to make new pdf files. So i click the safe to pdf, but then it takes the info from the "pricelist" - "offer" - "lease" - "invoice" and safe that data, not the data i have changed i sheet "1".

The reason why i thought it would be smart to make a new workbook with the name from "pricelist" and the other 3 sheets was, if i needed to change something i could do it here and just push save. But i cant, it re-open the "calender"-workbook and uses the "pricelist" from this workbook.

The reason why i made it this way was to avoid typing the same thing many places as i did before.
 

Forum statistics

Threads
1,086,122
Messages
5,387,975
Members
402,091
Latest member
thomastsiakis

Some videos you may like

This Week's Hot Topics

Top