+ Reply to Thread
Results 1 to 7 of 7

save a sheet with code on workbook_open included

Hybrid View

  1. #1
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    867

    save a sheet with code on workbook_open included

    Hi again,

    I'm dealing with a new challenge and I need your help.
    I'm saving only a sheet using the following code:

    Application.ScreenUpdating = False
    Worksheets("BULETIN").Select
    Worksheets("BULETIN").Copy
    
    ActiveSheet.Cells.Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
           Application.CutCopyMode = False
    
    ThisFile = Range("E13").Text & "-" & Range("A" & q).Value
    Worksheets("BULETIN").SaveAs Filename:="Z:\blahblah\" & ThisFile & ".xls", FileFormat:=xlExcel8
    Application.ScreenUpdating = True
    ActiveWorkbook.Close
    The question is: is there a way to put a vba code on this new workbook open method?
    The goal is that every new xls created to have the following code on workbook_open:
    
        Dim objNet As Object
        On Error Resume Next
        Set objNet = CreateObject("WScript.NetWork")
        MsgBox "Network username is: " & objNet.UserName
        Set objNet = Nothing
    thank you.

  2. #2
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: save a sheet with code on workbook_open included

    Instead of copying the worksheet to a new book, why not just save the current workbook under a new filename and then re-open the original
    Elegant Simplicity............. Not Always

  3. #3
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    867

    Re: save a sheet with code on workbook_open included

    because my workbook has multiple worksheets and I only save 1 of them. and i save that worksheet multiple times to multiple files :
    For q = 23 To 32
    [...]
    
    ThisFile = Range("E13").Text & "-" & Range("A" & q).Value
    
    [...]
    Next q

  4. #4
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: save a sheet with code on workbook_open included

    also
    msgbox environ("username")
    is simpler ;-)
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644
    Use a template which has the workbook code in it.
    Last edited by Norie; 04-10-2013 at 07:09 AM.
    If posting code please use code tags, see here.

  6. #6
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: save a sheet with code on workbook_open included

    Or delete the sheets you don't want, save under a new name then reopen the original file.

  7. #7
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    867

    Re: save a sheet with code on workbook_open included

    I saved my file using Worksheets("name").copy
    and added code on workbook_open using VBIDE.CodeModule
    .. and don't forget to have fun!
    Bogdan.

    mark SOLVED and Add Reputation if my answer pleases you

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1