Macro to copy and save cells by name to new workbook

[Danin]

Firstly, thank you for the invaluable information I get in here. I am no graceful in my macros but they work so i can't complain.
I have 4 Questions
I have a purchase order macro. Command Button is clicked and it opens a workbook and saves selected cells to next available row and saves workbook automatically.
This works great for me, but sometimes the others will move a row or a column and this causes issues. i go in and fix it and have shown them how to fix it but they can make it worse.

1 Can I set the cells by name instead of cell location (eg PODate instead of D6) so no matter where they are they will be found instead of by location?

2 I want to create a Command Button that will upon pressing open directly to the folder location I want to save it to and allows me to name it what i want to.
Or to save it as the Purchase Order Number and the Supplier Name. That would be cool to be able to save it as 2 cell locations on worksheet.

3 I want to also save the cell locations in bold below that are saved by command button already to a workbook to a worksheet in my purchase order workbook. This allows me to see orders at a glance without going to a main folder. and also make sure numbers aren't doubled up, etc.

4 When I open the purchase order workbook i want to auto update purchase number. my auto update seemed to create problem with command box sizes. Command Box sizes are frustrating.

Macro for purchase order at moment

Private Sub CommandButton1_Click()Dim Area As String
Dim Supplier As String, PurchaseNumber As Long
Dim Description As String, Commercial As String
Dim Approved As String, Costcode As String
Dim PODate As Long
Dim Total_GSTexc As Single
Dim PurchaseOrderWest As Workbook
Worksheets("PurchaseOrder").Select
Area = Range("G6")
Worksheets("PurchaseOrder").Select
PurchaseNumber = Range("H6")
Worksheets("PurchaseOrder").Select
PODate = Range("H7")
Worksheets("PurchaseOrder").Select
Supplier = Range("B6")
Worksheets("PurchaseOrder").Select
Description = Range("A35")
Worksheets("PurchaseOrder").Select
Costcode = Range("D35")
Worksheets("PurchaseOrder").Select
Commercial = Range("A32")
Worksheets("PurchaseOrder").Select
Approved = Range("A29")
Worksheets("PurchaseOrder").Select
Total_GSTexc = Range("H26")
Set PurchaseOrderWest = Workbooks.Open("P:\CAR - Contracts Admin(R)\CAR99-Subcontractors\04. Registers\TSRC - Purchase Register.xlsb")
Worksheets("NewPurchaseOrders").Select
Worksheets("NewPurchaseOrders").Range("A3").Select
RowCount = Worksheets("NewPurchaseOrders").Range("A1").CurrentRegion.Rows.Count
With Worksheets("NewPurchaseOrders").Range("A1")
.Offset(RowCount, 0) = Area
.Offset(RowCount, 1) = PurchaseNumber
.Offset(RowCount, 2) = PODate
.Offset(RowCount, 3) = Supplier
.Offset(RowCount, 4) = Description
.Offset(RowCount, 5) = Costcode
.Offset(RowCount, 6) = Approved
.Offset(RowCount, 7) = Commercial
.Offset(RowCount, 8) = Total_GSTexc
End With
PurchaseOrderWest.Save


End Sub


Private Sub CommandButton2_Click()
Worksheets("PurchaseOrder").Select
Range("H6").Value = Range("H6").Value + 1


End Sub

 

[Danin]

P.S - I have been using the below to save as. but I want it to open to a specific place.


Private Sub CommandButton3_Click()
Dim file_name As Variant
file_name = Application.GetSaveAsFilename(FileFilter:="Microsoft Excel file (*.xls), *.xls")
If file_name <> False Then
ActiveWorkbook.SAVEAS Filename:=file_name
MsgBox "File Saved!"
End If