Compile Error: Expected End Sub - HELP!!!

[jessicaspencer]

Please can someone help! I've never written a macro before! I've got this far but keep getting the same error message and despite reading numerous forums for advice, I can't see where I've gone wrong!

I am trying to create a Purchase Order template that will open with a new sequential number each time and will force a new file name when saved, ideally the name to be the sequential number... If it cooooould, I'd quite like the date to drop in upon opening as well

Please help!!

Private Sub Workbook_Open()
Sub NextPO()
    Range("G4").Value = Range("G4").Value + 1
        Range("A16:E32").ClearContents
            Range("G3").Value = Date
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Sub SavePOWithNewName()
    Dim NewFN As Variant
        ' Copy PO to a new workbook
            ActiveSheet.Copy
                NewFN = "Y:\\YS-SBS2011\YSL Purchase Order\YSL-PO" & Range("G4") & Range("G3").Value & ".xlsx"
                    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
                        ActiveWorkbook.Close
                            NextPO
End Sub

 

[GTO]

Hi Jessica,

...I want to have Purchase Order template that will know what the next available PO number is...

So, I want the excel sheet to open with the next sequential number from when previously opened. I then want it to save with this sequential number as the file name.

So... the first one would be PO001 (in the field G4 in my case) and then auto-saved as 'YSL-PO001'. The next time its opened, if it could then say PO002 and will auto-save as 'YSL-PO002'. Ideally this should stop duplications and will make sure that all PO's are saved...

If I could ask for more, I'd also like it to drop 'today's date' in the G3 box but this isn't essential...

Okay, here's a small try to see if I am understanding the current goal.

Create a folder (that you can delete later, this is just for testing) and save a new/blank workbook to this folder; using the macro-enabled format of course.

In the ThisWorkbook Module:

Option Explicit
  
Private Sub Workbook_Open()
Dim lCounter As Long
  
  '// for PO "number" like 'YSL-PO002', take the 7th character and on to get the    //
  '// current numeric value.  From this return, we add 1, format it to a 3-digit    //
  '// number, and append it to the constant string 'YSL-PO'
  lCounter = CLng(Mid(Sheet1.Range("G4").Value, 7))
  Sheet1.Range("G4").Value = "YSL-PO" & Format(lCounter + 1, "000")
  
  '// Then save the workbook we are using as a pseudo template.                     //
  ThisWorkbook.Save
  
  '// Plunk in the date with whatever format you choose.                            //
  With Sheet1.Range("G3")
    .NumberFormat = "mm/dd/yyyy"
    .Value = Date
  End With
  
  '// Actually I think this is the first time I've needed to kill the alert for     //
  '// saving a wb with code to .xlsx format (which loses the code after you close   //
  '// the wb).  This seems to work fine.
  Application.DisplayAlerts = False
  ThisWorkbook.SaveAs ThisWorkbook.Path & "\" & Sheet1.Range("G4").Value, xlOpenXMLWorkbook
  Application.DisplayAlerts = False
  
End Sub

Put "YSL-PO000" in G4 and save the workbook and close it. It should auto-increment each time opened.

I would add that I would not consider this a real solution, just something to help get you started. Firstly, the PO number's formatting changes in only one thousand orders, so I would expect that to be undesirable.

Anyways, hope it is in the right direction for some ideas at least,

Mark