Expected end sub ошибка vba

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

I’m not sure what you mean by this? What should I change? Add? Amend?

Im saying that you have two separate sub names for each of your procedures, and only one End Sub. When the cumputer goes past the End Sub, it finds that there is no End Sub to match the second sub name, so you get the error «(I) Expected (an) End Sub».
So …. If you tell it to ignore one of the sub names everything will be OK
You can make VBA iignore a line of code by putting a quote mark (‘) at the start of the line.

Private Sub Workbook_Open()
 'Sub NextPO()   This line will be ignored because of the ' mark
    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()  This Line will also be ignored
    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

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
…

Hi Jessica,

Off to the rack shortly, but for any «helper», could you expound a bit on this?

In really plain-English, please tell us what you would like the Template to do?

By utter guessing and most likely off-the-mark, but by example nevertheless:

«I have an Excel Template. Upon creating a new workbook based on this template, I would like….» to happen.

Mark

Thank you sooo much for your help!

This code seems to work and is inputting the sequential numbers, yay! It is saving the file correctly on opening too buuuuuut………. its saving the file over and over and over again and I can’t stop it! How can I get into the file to work on the code if it’s constantly resaving and I can’t get it to stay on the original!? And what is it that I need to change in the code to make the resave only happen the one time per opening…

Hi Jessica
Im glad we are getting a bit closer, but GTO is right that without a clear picture of the objective, we can only try to get your code executing without error, but it could be doing the wrong thing!
Lets look at what we have in the code (post #6)
1. You have a WorkbookOpen Sub, which will execute whenever the workbook containing it is opened. This adds 1 to the value in cell G4, puts the date in G3 and clears cells A16-E32 (Active Worksheet) {sub NxtPO}
2. You also have a WorksheetChange module, which I assume is attached to the active worksheet containng your Counter in G4, and there is the problem. This routine saves the ActiveWorksheet with a new name and increments
your counter , changing the activesheet, which runs the module again.

To resolve, you can either put the counter in a different worksheet, or amend the worksheet change routine to ignore a change to cell G4.
Just one more point: You do appreciate that a new workbook will be saved for every single change to the worksheet that has the change module ?