Creating a New Excel File Using VBA

[Jzelenka]

Hello,

I have created a user interface so that the users can create a new file from a template with a click of a button. The command buttons is on sheet6 and the template I want copied is on sheet5.

When they click the button, I have a text box come up that askes for the file name. When they type in the name and hit okay, I want a new excel file created in the same folder named after what has been typed in the textbox (textbox1), with sheet5 of the original file copied and pasted into the new file. I am getting very stuck with this, and I keep getting errors. Any suggestions?

Thanks!

 

[Jaafar Tribak]

Hi,

Place the Code below in the CommandButto1 Of Sheet6:

Private Sub CommandButton1_Click()
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
TryAgain:
    Flname = InputBox("Enter File Name :", "Creating New File...")
    MsgBox Len(Flname)
    If Flname <> "" Then
        Set NewWkbk = Workbooks.Add
        ThisWorkbook.Sheets(5).Copy Before:=NewWkbk.Sheets(1)
        NewWkbk.SaveAs ThisWorkbook.Path & "\" & Flname
        If Err.Number = 1004 Then
            NewWkbk.Close
            MsgBox "File Name Not Valid" & vbCrLf & vbCrLf & "Try Again."
            GoTo TryAgain
        End If
        ActiveWorkbook.Close
    End If
End Sub

Hope this helps.

 

[Jzelenka]

sorry I have not responded...Been out of town for a week...

Anyway, Thanks for the help, but I have another quick question. It did not copy the sheet I wanted into the new file. It made the new file in the folder, It was just empty. so rather than trying to copy and paste, Can I just create a duplicate of the file, and have it named whatever they type into the input box? This way, I can then write more code to edit and delete the sheets and the columns that I no longer want in the new file. Basically, the original file is for internal use and has a lot more needed information, and the new sheet is used to send out of the department and only needs the bear minimum of information....

any suggestions?

 

[Jzelenka]

actually, I just figured it out. I typed this line into the code...ThisWorkbook.Sheets.Copy Before:=NewWkbk.Sheets(1).

Sorry for not needing help on this anymore...

 

[Jzelenka]

I spoke too soon.

Now, on the new work sheet that was just created, I want to delete all sheets except sheet7. and then on sheet7 I want to delete columns g,h,i,j,k,l, and format the page to portrait, fitting on one page wide and how ever many needed for long....

I keep getting stuck on the formatting stuff. Usually for formatting I have used the macro recorder, but I am not sure how to do that with this changing name of the file variable...

Is there a way for the macro recorder to access the newly created file no matter what the name is? or should I write code for all of the above formats and how would I go abotu that???

Sorry for the constants posts on this issue...but I really stuck.

 

[Jzelenka]

Still wondering about the formatting questions from the last post...but I just realized I had a huge problem.

When copying the sheets into the new file, is there any way to copy all the vbcode, modules, and userforms also into the new file??? Without those also carried over with the information/data...this project is useless!!!HELP!

 

[LTunnicliffe]

I think what you might have to do in your macro is Save As the file and then delete all of the information you don't want in the "new" workbook. This will preserve your original document but allow you to carry over all of the modules, userforms, etc. from the orignal. Make sense?

Hope this helps!

 

[Jzelenka]

well, with the code I have right now:
Flname = InputBox("Enter File Name :", "Creating New File...")
If Flname <> "" Then
Set newwkbk = Workbooks.Add
ThisWorkbook.Sheets.Copy Before:=newwkbk.Sheets(1)
newwkbk.SaveAs ThisWorkbook.Path & "\" & Flname
If Err.Number = 1004 Then
newwkbk.Close
MsgBox "File Name Not Valid" & vbCrLf & vbCrLf & "Try Again."
GoTo TryAgain
End If

this is creating a new workbook. How would I go about savingas, with the user still getting prompted for a file name? and how would the saveas save as that new file name?

 

[LTunnicliffe]

flname = InputBox("Enter File Name :", "Creating New File...")
If flname <> "" Then
  ThisWorkbook.SaveAs ThisWorkbook.Path & "\" & flname
  '  Now delete what you don't want because the file is the newfile
End If

Hope this helps!