Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

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

  1. #1
    New Member
    Join Date
    Oct 2014
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation 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!!
    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 RoryA; Oct 14th, 2014 at 12:43 PM.

  2. #2
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,380
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Compile Error: Expected End Sub - 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
    you don't call a sub with the title

    NextPO will have to exist in the workbook already to avoid an error
    • Yes I know there are better ways to do it. I just wish I knew them. - 2003, 2007, 2010, 2013 & 2016
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  3. #3
    Board Regular
    Join Date
    Oct 2007
    Location
    York, England
    Posts
    545
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compile Error: Expected End Sub - HELP!!!

    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()

  4. #4
    New Member
    Join Date
    Oct 2014
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compile Error: Expected End Sub - HELP!!!

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

  5. #5
    Board Regular
    Join Date
    Oct 2007
    Location
    York, England
    Posts
    545
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compile Error: Expected End Sub - HELP!!!

    Quote Originally Posted by jessicaspencer View Post
    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.

    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 by Hercules1946; Oct 14th, 2014 at 01:03 PM.

  6. #6
    Board Regular
    Join Date
    Oct 2007
    Location
    York, England
    Posts
    545
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compile Error: Expected End Sub - HELP!!!

    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

  7. #7
    New Member
    Join Date
    Oct 2014
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compile Error: Expected End Sub - HELP!!!

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

  8. #8
    MrExcel MVP
    Join Date
    Dec 2008
    Location
    Phoenix, Arizona
    Posts
    6,154
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compile Error: Expected End Sub - HELP!!!

    Quote Originally Posted by jessicaspencer View Post
    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

  9. #9
    New Member
    Join Date
    Oct 2014
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compile Error: Expected End Sub - HELP!!!

    Quote Originally Posted by GTO View Post
    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

  10. #10
    Board Regular
    Join Date
    Oct 2007
    Location
    York, England
    Posts
    545
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compile Error: Expected End Sub - HELP!!!

    Quote Originally Posted by jessicaspencer View Post
    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 by Hercules1946; Oct 15th, 2014 at 12:20 PM.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •