Excel Macro to Save Sheets As xlsx

mare23

New Member
Joined
Oct 28, 2020
Messages
22
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.
VBA Code:
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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Watch MrExcel Video

Forum statistics

Threads
1,126,948
Messages
5,621,777
Members
415,856
Latest member
jimb2k

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