2 questions related to VBA

[STIRRELL]

Hi,
I have 3 worksheets in a workbook. Spreadsheets named EXCEL, PDF and CSV in the TEMPLATE workbook. ( different details on each tab). I want to save off the Excel tab as its own excel workbook and the name based on the value in B1. I would like to save to the C Drive in a folder called Macro. The second step is the PDF spreadsheet as Name in Cell B2. I would like to save off the PDF tab to the same location and save as PDF. and similar on the 3rd tab save of as CSV also saved to the Macro folder. I have not created the name field yet but to make is easy say its cell B3.
Can you please help me with the code below. I am guessing once I have one step working the others will only need slight modifications.
VBA hates the Path field as well. And when I run it exports as excel and does not save anywhere. ( probably due to the error)
Any help is appreciated. Can an VBA master please help.

Dim name As String
Dim Path As String
Path = C:\MACRO
Name = Range("B2")
ActiveSpreadsheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=Path &Name& ".pdf"

 

[Joe4]

Best not to use reserved words in variable names.
Also, missing last backslash and quotes around Path value.

Try this:

Dim myName As String
Dim myPath As String
myPath = "C:\MACRO\"
myName = Range("B2")
ActiveSpreadsheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=myPath & myName & ".pdf"

 

[STIRRELL]

Hi thank you so much, I was able to get it to export to the specific file on my C drive to the Macro1 folder ( big win) but for some reason its exporting them all as PDF's even though I changed the Xltype to XLSX and CSV respectively. Any suggestions?




Sheets("EXCEL1").Select
Dim MyName1 As String
Dim MyPath1 As String
MyPath1 = "C:\MACRO1\"
MyName1 = Range("A2")
ActiveSheet.ExportAsFixedFormat xlXLSX, MyPath & MyName & ".xlsx", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

 

[Joe4]

Turn on your Macro Recorder, and record yourself saving as an XLSX and a CSV file type manually, and then view the code that you recorded.
They all have different file types, so you need to make sure you have all that code correct.

 

[Skyybot]

May I suggest this information reference? It may help you understand the arguments that are needed.

Worksheet.SaveAs method (Excel)

Office VBA reference topic

learn.microsoft.com

Workbook.ExportAsFixedFormat method (Excel)

Office VBA reference topic

learn.microsoft.com