title: Excel/VBA Macros assistance
body: <p>I am having a bit of trouble with some code and was wondering if someone could maybe assist. Basically I have 2 errors which I can't work out myself (too inexperienced with VBA, unfortunately)</p>
  
  <p><strong>Brief overview:</strong> </p>
  
  <p>This macro is designed to generate a new workbook with copies of selected sheets from a "source" workbook in order to present to clients as a report batch. Essentially - we have master workbook "A" which may have 50 tabs or so, and we want to quickly select a couple of sheets to "copy" into a new workbook to save and send to a client. The code is a bit of a mess but I am not really sure what is going on/what I can remove etc.</p>
  
  <p><strong>Problems:</strong></p>
  
  <ol>
  <li><p>When you run the attached code/macro in Excel, it does everything it is supposed to do, however, it ALSO copies the sheet from which you run the macro. (i.e. I might be on sheet 1 in the Workbook. Run the macro to generate reports, checkbox menu appears and I select sheets 2, 5 &amp; 9 - it will then copy into a new Workbook sheets 2, 5 &amp; 9 AND sheet 1. But I never selected sheet 1 from the checkbox menu...)</p></li>
  <li><p>Once this code has finished running, I am unable to save the Excel file. It just crashes and says "Microsoft Excel has stopped working" and then the file dies and I have to close Excel and recover etc. etc. I combined 2 pieces of code to get this working and I imagine I may be missing something crucial which is causing the problem. We have another piece of code to print sheets out in a similar way to this, and if I run this I am able to save with no problems.</p></li>
  </ol>
  
  <p><strong>Code:</strong></p>
  
  <p>I have included all the Visual Basic code (i.e. for the generate reports &amp; print sheets macros). </p>
  
  <p>I really don't have any experience with VBA so I hope someone will be able to assist! Thanks in advance :)</p>
  
  <pre><code>Sub PrintSelectedSheets()
  Dim i As Integer
  Dim TopPos As Integer
  Dim SheetCount As Integer
  Dim Printdlg As DialogSheet
  Dim CurrentSheet As Worksheet, wsStartSheet As Worksheet
  Dim CB As CheckBox
  Application.ScreenUpdating = False
  
  'Check for protected workbook
  If ActiveWorkbook.ProtectStructure Then
      MsgBox "Workbook is protected.", vbCritical
      Exit Sub
  End If
  
  'Add a temporary dialog sheet
  Set CurrentSheet = ActiveSheet
  Set wsStartSheet = ActiveSheet
  Set Printdlg = ActiveWorkbook.DialogSheets.Add
  
  SheetCount = 0
  
  'Add the checkboxes
  
  TopPos = 40
  For i = 1 To ActiveWorkbook.Worksheets.Count
      Set CurrentSheet = ActiveWorkbook.Worksheets(i)
      'Skip empty sheets and hidden sheets
      If Application.CountA(CurrentSheet.Cells) &lt;&gt; 0 And _
          CurrentSheet.Visible Then
          SheetCount = SheetCount + 1
          Printdlg.CheckBoxes.Add 78, TopPos, 150, 16.5
              Printdlg.CheckBoxes(SheetCount).Text = _
                  CurrentSheet.Name
          TopPos = TopPos + 13
      End If
  Next i
  
  'Move the OK and Cancel buttons
  Printdlg.Buttons.Left = 240
  
  'Set dialog height, width, and caption
  With Printdlg.DialogFrame
      .Height = Application.Max _
          (68, Printdlg.DialogFrame.Top + TopPos - 34)
      .Width = 230
      .Caption = "Select sheets to print"
  
  End With
  
  'Change tab order of OK and Cancel buttons
  'so the 1st option button will have the focus
  Printdlg.Buttons("Button 2").BringToFront
  Printdlg.Buttons("Button 3").BringToFront
  
  'Display the dialog box
  CurrentSheet.Activate
  wsStartSheet.Activate
  Application.ScreenUpdating = True
  If SheetCount &lt;&gt; 0 Then
  
  'the following code will print the selected sheets as multiple print jobs.
  'continuous page numbers will therefore not be printed
  
      If Printdlg.Show Then
  
          For Each CB In Printdlg.CheckBoxes
              If CB.Value = xlOn Then
                  Worksheets(CB.Caption).Activate
                  ActiveSheet.PrintOut
                  'ActiveSheet.PrintPreview 'for debugging
                  End If
                  Next CB
  
   'the following code will print the selected sheets as a single print job.
   'This will allow the sheets to be printed with continuous page numbers.
  
          'If Printdlg.Show Then
                  'For Each CB In Printdlg.CheckBoxes
                      'If CB.Value = xlOn Then
                          'Worksheets(CB.Caption).Select Replace:=False
                      'End If
                  'Next CB
                  'ActiveWindow.SelectedSheets.PrintOut copies:=1
                  'ActiveSheet.Select
          Else
              MsgBox "No worksheets selected"
          End If
      'End If
  
  End If
  
  'Delete temporary dialog sheet (without a warning)
  Application.DisplayAlerts = False
  Printdlg.Delete
  
  'Reactivate original sheet
  CurrentSheet.Activate
  wsStartSheet.Activate
  
  End Sub
  
  Sub GenerateClientExcelReports()
  
  '1. Declare variables
  
  Dim i As Integer
  Dim SheetCount As Integer
  Dim TopPos As Integer
  Dim lngCheckBoxes As Long, y As Long
  Dim intTopPos As Integer, intSheetCount As Integer
  Dim intHor As Integer       'this will be for the horizontal position of the items
  Dim intWidth As Integer     'this will be for the overall width of the dialog box
  Dim intLBLeft As Integer, intLBTop As Integer, intLBHeight As Integer
  Dim Printdlg As DialogSheet
  Dim CurrentSheet As Worksheet, wsStartSheet As Worksheet
  Dim CB As CheckBox
  
   'Dim wb As Workbook
   'Dim wbNew As Workbook
   'Set wb = ThisWorkbook
   'Workbooks.Add ' Open a new workbook
   'Set wbNew = ActiveWorkbook
  
  On Error Resume Next
  Application.ScreenUpdating = False
  
  '2.   Check for protected workbook
  
  If ActiveWorkbook.ProtectStructure Then
      MsgBox "Workbook is protected.", vbCritical
      Exit Sub
  End If
  
  '3.   Add a temporary dialog sheet
  Set CurrentSheet = ActiveSheet
  Set wsStartSheet = ActiveSheet
  Set Printdlg = ActiveWorkbook.DialogSheets.Add
  
  SheetCount = 0
  
  '4.   Add the checkboxes
  
  TopPos = 40
  For i = 1 To ActiveWorkbook.Worksheets.Count
      Set CurrentSheet = ActiveWorkbook.Worksheets(i)
  '5.       Skip empty sheets and hidden sheets
      If Application.CountA(CurrentSheet.Cells) &lt;&gt; 0 And _
          CurrentSheet.Visible Then
          SheetCount = SheetCount + 1
          Printdlg.CheckBoxes.Add 78, TopPos, 150, 16.5
              Printdlg.CheckBoxes(SheetCount).Text = _
                  CurrentSheet.Name
          TopPos = TopPos + 13
      End If
  Next i
  
  '6.   Move the OK and Cancel buttons
  Printdlg.Buttons.Left = 240
  
  '7.   Set dialog height, width, and caption
  With Printdlg.DialogFrame
      .Height = Application.Max _
          (68, Printdlg.DialogFrame.Top + TopPos - 34)
      .Width = 230
      .Caption = "Select sheets to generate"
  
  End With
  
  '8.   Change tab order of OK and Cancel buttons
  '   so the 1st option button will have the focus
  Printdlg.Buttons("Button 2").BringToFront
  Printdlg.Buttons("Button 3").BringToFront
  
  '9.   Display the dialog box
  CurrentSheet.Activate
  wsStartSheet.Activate
  Application.ScreenUpdating = True
  If SheetCount &lt;&gt; 0 Then
  
  
          If Printdlg.Show Then
                  For Each CB In Printdlg.CheckBoxes
  
                      If CB.Value = xlOn Then
                          Worksheets(CB.Caption).Select Replace:=False
  
                         'For y = 1 To ActiveWorkbook.Worksheets.Count
                              'If WorksheetFunction.IsNumber _
                              '(InStr(1, "ActiveWorkbook.Sheets(y)", "Contents")) = True Then
                                  'CB.y = xlOn
                              'End If
  
                      End If
  
                  Next
  
  
                  ActiveWindow.SelectedSheets.Copy
  
          Else
              MsgBox "No worksheets selected"
  
  
          End If
  
  End If
  
  'Delete temporary dialog sheet (without a warning)
  'Application.DisplayAlerts = False
  'Printdlg.Delete
  
  'Reactivate original sheet
  'CurrentSheet.Activate
  'wsStartSheet.Activate
  
  '10.   Delete temporary dialog sheet (without a warning)
  
  Application.DisplayAlerts = False
  Printdlg.Delete
  
  '11.   Reactivate original sheet
  
  CurrentSheet.Activate
  wsStartSheet.Activate
  Application.DisplayAlerts = True
  
  End Sub
  
  Sub SelectAllCheckBox()
  Dim CB As CheckBox
  
  For Each CB In ActiveSheet.CheckBoxes
      If CB.Name &lt;&gt; ActiveSheet.CheckBoxes(1).Text Then
          CB.Value = ActiveSheet.CheckBoxes(1).Value
      End If
  Next CB
  
  'ActiveSheet.CheckBoxes("Check Box 1").Value
  End Sub
  </code></pre>