Userform CommandButton to Populate based on CheckBox True/False

Question

My workbook has sheet1 = Overview (contains button to launch UserForm1). My Userform has 24 CheckBox and 2 CommandButton (OK and Cancel).

Each CheckBox is associated with a sheet in the workbook. Each of the subsequent sheets is labelled PQC 1001, PQC 1002, etc., that contain data (images pushed to the upper left in A1 for each sheet).

My goal is to have the user check the CheckBox for each of the items they want to use, then select "OK" (CommandButton1) to have it copy the data to a new workbook for them to print out.

Later, I intend to define printing parameters to have the items paginated, but I need to get the basics before I go for the gold.

Currently I have the following code (working with first and second CheckBox to make sure they work, then I intended to expand by copying the code and modifying for the correct sheet/CheckBox):

Sub CommandButton1_Click()

 Dim WB As Workbook

 If CheckBox1.Value = True Then
    sheets("PQC 1001").Copy
    Set NewBook = Workbooks.Add
        With NewBook
        End With
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
    Else: CheckBox1.Value = False
  End If

 If CheckBox2.Value = True Then
 sheets("PQC 1002").Copy
     Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
    Else: CheckBox2.Value = False
  End If

End Sub

Sub CommandButton2_Click()
 Unload Me
 End
End Sub

An error is triggered when I run this code (having checked CheckBox1 only): PasteSpecial method of range class failed. Beyond the code issue, I have 2 workbooks spawned; the first has just the PQC 1001 sheet in it ans the second workbook is blank (unless I copied anything else, e.g. when I copy/pasted the code before modifying, the code I copied pasted into cell A1 of the second workbook).

I honestly don't know where to go from here. I've spent a bit of time searching on Google for possible resolution and have tried looking for a few terms/phrases in Stack Overflow's search bar. I think I might be looking up the incorrect term names, so that could make a difference ("commandbutton to paste into newbook" being one).

As a general note, this is essentially the third thing I have ever attempted to code; my educational background contains zero coding. The extent of my coding skill comes from some reading and feedback on the code I've written from persons more knowledgeable than I.

I think the problem is your reliance on the `Selection`. Try instead something to explicitly state the destination range, like `NewBook.Sheets(1).Range("A1").PasteSpecial...`. — David Zemens, May 05 '14 at 15:50
Thanks for that, David; that corrected the issue with the method range (I removed all of the "stuff" after PasteSpecial to make that work). Still getting 2 workbooks opened, though. — Cyril, May 05 '14 at 16:20
Er, I thought that fixed it; I had something on my clipboard for the item to paste into the second workbook, which appears to be that "pasteSpecial" error. — Cyril, May 05 '14 at 16:29

David Zemens · Accepted Answer · 2014-05-05T17:23:48.193

0

For your first issue, please avoid relying on Selection. Instead, you should explicitly state the destination range, like:

NewBook.Sheets(1).Range("A1").PasteSpecial...

Now, you are getting multiple workbooks open because the Sheets(_name_).Copy method will always return a new workbook if you have not specified a destination explicitly in the Copy statement.

http://msdn.microsoft.com/en-us/library/office/ff837784(v=office.15).aspx

Instead, specify the destination for the copied sheet using either the Before or After arguments of the Copy method:

Try this:

Dim NewBook as Workbook
Set wb = ActiveWorkbook 'Or ThisWorkbook, or Workbooks("workbookname.xlsx")
'#Create the new book outside of the If blocks.
Set NewBook = Workbooks.Add
'# Get rid of excess sheets
Do Until NewBook.Sheets.Count = 1
    NewBook.Sheets(1).Delete
Loop
If CheckBox1.Value = True Then
'Creates exact copy of the sheet PQC 1001 in the NEW workbook.
    wb.sheets("PQC 1001").Copy After:=NewBook.Sheets(1)
Else: CheckBox1.Value = False
End If
If CheckBox2.Value = True Then
     wb.sheets("PQC 1002").Copy After:=NewBook.Sheets(NewBook.Sheets.Count)
Else: CheckBox2.Value = False
End If

edited May 05 '14 at 17:23

answered May 05 '14 at 16:37

David Zemens

53,033
11
81
130

Runtime error "9," subscript out of range. It highlights the row below the comment which uses "Before." Should I get a pop-up when I try to type that in? Sometimes when I type words I get a little menu that tells me how to provide syntax for it. I'm mentioning that as I don't know if I don't have the correct reference for that argument (I've heard that phrase used before, don't actually know what it means). – Cyril May 05 '14 at 16:52
Ahhhh. Improper scoping. Hold on a second... I will revise. – David Zemens May 05 '14 at 16:54
Revised. Use the `WB` variable to set a handle to the activeworkbook before you add the new book. When you add the new book, the unqualified `Sheets("PQC 1001").Copy...` will raise an error because there is no such sheet in the `NewBook` (which is currently the active book). – David Zemens May 05 '14 at 16:56
That just did wonders. Testing the form with only CheckBox1 or CheckBox2 selected and noticing that the sheet must be defined on the NewBook. Is there a way to send a copy onto the first available, unused sheet? Alternatively, I could probably remove all "unused" sheets in NewBook at the end of the transaction. Additional: thanks for the help thusfar. This is seemingly overwhelming for me, so it really is appreciated. – Cyril May 05 '14 at 17:04
Okay; I can get the unused pages gone by recording a macro and removing unused pages. That's good, though each checkbox is creating its own workbook. I am wrong in this thinking, but I thought that defining "NewBook" as a workbook meant that only 1 could be generated, if it uses said term? – Cyril May 05 '14 at 17:14
Will do; much appreciated! – Cyril May 05 '14 at 17:24
I made another revision. Probably should create the `NewBook` outside of the `If` blocks. It may be helpful if you still have questions if you would update the code in your Question so that I can see what code you are using. – David Zemens May 05 '14 at 17:24
That actually resolved what I was just typing up. I am quite grateful you had the time to help me. Thank you, David! – Cyril May 05 '14 at 17:29

Userform CommandButton to Populate based on CheckBox True/False

1 Answers1