I'm having a program in which one command button is provided by me. By
clicking the Command button, a menu will appear which will show a list of
available sheets in the program. An user can select the sheet he wants (to
save it in a separate workbook) by just clicking the Checkbox against each
sheet name in the menu.
Once if he select and click the ("SAVE") button in the menu, those
particular sheets will be saved in a Separte workbook in C directory.
I made the program and Codings already. The problem is I used the Command
Called ("Activewindow Active next (or) Active previous). So as per the
command it will copy the sheet from my master program (workbook) and it will
paste it in a new workbook . Again it will deactivate the new workbook and
activate my master workbook for copying an another sheet (which was selected
by the user in the menu). During this time, if my user run any other workbook
other than this, the above COmmand will activate that book and the whole
program will be collapsed. Please suggest me whehter it is possible to go
with some other option for performing the above action.
Thankyou in advance
Regards,
Premanand Sethuraman.
I've provided the coding below,

Private Sub CommandButton1_Click()
Dim fname As Range
Set fname = Worksheets("Preorder").Range("B2")
If CheckBox1.Value = True And CheckBox2.Value = True Then
MsgBox ("You should enter either Thermax's Format or Consutant/Customer's
Format for Bag Filter Technical data")
ElseIf CheckBox3.Value = True And CheckBox4.Value = True Then
MsgBox ("You should enter either Thermax's Format or Consutant/Customer's
Format for Fan Technical data")
ElseIf CheckBox12.Value = True And CheckBox13.Value = True Then
MsgBox ("You should enter either Thermax's Format or Consutant/Customer's
Format for Scope of Supply")
Else
Workbooks.Add
ChDir "C:\"
If ActiveWorkbook.RevisionNumber = 0 Then
ActiveWorkbook.SaveAs _
Filename:=fname, _
accessMode:=xl, _
ConflictResolution:= _
xlOtherSessionChanges
End If
ActiveWindow.ActivateNext
If UserForm9.CheckBox1.Value = True Then
Worksheets("Results").Activate
ActiveSheet.Cells.Select
Selection.Copy
ActiveWindow.ActivatePrevious
ActiveSheet.Cells.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
ActiveSheet.Name = "Tech data - Thermax"
Else
ActiveWindow.ActivateNext
End If
ActiveWindow.ActivateNext
If UserForm9.CheckBox2.Value = True Then
Worksheets("Techdata").Activate
ActiveSheet.Cells.Select
Selection.Copy
ActiveWindow.ActivatePrevious
Worksheets.Add
ActiveSheet.Cells.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
ActiveSheet.Name = "Tech data - Others"
Else
ActiveWindow.ActivateNext
End If
ActiveWindow.ActivateNext
If UserForm9.CheckBox3.Value = True Then
Worksheets("Fanresult").Activate
ActiveSheet.Cells.Select
Selection.Copy
ActiveWindow.ActivatePrevious
Worksheets.Add
ActiveSheet.Name = "Fan - Thermax"
ActiveSheet.Cells.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Else
ActiveWindow.ActivateNext
End If
ActiveWindow.ActivateNext
If UserForm9.CheckBox4.Value = True Then
Worksheets("Techfan").Activate
ActiveSheet.Cells.Select
Selection.Copy
ActiveWindow.ActivatePrevious
Worksheets.Add
ActiveSheet.Name = "Fan - Others"
ActiveSheet.Cells.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Else
ActiveWindow.ActivateNext
End If
ActiveWindow.ActivateNext
If UserForm9.CheckBox5.Value = True Then
Worksheets("Notes").Activate
ActiveSheet.Cells.Select
Selection.Copy
ActiveWindow.ActivatePrevious
Worksheets.Add
ActiveSheet.Name = "Notes"
ActiveSheet.Cells.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Else
ActiveWindow.ActivateNext
End If
ActiveWindow.ActivateNext
If UserForm9.CheckBox6.Value = True Then
Worksheets("boitems").Activate
ActiveSheet.Cells.Select
Selection.Copy
ActiveWindow.ActivatePrevious
Worksheets.Add
ActiveSheet.Name = "Accessories"
ActiveSheet.Cells.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Else
ActiveWindow.ActivateNext
End If
ActiveWindow.ActivateNext
If UserForm9.CheckBox7.Value = True Then
Worksheets("Batlimits").Activate
ActiveSheet.Cells.Select
Selection.Copy
ActiveWindow.ActivatePrevious
Worksheets.Add
ActiveSheet.Name = "Batterylimits"
ActiveSheet.Cells.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Else
ActiveWindow.ActivateNext
End If
ActiveWindow.ActivateNext
If UserForm9.CheckBox8.Value = True Then
Worksheets("Sitecon").Activate
ActiveSheet.Cells.Select
Selection.Copy
ActiveWindow.ActivatePrevious
Worksheets.Add
ActiveSheet.Name = "Site Conditions"
ActiveSheet.Cells.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Else
ActiveWindow.ActivateNext
End If
ActiveWindow.ActivateNext
If UserForm9.CheckBox9.Value = True Then
Worksheets("Duct").Activate
ActiveSheet.Cells.Select
Selection.Copy
ActiveWindow.ActivatePrevious
Worksheets.Add
ActiveSheet.Name = "Duct"
ActiveSheet.Cells.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Else
ActiveWindow.ActivateNext
End If
ActiveWindow.ActivateNext
If UserForm9.CheckBox10.Value = True Then
Worksheets("Appmake").Activate
ActiveSheet.Cells.Select
Selection.Copy
ActiveWindow.ActivatePrevious
Worksheets.Add
ActiveSheet.Name = "Approval make"
ActiveSheet.Cells.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Else
ActiveWindow.ActivateNext
End If
ActiveWindow.ActivateNext
If UserForm9.CheckBox11.Value = True Then
Worksheets("Paint").Activate
ActiveSheet.Cells.Select
Selection.Copy
ActiveWindow.ActivatePrevious
Worksheets.Add
ActiveSheet.Name = "Paint"
ActiveSheet.Cells.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Else
ActiveWindow.ActivateNext
End If
ActiveWindow.ActivateNext
If UserForm9.CheckBox12.Value = True Then
Worksheets("Scope").Activate
ActiveSheet.Cells.Select
Selection.Copy
ActiveWindow.ActivatePrevious
Worksheets.Add
ActiveSheet.Name = "Scope - Thermax"
ActiveSheet.Cells.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Else
ActiveWindow.ActivateNext
End If
ActiveWindow.ActivateNext
If UserForm9.CheckBox13.Value = True Then
Worksheets("Scopeformat").Activate
ActiveSheet.Cells.Select
Selection.Copy
ActiveWindow.ActivatePrevious
Worksheets.Add
ActiveSheet.Name = "Scope - Others"
ActiveSheet.Cells.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Else
ActiveWindow.ActivateNext
End If
ActiveWorkbook.Save
ActiveWorkbook.Close
ActiveWindow.ActivateNext
UserForm9.Hide
Worksheets("Preorder").Visible = True
Worksheets("Preorder").Activate
ActiveSheet.Range("A4").Select
ActiveWindow.DisplayWorkbookTabs = False
MsgBox (fname & " was saved in C Directory")
End If
End Sub