Using Checkboxes to Copy Rows From One Sheet to Another in the Same Order That They Appear

ShammoAero

New Member
Joined
Aug 31, 2017
Messages
3
Hi everyone,

I'm fairly new to VBA so this doesn't seem obvious to me at the moment.

I've managed to use Checkboxes to copy the rows that have a check mark from sheet1 to sheet2.
The problem is that if I insert a new row in sheet1 somewhere in the middle of the worksheet and I insert a checkbox into that row which I then check mark, the row will be inserted as the LAST ROW of sheet2. I need the row to be inserted in the same order as they appear in sheet1.

Here is the code that I am currently using:

Sub Addcheckboxes()
Dim cell, LRow As Single
Dim chkbx As CheckBox
Dim CLeft, CTop, CHeight, CWidth As Double

Application.ScreenUpdating = False
LRow = ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row

For cell = 3 To LRow
If Cells(cell, "A").Value <> "" Then
CLeft = Cells(cell, "E").Left
CTop = Cells(cell, "E").Top
CHeight = Cells(cell, "E").Height
CWidth = Cells(cell, "E").Width
ActiveSheet.CheckBoxes.Add(CLeft, CTop, CWidth, CHeight).Select
With Selection
.Caption = ""
.Value = xlOff
.Display3DShading = False
End With
End If
Next cell

Application.ScreenUpdating = True

End Sub


Sub RemoveCheckboxes()
Dim chkbx As CheckBox

For Each chkbx In ActiveSheet.CheckBoxes
chkbx.Delete
Next

End Sub


Sub CopyRows()

Sheets("sheet2").Range("A2:TX100000").ClearContents 'This ensures that entries aren't duplicated

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("C.I.D Summary")

LRow = .Range("A" & Rows.Count).End(xlUp).Row + 1
.Range("A" & LRow & ":S" & LRow) = _
Worksheets("sheet1").Range("A" & r & ":S" & r).Value

End With

Exit For
End If

Next r
End If
Next

End Sub


This works well, until I need to add another row somewhere in the middle of sheet 1, which has a check mark. Then the row will be added at the end of sheet 2, instead of the order in which it appears in sheet1. The problem lies with the fact that Checkboxes are given an I.D which is in order of when they were placed in the sheet. If I remove all the checkboxes using the RemoveCheckboxes subroutine, then add them again using AddCheckboxes, then this will "solve" this problem but all the checkboxes will be cleared, and I have about 1000 checkboxes that will need to be checked again manually.

Does anyone have a solution to this problem?

Thank you very much.
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,263
Office Version
  1. 2013
Platform
  1. Windows
Why did you decide to do things this way?

Adding all these checkboxes and then selecting them and then deleting them seems like a lot of work.
Why not do something like putting a "X' in each cell in column 'A" when you want that row deleted.

Or double click on any cell in column "A" if you want the row deleted.
 

ShammoAero

New Member
Joined
Aug 31, 2017
Messages
3
It is the way that my company decided to sort their electronic archive of documents. Also, the line in the CopyRows subroutine is that says With Worksheets("C.I.D Summary"), is meant to say With Worksheets("sheet2") as I changed the names of the sheets to be less confusing for the thread reader.

I also don't think you understand my problem, or perhaps I don't understand what you're suggesting.

I have a sheet of information pertaining to various documents, about 1000 documents. Some of these documents are the "latest" versions and these have a check mark next to them. These check marked rows are then sent to sheet2, in order to summarise the latest documents that are marked in sheet1. However, new rows may be added to the sheet1 and could be placed randomly in the sheet. When check marked, these random new rows will be placed as the LAST row in sheet2, instead of order it appears in the sheet1.

If anyone knows of a better way to do this then I am open to suggestions, however I am not experienced at all with VBA.
 

ShammoAero

New Member
Joined
Aug 31, 2017
Messages
3
Using checkboxes to copy rows from one sheet to another in the same relative order

Hi everyone,

I'm fairly new to VBA so this doesn't seem obvious to me at the moment.

My company is using a workbook where Sheet1 contains details of about 1000 documents, one on each row. Some of these documents are the "latest" versions and these have a check mark next to them, using checkboxes. We are trying to use Checkboxes to copy the rows that have a check mark from Sheet1 to Sheet2, where Sheet2 is meant to summarise these "latest" versions of the documents from Sheet1.
The problem is that if we insert a new row in Sheet1 somewhere in the middle of the worksheet and I insert a checkbox into that row which I then check mark, the row will be inserted as the LAST ROW of Sheet2. I need the row to be inserted in the same relative order as they appear in Sheet1.

Here is the code that I am currently using:

Code:
[COLOR=#333333]Sub Addcheckboxes()[/COLOR]
[COLOR=#333333]Dim cell, LRow As Single[/COLOR]
[COLOR=#333333]Dim chkbx As CheckBox[/COLOR]
[COLOR=#333333]Dim CLeft, CTop, CHeight, CWidth As Double[/COLOR]

[COLOR=#333333]Application.ScreenUpdating = False[/COLOR]
[COLOR=#333333]LRow = ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row[/COLOR]

[COLOR=#333333]For cell = 3 To LRow[/COLOR]
[COLOR=#333333]If Cells(cell, "A").Value <> "" Then[/COLOR]
[COLOR=#333333]CLeft = Cells(cell, "E").Left[/COLOR]
[COLOR=#333333]CTop = Cells(cell, "E").Top[/COLOR]
[COLOR=#333333]CHeight = Cells(cell, "E").Height[/COLOR]
[COLOR=#333333]CWidth = Cells(cell, "E").Width[/COLOR]
[COLOR=#333333]ActiveSheet.CheckBoxes.Add(CLeft, CTop, CWidth, CHeight).Select[/COLOR]
[COLOR=#333333]With Selection[/COLOR]
[COLOR=#333333].Caption = ""[/COLOR]
[COLOR=#333333].Value = xlOff[/COLOR]
[COLOR=#333333].Display3DShading = False[/COLOR]
[COLOR=#333333]End With[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]Next cell[/COLOR]

[COLOR=#333333]Application.ScreenUpdating = True[/COLOR]

[COLOR=#333333]End Sub[/COLOR]


[COLOR=#333333]Sub RemoveCheckboxes()[/COLOR]
[COLOR=#333333]Dim chkbx As CheckBox[/COLOR]

[COLOR=#333333]For Each chkbx In ActiveSheet.CheckBoxes[/COLOR]
[COLOR=#333333]chkbx.Delete[/COLOR]
[COLOR=#333333]Next[/COLOR]

[COLOR=#333333]End Sub[/COLOR]


[COLOR=#333333]Sub CopyRows()[/COLOR]

[COLOR=#333333]Sheets("sheet2").Range("A2:TX100000").ClearContents 'This ensures that entries aren't duplicated[/COLOR]

[COLOR=#333333]For Each chkbx In ActiveSheet.CheckBoxes[/COLOR]
[COLOR=#333333]If chkbx.Value = 1 Then[/COLOR]
[COLOR=#333333]For r = 1 To Rows.Count[/COLOR]
[COLOR=#333333]If Cells(r, 1).Top = chkbx.Top Then[/COLOR]
[COLOR=#333333]With Worksheets("sheet2")[/COLOR]

[COLOR=#333333]LRow = .Range("A" & Rows.Count).End(xlUp).Row + 1[/COLOR]
[COLOR=#333333].Range("A" & LRow & ":S" & LRow) = _[/COLOR]
[COLOR=#333333]Worksheets("sheet1").Range("A" & r & ":S" & r).Value[/COLOR]

[COLOR=#333333]End With[/COLOR]

[COLOR=#333333]Exit For[/COLOR]
[COLOR=#333333]End If[/COLOR]

[COLOR=#333333]Next r[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]Next[/COLOR]

[COLOR=#333333]End Sub[/COLOR]

This works well, until I need to add another row somewhere in the middle of sheet 1, which has a check mark. Then the row will be added at the end of sheet 2, instead of the order in which it appears in sheet1. The problem lies with the fact that Checkboxes are given an unique number which is in order of when they were placed in the sheet. If I remove all the checkboxes using the RemoveCheckboxes subroutine, then add them again using AddCheckboxes, then this will "solve" this problem but all the checkboxes will be cleared, and I have hundreds of checkboxes that will need to be checked again manually.

Does anyone have a solution to this problem?

Thank you very much.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,301
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Re: Using checkboxes to copy rows from one sheet to another in the same relative order

If you link the checkboxes to a cell, you could then autofilter on that column & then copy the visible cells.
 

tonyyy

Well-known Member
Joined
Jun 24, 2015
Messages
1,647
Re: Using checkboxes to copy rows from one sheet to another in the same relative order

ShammoAero,

Welcome to the Board.

You might consider swapping the loops, making "For r = 1 To Rows.Count" the primary loop and "For Each chkbx..." the nexted loop...

Code:
Sub CopyRows2()
Application.ScreenUpdating = False
Sheets("sheet2").Range("A2:TX100000").ClearContents 'This ensures that entries aren't duplicated
For r = 1 To Rows.Count
    For Each chkbx In ActiveSheet.CheckBoxes
        If Cells(r, 1).Top = chkbx.Top And chkbx.Value = 1 Then
            With Worksheets("sheet2")
                LRow = .Range("A" & Rows.Count).End(xlUp).Row + 1
                .Range("A" & LRow & ":S" & LRow).Value = _
                    Worksheets("sheet1").Range("A" & r & ":S" & r).Value
            End With
            Exit For
        End If
    Next chkbx
Next r
Application.ScreenUpdating = True
End Sub

The order that rows are copied to sheet2 is then determined by the row number rather than the checkbox index.

If speed/performance is an issue, then Fluff's suggested approach would be a viable solution.

Cheers,

tonyyy
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
@ ShammoAero
I have merged your two threads since both had replies.
In future please do not post the same question multiple times. See #12 of the Forum Rules and points 6 & 7 of the Forum Use Guidelines.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,311
Messages
5,600,886
Members
414,414
Latest member
neil_c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top