Creating new workbook

[asyamonique]

Good Day,
Is it possible to create new workbook which contains sheet1 datas only from master workbook by clicking command button,and the name of workbook will be asking by inputbox and create accordingly??
Many Thanks.

 

[John Davis]

Good Day,
Is it possible to create new workbook which contains sheet1 datas only from master workbook by clicking command button,and the name of workbook will be asking by inputbox and create accordingly??
Many Thanks.

Do you mean something like this?

Sub NewWorkbook()
'
'
Application.DisplayAlerts = False
Dim i As Integer
Dim FileName1 As String
For i = 2 To (how ever many sheets are in the Master)
Worksheets("Sheet" & i).Select
ActiveWindow.SelectedSheets.Delete
Next i
FileName1 = InputBox("Enter New Workbook Name")
ActiveWorkbook.SaveAs Filename:="D:\" & FileName1, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True
End Sub

 

[asyamonique]

Thanks for your reply,but this code creats whole workbook .Actually nothing wrong with it but i need to get only sheet1 values as it is..
My master workbook has large of entries i don't need to create whole copy of workbook.Hope somone can help me on this problem.
Many Thanks

Sub Button1_Click()
Application.DisplayAlerts = False
Dim i As Integer
Dim FileName1 As String
For i = 2 To (1)
Worksheets("Sheet1" & i).Select
ActiveWindow.SelectedSheets.Delete
Next i
FileName1 = InputBox("Enter New Workbook Name")
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\me\Desktop\work" & FileName1, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True
End Sub

 

[John Davis]

Thanks for your reply,but this code creats whole workbook .Actually nothing wrong with it but i need to get only sheet1 values as it is..
My master workbook has large of entries i don't need to create whole copy of workbook.Hope somone can help me on this problem.
Many Thanks

Sub Button1_Click()
Application.DisplayAlerts = False
Dim i As Integer
Dim FileName1 As String
For i = 2 To (1)
Worksheets("Sheet1" & i).Select
ActiveWindow.SelectedSheets.Delete
Next i
FileName1 = InputBox("Enter New Workbook Name")
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\me\Desktop\work" & FileName1, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True
End Sub

Your welcome. I thought the code was what you wanted. You asked "to create new workbook which contains sheet1 datas only". The For Loop as I sent it eliminates all sheets from the master except Sheet1. You made some changes too the For Loop (i.e. I gave For i = 2 To the number of sheets in the master - you changed to too For i = 2 To (1); Also, I gave Worksheets("Sheet" & i).Select - you changed too Worksheets("Sheet1" & i).


You asked "the name of workbook will be asking by inputbox and create accordingly?". The second part of the code creates a new workbook and prompts for a name.??

Altogether when you run the code you should have a new workbook, with sheet1 data only, and you fill in the name by prompt. If you wanted the master to remain on the screen, you could add an open command at the end of the code.

Anyway. That was my understanding of what you wanted. There's probably a better way, hope someone can help you.

 

[asyamonique]

Jonh have you tested the code you've posted ?? its not working unless have some changes.But still creating the new workbook with all datas including macros....
Thanks

Sub NewWorkbook()
'
'
Application.DisplayAlerts = False
Dim i As Integer
Dim FileName1 As String
For i = 2 To (how ever many sheets are in the Master)
Worksheets("Sheet" & i).Select
ActiveWindow.SelectedSheets.Delete
Next i
FileName1 = InputBox("Enter New Workbook Name")
ActiveWorkbook.SaveAs Filename:="D:\" & FileName1, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True
End Sub <!-- / message -->

 

[micro-blades]

Try:

sheet1.copy

You can use additional code to save the workbook with the appropriate name.

Brian

 

[westconn1]

x = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Set newbook = Workbooks.Add
Application.SheetsInNewWorkbook = x
ActiveWorkbook.Sheets("sheet1").Copy (newbook.Sheets(1))
Application.DisplayAlerts = False
newbook.Sheets(2).Delete
Application.DisplayAlerts = True
Application.Dialogs(xlDialogSaveAs).Show

the user can dump the new workbook if he clicks cancel to the saveas

 

[westconn1]

some reason, can't edit
corrected that the newworkbook becomes activeworkbook

n = ActiveWorkbook.Name
x = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Set newbook = Workbooks.Add
Application.SheetsInNewWorkbook = x
Workbooks(n).Sheets("sheet1").Copy (newbook.Sheets(1))
Application.DisplayAlerts = False
newbook.Sheets(2).Delete
Application.DisplayAlerts = True
Application.Dialogs(xlDialogSaveAs).Show

 

[asyamonique]

Thanks WestConn,
After Saving the file how can we exit the new workbook?

 

[westconn1]

newbook.close