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]

If you have Workbooks(1) and Workbooks(2) and Workbooks(1) is the active workbook at runtime (When you start the macro) then you can copy from the active workbook to the other workbook and paste special like this:
Assuming both workbooks are open.

Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = ActiveSheet
Set sh2 = Workbooks(2).Sheets(1) ''For illustration only, best practice is to use names instead of index numbers.
sh1.Range("A1:F20").Copy
sh2.Range("A1").PasteSpecial xlPasteColumnWidths
sh2.Ragne("A1"),PasteSpecial xlPasteValuesAndNumberFormats

The sh2 variable includes the second workbook and sheet references. I use the variable to avoid using the long 'Worksheets((2).Sheets(1)' syntax every time I need to make reference to it. But the point is, you have to tell vba which workbook, sheet and range you want to paste into when the paste destination is external to the current workbook. The copy and paste transaction is essentially the same, with the difference being in how the destination is defined. If you still do not understand then I suggest you search on line for a tutorial on copying and pasting for Excel.
Regards, JLG