Compile Error: Expected End Sub - HELP!!!

jessicaspencer

New Member
Joined
Oct 14, 2014
Messages
4
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!!
Code:
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
 
Last edited by a moderator:

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Rich (BB code):
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

you don't call a sub with the title

NextPO will have to exist in the workbook already to avoid an error
 
Upvote 0
Hello Jessica
I think the problem is that you have two sets of Sub for each procedure and only one End Sub

Try commenting out the second one by adding a quoute mark like this before Sub:

'Sub NextPO()
 
Upvote 0
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.

Rich (BB code):
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
 
Last edited:
Upvote 0
Sorry Jessica - Im having a bad day :)

I see your trying to NextPO as part of the Worksheet Change module so we need to keep it. The problem is you can't start to define NextPO from within another procedure that is incomplete as your doing.
It would need to be defined separately and then used rather that defined in Workbook Open (Done by stating it without the sub qualifier which means its being defined. This arrangement should work without errors:

Code:
 Private Sub Workbook_Open()
 NxtPO
   End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

SavePOWithNewName
End Sub

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
                            NxtPO
End Sub

Sub NxtPO()

 Range("G4").Value = Range("G4").Value + 1
        Range("A16:E32").ClearContents
            Range("G3").Value = Date
End Sub
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0
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

Hi Mark,

Thanks so much for coming back to me...

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...

What do you think? Thanks again in advance,

Jessica
 
Upvote 0
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 ?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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
Back
Top