Checkbox that copies and pastes selected rows into different sheet

Question

I am trying to find out how to copy three rows from one sheet to another based on clicking a checkbox. I am using code from here:

Private Sub CheckBox1_Click()
Dim SrcRange As Range, TrgRange As Range

   Set SrcRange = Worksheets("Data Sheet").[B1]
   Set TrgRange = Worksheets("Sheet1").[C1]

   If CheckBox1 Then
       TrgRange = SrcRange
   Else
       TrgRange = ""
   End If
End Sub

This is the code I am currently using for this process:

Private Sub CheckBox1_Click()
Dim SrcRange As Range, TrgRange As Range

   Set SrcRange = Worksheets("Data Sheet").Range("A1:E1")
   Set TrgRange = Worksheets("Sheet1").Range("A1:E1")

   Set SrcRange = Worksheets("Data Sheet").Range("A2:E2")
   Set TrgRange = Worksheets("Sheet1").Range("A2:E2")

   Set SrcRange = Worksheets("Data Sheet").Range("A3:E3")
   Set TrgRange = Worksheets("Sheet1").Range("A3:E3")

   If CheckBox1 Then
       TrgRange = SrcRange
   Else
       TrgRange = ""
   End If
End Sub

When using this code no error is provided, however none of the data is copied and pasted into the selected worksheet.

Thanks Dave! Really appreciate the quick response and have a great weekend. — MGurz, Oct 23 '15 at 13:15
I'm assuming that Dave's answer worked, which is great. I had trouble on my end getting `CheckBox1` to evaluate to anything at all. I looked at [link](http://stackoverflow.com/questions/11741836/checking-if-a-worksheet-based-checkbox-is-checked) and saw I could use `ActiveSheet.Shapes("Check Box 1").OLEFormat.Object.Value = 1` as a work around. — Demetri, Oct 23 '15 at 13:23
Actually Dave, would there be a way to select multiple specific rows within the same sub box? — MGurz, Oct 23 '15 at 13:39
Yes, for example, in my answer `Worksheets("Data Sheet").Range("A1:E3")` affects three rows. The range from A1 to E3. — Alex Weber, Oct 23 '15 at 13:59
Sorry about that Alex, yes that was the answer that both yourself and coderI provided. It works. Thanks! — MGurz, Oct 23 '15 at 14:02
@MGurz be sure to pick an someone for answer the question! Thanks! — Alex Weber, Oct 23 '15 at 15:47

score 0 · Answer 1 · answered Oct 23 '15 at 12:59

0

I think you want to use:

If CheckBox1 Then
    TrgRange = SrcRange.Value
Else
    TrgRange = ""
End If

But I'm not sure on why you are changing the ranges three times before getting to this part of the code, I'm sure this will come up as another question...

answered Oct 23 '15 at 12:59

Dave

1,643
1
9
9

I am trying to select three different rows and noticed that when executed, only the last row was provided – MGurz Oct 23 '15 at 13:42

ga56mor · Answer 2 · 2015-10-23T13:12:57.643

0

your problem is that you assign to the TrgRange variable another Range variable, but not the value in the Range variable. SrcRange reference to the range you selected. Try something like this to copy values :

TrgRange.Copy
SrcRange.Paste

edited Oct 23 '15 at 13:12

answered Oct 23 '15 at 13:07

ga56mor

73
8

score 0 · Accepted Answer · answered Oct 23 '15 at 13:12

Use the following sub

 Sub CheckBox1_Click()

   Dim SrcRange As Range
   Dim TrgRange As Range

    If CheckBox1 = 1 Then
        Set SrcRange = Worksheets("Data Sheet").Range("A1:E3")
        Set TrgRange = Worksheets("Sheet1").Range("A1:E3")

        SrcRange.Copy
        TrgRange.PasteSpecial _
        Paste:=xlPasteValues

    End If

End Sub

score 0 · Answer 4 · answered Oct 23 '15 at 13:50

Looks like you missed a critical part of the original solution.

Essentially, the original solution is:

'TrgRange = SrcRange 
Worksheets("Sheet1").[C1] = Worksheets("Data Sheet").[B1]

Which in your case would translate to be:

'trailing .Value is required for Ranges
Worksheets("Sheet1").Range("A1:E1") = Worksheets("Data Sheet").Range("A1:E1").Value
Worksheets("Sheet1").Range("A2:E2") = Worksheets("Data Sheet").Range("A2:E2").Value
Worksheets("Sheet1").Range("A3:E3") = Worksheets("Data Sheet").Range("A3:E3").Value

Which can be simplified to be just:

Worksheets("Sheet1").Range("A1:E3") = Worksheets("Data Sheet").Range("A1:E3").Value

Thanks for the breakdown. Really helps clean up the code. – MGurz Oct 23 '15 at 14:29 — MGurz, Oct 23 '15 at 14:29

Checkbox that copies and pastes selected rows into different sheet

4 Answers4