Page 1 of 3 123 LastLast
Results 1 to 15 of 37
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    How to autosave a template?

    HI I need some help with VBA


    I have a Purchase Order form which has a self incrementing cell in B8
    I do not want to rely on users to save the file with a different name to avoid overwriting.


    Is it possible to make the file automaticlly save ie Thisworkbook.saveas for example


    Something along these lines:


    Saveas C:\mybackup\po_ then add the po number from cell B8 so that the file would be saved as po_00001


    I would like this to happen on the workbook open event if possible.


    So the user would not even see the origional template.

    I am using Excel 2010


    Many Thanks in advance


    Regards


    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    For example:
    Code:
    Private Sub Workbook_Open()   
    Dim strFile As String
       With ThisWorkbook
          strFile = "c:\mybackup\po_" & LCase$(.Sheets(1).Range("B8").Value) & ".xls"
          If LCase$(.FullName) <> strFile Then .SaveAs strFile
       End With
          
    End Sub
    Last edited by rory; 2011-09-27 at 07:37. Reason: correct code.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post
    Hi Rory
    Thanks for the reply, when I enter the code this line shows red for error Private Sub Workbook_Open()Dim strFile As String

    Regards
    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Sorry - that should be two separate lines. The paste seems to have messed it up. I have amended it in the original post.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post
    Hi Rory

    That's great thanks, could I impose a littler further B8 is formatted with leading zeros, is it possible to for it to save as 00001
    Thanks
    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Sure - just replace .Range("B8").Value with .Range("B8").Text
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post
    Hi Rory
    Sorry to bother you further but I need to add my code to increment B8, but I can't get the sequence correct
    Thanks
    Braddy

    Private Sub Workbook_Open()
    ActiveSheet.Unprotect Password:="aab"
    With Range("B8")
    .NumberFormat = "00000"
    .Value = .Value + 1
    End With
    Dim strFile As String
    With ThisWorkbook
    strFile = "c:\mybackup\po_" & LCase$(.Sheets(1).Range("B8").Text) & ".xls"
    If LCase$(.FullName) <> strFile Then .SaveAs strFile(this is the part highlighted
    End With
    End Sub
    If you are a fool at forty, you will always be a fool

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Are you getting an error? If so, what is the message?
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post
    Hi Rory

    It appears that the B8 Won't increment and I get this message

    error.jpgerror.jpg

    Regards Braddy
    If you are a fool at forty, you will always be a fool

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post
    Hi Rory

    I thought I might attach file.

    Regards

    Braddydpc_purchase_order _final.xlsm
    If you are a fool at forty, you will always be a fool

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Works OK for me, but you should specify the sheet, so change:
    Code:
    With Range("B8")
    to:
    Code:
    With Sheets(1).Range("B8")
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post
    Hi Rory

    I'm sorry if I am missing something but isn't this in your original code

    Regards
    Braddy


    With Sheets(1).Range("B8")
    If you are a fool at forty, you will always be a fool

  13. #13
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post
    Hi Rory

    It seems to run once then I get this error.bmp

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  14. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I was referring to the second line of your Workbook_Open code.
    It would help to see the error itself, not the debug.
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #15
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post
    Hi Rory

    I am sorry if I am confusing you, It's not an error as such, what happens is when I open it the first time it works fine, then when I open it a second time the number in B8 does not increase by 1, the message I get is the warning that there is already there is already a file with this name do you want to overwrite. As shown above.
    I cant' understand why B8 won't increment on the second and subsequent runs.

    Hope this is clearer

    Many Thanks
    Braddy
    If you are a fool at forty, you will always be a fool

Page 1 of 3 123 LastLast

Posting Permissions

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