Copy selected rows (checkboxes) (2/2)
This article demonstrates a macro that copies selected rows based on enabled check boxes. The image above shows data on Sheet1 and checkboxes, some of them are enabled and those will be copied to Sheet2 if you press with left mouse button on button "Copy selected rows".
This article is the last and second part, you can read the first part here:Excel vba: Add checkboxes to a sheet (1/2)
Table of Contents
1. Worksheet data
The image above shows Sheet2 and records copied from Sheet1.
2. VBA code
'Name of macro
Sub CopyRows()
'Go through each check box in active sheet
For Each chkbx In ActiveSheet.CheckBoxes
'If check box is enabled
If chkbx.Value = 1 Then
'Go through each row on worksheet
For r = 1 To Rows.Count
'Check if checkbox is on the same row
If Cells(r, 1).Top = chkbx.Top Then
'Simplify syntax
With Worksheets("Sheet2")
'Identify the cell right below the last non empty cell
LRow = .Range("A" & Rows.Count).End(xlUp).Row + 1
'Copy record from Sheet1 and paste to first empty row on Sheet2
.Range("A" & LRow & ":D" & LRow) = _
Worksheets("Sheet1").Range("A" & r & ":D" & r).Value
End With
'Exit For Loop
Exit For
End If
Next r
End If
Next
End Sub
3. Where to copy vba code?
- Copy above code.
- Press Alt+F11 in excel.
- Insert a module.
- Paste code into the code window.
- Return to Excel.
4. How to run the macro using a button
I have assigned the macro to a button: Copy selected rows
- Go to Developer tab
- Press with left mouse button on "Insert Controls" button
- Press with left mouse button on "Button" button
- Create the button
- Select CopyRows macro
- Press with left mouse button on OK!
Check boxes category
In this post, I will demonstrate a macro that creates checkboxes in nonempty rows, see checkboxes in column E in image […]
Today I will share a To-do list excel template with you. You can add text to the sheet and an […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
Excel categories
40 Responses to “Copy selected rows (checkboxes) (2/2)”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
In your CopyRows macro, since the CheckBox is actually a Shape underneath it all, we can eliminate your inner loop by making use of its TopLeftCell property to determine the row its on.
Sub CopyRows() Â Dim LRow As Long, ChkBx As CheckBox, WS2 As Worksheet Set WS2 = Worksheets("Sheet2") LRow = WS2.Range("A" & Rows.Count).End(xlUp).Row For Each ChkBx In ActiveSheet.CheckBoxes If ChkBx.Value = 1 Then LRow = LRow + 1 WS2.Cells(LRow, "A").Resize(, 4) = Range("A" & _ ChkBx.TopLeftCell.Row).Resize(, 4).Value End If Next End SubHi Rick & Oscar
I am a bit late to the show,
However I have used this code that Rick has altered slightly of Oscars however would you be able to assist on where in the code i can determine the exact cell on sheet 2 that the paste occurs.
I have managed to change the paste column from A to C but i cant seem to find a way to have the pasting start at cell C12 rather than C2.
Any help would be great.
Thank you
Rick Rothstein (MVP - Excel),
Thank you for your valuable comments!
Hi,
I have a similar problem - I would like to use checkboxes in "sheet1" and populate the rows of data into "sheet 2" and Sheet 3"
But can this be done without the use of a macro??
Maybe using auto sum or something??
Please help,
Regards,
Hello,
I have a similar issue. I am trying to replicate your example but for some reason it does not work form. It looks as though the macro runs, but does not pick up the check box. Also, the main difference is that my header begins on row 8, but my check boxes remain in column E.
Would you be able to please advise?
Thanks in advance!!
Carlos,
I assume you have read this post?
Excel vba: Add checkboxes to a sheet (1/2) The code for adding the checkboxes are in that post.
Also, the main difference is that my header begins on row 8
Change this line
to
and all lines containing "E" to your column.
Hi Oscar,
Many thanks for the response. I am still a bit confused on your instructions. Can you validate the changes I have made below:
Sub CopyRows() For Each chkbx In ActiveSheet.CheckBoxes If chkbx.Value = 1 Then For r = 8 To Rows.Count If Cells(r, 1).Top = chkbx.Top Then With Worksheets("Products Summary") '"Products Summary" is the name of tab where I want the copied cells to be place LRow = .Range("A" & Rows.Count).End(xlUp).Row + 1 .Range("A" & LRow & ":D" & LRow) = _ Worksheets("Product Data").Range("A" & r & ":D" & r).Value ' "Product Data" is the tab where my data is located End With Exit For End If Next r End If Next End SubMany thanks again!
Also, a correction to my previous comment. My header begins on both tabs on row 7. First check box in the "Product Data" tab is on row 10. Not sure if this makes a difference.
Thanks again!
Carlos,
Yes, it seems fine. If it doesn´t work upload the file.
Dear Oscar,
When we apply the script shown above, is possible specify, for the copied rows, those columns that we really want?
( Imagine that I have 10 columns with information, but I just want to copy the information available in 5 of them. )
Best Regards and thanks for all your availability to make clear this kind of matters !
Hi Oscar,
I´ve searched for awhile, but didn´t found a solution :-(
Your vba code works fine - nice work!
It´s possible to transfer besides the values, the format (Width, High, Border, Color,..), too?
thanks for your support.
regards from Germany
Hello Mike,
try this:
Sub CopyRows() Application.ScreenUpdating = False For Each chkbx In ActiveSheet.CheckBoxes If chkbx.Value = 1 Then For r = 1 To Rows.Count If Cells(r, 1).Top = chkbx.Top Then With Worksheets("Sheet2") Worksheets("Sheet1").Range("A" & r & ":D" & r).Copy LRow = .Range("A" & Rows.Count).End(xlUp).Row + 1 .Range("A" & LRow & ":D" & LRow).PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False .Range("A" & LRow & ":D" & LRow).PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False End With Application.CutCopyMode = False Exit For End If Next r End If Next Application.ScreenUpdating = True End SubGet the Excel *.xlsm file
Copy-selected-rows-checkboxesv2.xlsm
Hi,
Just want to ask, for this macro when it copies it seems to copy the rows more than once. Any insights how to ammend this?
Bests
Melvin
Hi,
I just wanted to know how i can stop the duplication in the copy and paste section
thanks
HI I am looking for this Fucntion for my attendance thanks
i,
Just want to ask, for this macro when it copies it seems to copy the rows more than once. Any insights how to automatically delete the duplicate rows from the sheet the rows are copied to?
Will ActiveSheet("sheet2").clear if put at the start of the CopyRows sub?
Oscar,
I am running the code from Rick and everything seems to be working fine. The thing I need though is for the copy button to copy everything from A to I. Right now I am only copying A to D.
Thanks for your help in advance.
Jim,
Try this
Sub CopyRows() Dim LRow As Long, ChkBx As CheckBox, WS2 As Worksheet Set WS2 = Worksheets("Sheet2") LRow = WS2.Range("A" & Rows.Count).End(xlUp).Row For Each ChkBx In ActiveSheet.CheckBoxes If ChkBx.Value = 1 Then LRow = LRow + 1 WS2.Cells(LRow, "A").Resize(, 9) = Range("A" & _ ChkBx.TopLeftCell.Row).Resize(, 9).Value End If Next End SubOscar,
I am using your code and altered it with multiple Check boxes(multiple columns of Check boxes).Every thing working fine.
As I am having for example 3 checkboxes in a single row, it copies 3 times a single row , I want separate destinations for copied data for every check box.( as it starts pasting from A column, I want it to start from A, and for 2nd check box it should start with any other column that I want and so on..)
Can you plz help
Hi Spider,
I think i am trying to do the same as you have mentioned in your previous post. I have multiple rows of data with three columns with check boxes. Each check box ticked should send the row to a different sheet. Did you manage to get a solution from anywhere?
Hi bit late to the show but I am using this code however would someone be able to assist on how I would be able to alter the code to be able to choose where the cells I paste the information to on sheet2 For example I want to paste the information starting at cell C12 i have managed to get it to paste starting at column C but cant get it to paste to C12 it still starts the pasting at C2.
Any help,
Thanks!
This is really great! Thank you! Can you tell me what code I would need to add in order to remove the original copied data? In other words, now that it has been copied from Sheet 1 to Sheet 2, how can I have it removed from Sheet 1 as part of the same process?
Thank you sir for your very useful and informative post. i want change the color row data after copying to indicate that was copied. please help
Faisal Mirza,
This code colors copied rows light blue:
Sub CopyRows() For Each chkbx In ActiveSheet.CheckBoxes If chkbx.Value = 1 Then For r = 1 To Rows.Count If Cells(r, 1).Top = chkbx.Top Then With Worksheets("Sheet2") LRow = .Range("A" & Rows.Count).End(xlUp).Row + 1 .Range("A" & LRow & ":D" & LRow) = _ Worksheets("Sheet1").Range("A" & r & ":D" & r).Value Worksheets("Sheet1").Range("A" & r & ":D" & r).Interior.ColorIndex = 37 End With Exit For End If Next r End If Next End SubHi Sir,
First of all I'd like to thank you for your code which gave me hope that my requirement is possible in excel.
I modified it as per my requirement and I must say its working 70%.
My requirement is by pressing with left mouse button on a checkbox in a particular row should select that row.So I modified your code as below.
Sub SelectRows() Dim cell, LRow As Single Dim chkbx As CheckBox Application.ScreenUpdating = False LRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row For Each chkbx In ActiveSheet.CheckBoxes If chkbx.Value = 1 Then For r = 1 To Rows.Count If Cells(r, 1).Top = chkbx.Top Then ActiveSheet.Range("A" & r & ":D" & r).Select 'With Worksheets("Sheet2") 'LRow = .Range("A" & Rows.Count).End(xlUp).Row + 1 '.Range("A" & LRow & ":D" & LRow) = _ 'ActiveSheet.Range("A" & r & ":D" & r).Value 'End With Exit For End If Next r End If Next End Subnow if I press with left mouse button on the checkbox it selects that row. I want to select multiple checkbox at a time and multiple row should be selected (as we manually select by pressing CTRL Key and Mouseover cells). Is it possible?
Please help me. I trying my best to find the solution but seems that's not enough.
Dear Mr. Oscar
Well done! Your post has reduced my all odds. need your further help. i want copy rows data from different sheets to single (MAIN) sheet.
JPG LINK BELOW MENTION
https://postimg.org/image/y8nq7wubp/
Sub CopyRows() For Each chkbx In ActiveSheet.CheckBoxes If chkbx.Value = 1 Then For r = 1 To Rows.Count If Cells(r, 1).Top = chkbx.Top Then With Worksheets("Main") LRow = .Range("C" & Rows.Count).End(xlUp).Row + 1 .Range("C" & LRow & ":J" & LRow) = _ Worksheets("1st-Party").Range("C" & r & ":J" & r).Value Worksheets("2nd-Party").Range("C" & r & ":J" & r).Interior.ColorIndex = 37 End With Exit For End If Next r End If Next End SubFaisal Mirza
Sub CopyRows() sht = Array("1st-Party", "2nd-Party", "3rd-Party") For i=LBound(sht) to Ubound(sht) For Each chkbx In Worksheets(sht(i)).CheckBoxes If chkbx.Value = 1 Then For r = 1 To Rows.Count If Cells(r, 1).Top = chkbx.Top Then With Worksheets("Main") LRow = .Range("C" & Rows.Count).End(xlUp).Row + 1 .Range("C" & LRow & ":J" & LRow) = _ Worksheets(sht(i)).Range("C" & r & ":J" & r).Value Worksheets(sht(i)).Range("C" & r & ":J" & r).Interior.ColorIndex = 37 End With Exit For End If Next r End If Next Next i End SubDear Oscar,
how would you code it if you wanted to generate a CSV and not a new sheet.
regards
HI Oscar,
I have tried to copy data from selected checkbox with the help of code which you mention above but it didnt work. Can you guid me what to do to get only selected check box data in new work sheet.
Thank you
kjp
Make sure you change Sheet1 and Sheet2 so it matches your worksheet names.
Oscar
Need your help
I need to start past in c12 in sheet2
Hi Oscar. Congrats for ur blog. Very helpful. I'm trying to use the code above to my project but I need some modifications. I want to use the checkboxes to select rows in 3 different sheets and then paste the selections to another workbook in 3 dif. sheets. Could that be possible? Thanks in advance.
Hello Oscar!
This is Sheela from Germany.
Could you please tell me what should I add to move (Cut) the selected rows from Sheeet 1 and Sheet 2.
I mean the data from Original Destination (Sheet 1)should be erased.
Thanks in Advance.
how to copy images also and paste
suppose I have two columns A and B. How to make checkbox be added automatically to column A once I type anything in column B?
Hi Oscar, I was looking something similar, instated copying into an sheet I like to copy the row in to a email body is this possible?
Hello,
I've learned quite a bit since reviewing your blog and want to thank you for such great tutorials.
I've run into something I can't quite figure out. If possible, could you assist? Here's what I want to do:
Sheet1: Contains a menu for the user.
Sheet2: Contains an extract of data that contains multiple business unit names in column A.
I want to populate a checkbox list of all the business unit names in Sheet1 with checkboxes to then allow the user to check only the business units they would like to include in the output on Sheet3.
I have code developed that assigns a random number for each row of data on Sheet3, which is then sorted to only the top 25 random numbers (a sample selection). That part of my code works fine, but am trying to learn how to take a list of business units from Column A of Sheet2 and then populate a checkbox list in Sheet1 for the user to then only select the business units they want.
I hope this makes sense, and I appreciate any help you can offer on how to go about doing this.
Kindest Regards,
Jeff
Hello Oscar,
Very usefull code.
When applyng a filter on the source data, the copied lines are different from the selected ones.
Any Idea to fix this ?
By advance,
Sire
This works great!! Is there a way to do this with a Button instead? I can not locate that option anywhere online.
ThankYou for your time.
hi Oscar,
I have managed to change the paste column from A to D but i cant seem to find a way to have the pasting start at cell D11 rather than D2.