+ Reply to Thread
Results 1 to 2 of 2

Naming a file using an inputbox

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    03-28-2012
    Location
    Pennsylvania, USA
    MS-Off Ver
    Excel 2010
    Posts
    166

    Question Naming a file using an inputbox

    Here is the code;

    Sub CopySheetNewWorkbook()
    
        
        Dim CQuestion As String
        Dim Response As String
        Dim wbName As Variant
        Dim wsName As String
        Dim strPath As String
        Dim vFile As Variant
        Dim MyFile As Workbook
        
        'Copy sheet as a new workbook
        CQuestion = "Do you want to open another workbook for the year?"
        Response = MsgBox(CQuestion, vbQuestion + vbYesNoCancel, "???")
          If Response = vbNo Then
          Workbooks.Open Filename:="2012.xls", UpdateLinks:=True
          Worksheets.Add After:=Worksheets(Worksheets.Count)
          wsName = InputBox("Enter name for new worksheet", "New sheet")
            If wsName <> "" Then
               With ActiveSheet
                .Name = wsName
                .Visible = True
               End With
            End If
           If wsName = "False" Then Exit Sub 'User Canceled
           On Error GoTo GetOut
           
          End If
          If Response = vbYes Then
            Sheets("Reports").Activate
            Sheets("Reports").Copy
                   wbName = InputBox("Enter current year for linen reports", "New Worbook")
                   ActiveWorkbook.SaveAs Filename:=wbName
                      wsName = InputBox("Enter name for new worksheet", "New sheet")
                      If wsName <> "" Then
                         With ActiveSheet
                           .Name = wsName
                           .Visible = True
                         End With
                      End If
          End If
          If Response = vbCancel Then Exit Sub
                      
        If wsName = "False" Then Exit Sub 'User Canceled
        ActiveWorkbook.SaveAs Filename:=wbName
        
        'Close the copied and saved workbook
        ActiveWorkbook.Close SaveChanges:=False
           
        
        
        
    GetOut:
      End Sub
    What I am trying to do is instead of having to enter a year into this code (Workbooks.Open Filename:="2012.xls", UpdateLinks:=True) is there anyway to use it right from when the user inputs the date when this line of code is executed? (wbName = InputBox("Enter current year for linen reports", "New Worbook")

    Intead of the person managing the code having to go in and change the 2012.xls each year when the user answers yes and input the year in can
    it be automatically updated then?

    Thanks,
    LeapingLizards

  2. #2
    Forum Contributor
    Join Date
    03-28-2012
    Location
    Pennsylvania, USA
    MS-Off Ver
    Excel 2010
    Posts
    166

    Re: Naming a file using an inputbox

    Or are there any suggestions as to how I might accomplish this task?

+ 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