Save copy as excel format as well

josros60

Well-known Member
Joined
Jun 27, 2010
Messages
595
Office Version
  1. 365
Hi,

how can I modify the code below to also save a copy as excel format as well beside the PDF and after saving it open the file.

VBA Code:
Sub SaveActiveWorkbookAsPDF()
Dim saveLocation As String
'Call posttoregister
'Call create_and_email_4pdf
saveLocation = "C:\Users\jose.rossi\Desktop\Excel Files\Contras\West Telecom\" & Range("A125").Value & ".pdf"
'Save active workbook as PDF
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
    filename:=saveLocation

End Sub

Thank you,
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,898
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
UNTESTED
VBA Code:
Sub SaveActiveWorkbookAsPDF()
Dim saveLocation As String
'Call posttoregister
'Call create_and_email_4pdf
saveLocation = "C:\Users\jose.rossi\Desktop\Excel Files\Contras\West Telecom\" & Range("A125").Value
With ActiveWorkbook
   .ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveLocation
   .SaveAs Filename:=saveLocation & ".xlsm", FileFormat:=52
End With
End Sub
 

josros60

Well-known Member
Joined
Jun 27, 2010
Messages
595
Office Version
  1. 365
Thank you, it worked.

just one question when saved it close the original is there a way to save the copies but keep the original open?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,444
Office Version
  1. 365
Platform
  1. Windows
Maybe
VBA Code:
Sub SaveActiveWorkbookAsPDF()
Dim saveLocation As String
'Call posttoregister
'Call create_and_email_4pdf
saveLocation = "C:\Users\jose.rossi\Desktop\Excel Files\Contras\West Telecom\" & Range("A125").Value
With ActiveWorkbook
   .ExportAsFixedFormat Type:=xlTypePDF, FileName:=saveLocation
   .SaveCopyAs FileName:=saveLocation & ".xlsm"
End With
End Sub
 

josros60

Well-known Member
Joined
Jun 27, 2010
Messages
595
Office Version
  1. 365

ADVERTISEMENT

Sorry forgot to ask once saved besides keeping open the original file, want also delete a sheet (tab) named Register in the saved one
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,444
Office Version
  1. 365
Platform
  1. Windows
Did my suggestion work?
 

josros60

Well-known Member
Joined
Jun 27, 2010
Messages
595
Office Version
  1. 365

ADVERTISEMENT

yes, it did the only thing need to change it xlsx format but when tried open the file said format no supported, can I also when save delete the sheet "Register".

thank you for all your help.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,444
Office Version
  1. 365
Platform
  1. Windows
Is the file an xlsx or xlsm ?
 

josros60

Well-known Member
Joined
Jun 27, 2010
Messages
595
Office Version
  1. 365
the original is xlsm but I want save copy as xlsx
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,444
Office Version
  1. 365
Platform
  1. Windows
In that case you will need to use SaveAs. You cannot change the file type when using SaveCopyAs.
 

Forum statistics

Threads
1,141,405
Messages
5,706,261
Members
421,437
Latest member
GijoeBlack

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
Top