Excel: Compile Error: Expected End Sub - HELP!!!


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


This question generated 9 answers. To proceed to the answers, click here.

This thread is current as of October 14, 2014.


For more resources for Microsoft Excel