Excel Macro to Save Sheets As xlsx

[mare23]

Hi all,

I am new to the VBA and Macro world, but I have been trying for a week to figure out a specific code. I need to create a macro that will take that Purchase order template once filled out and save it as an Xlsx file. I have code for PDF below and I need same fo xlsx. Is it possible to save excel for specific columns/rows as in the code below? And also when I click the SAVE button let it save my file without opening it


'Save Invoice as PDF

On Error Resume Next

Sheet2.Range("B2:T65").ExportAsFixedFormat xlTypePDF, Filename:= _
"C:\invoice\" & Sheet2.Range("R4").Value, Openafterpublish:=False
End Sub

thx
Mare

 

[JLGWhiz]

See if this will do what you want.

Sub t()
Dim sh As Worksheet, fPath
fPath = ThisWorkbook.Path
With ThisWorkbook
    Set sh = .Sheets.Add
    Sheet2.Range("B2:T65").Copy sh.Range("B2")
    sh.Copy
    ActiveWorkbook.SaveAs fPath & "\" & Sheet2.Range("R4").Value & ".xlsx", 51
    ActiveWorkbook.Close False
    Application.DisplayAlerts = False
    sh.Delete
    Application.DisplayAlerts = True
End With
End Sub

 

[mare23]

Great, that’s it but I want to save this xlsx in "C:\invoice\"

 

[JLGWhiz]

fPath = ThisWorkbook.Path

Change to:

fPath = "C:\Invoice"

 

[mare23]

Great, thx!

 

[mare23]

One more question how to Use PasteSpecial Method to Copy Range and Paste in Another Sheet with Formatting and Column Widths. I need same size as original, without Gridlines and with 70% zoom

 

[JLGWhiz]

One more question how to Use PasteSpecial Method to Copy Range and Paste in Another Sheet with Formatting and Column Widths. I need same size as original, without Gridlines and with 70% zoom

example:
Sheet1.Range("A2:F20").Copy

With Sheet2
    .Range("A2").PasteSpecial xlPasteColumnWidths
    .Range("A2").PasteSpecial. xlPasteValuesAndNumberFormats
End With

For reference: XlPasteType enumeration (Excel)

 

[mare23]

Sorry I didn't mean another sheet in the same workbook, I mean in new workbook. I need Copy/paste same as I have in original workbook without gridlines and with same size and merge rows/columns...

 

[JLGWhiz]

Sorry I didn't mean another sheet in the same workbook, I mean in new workbook. I need Copy/paste same as I have in original workbook without gridlines and with same size and merge rows/columns...

It is the same principle. You just and in the workbook reference in front of the sheet reference.

Workbooks(2).Sheets(2).PasteSpecial xlPasteColumnWidths

 

[mare23]

Sorry but I don't understand