Copying Selected Data into Excel Table

Microsoft Excel 2007 home and student
September 23, 2009 at 21:40:03
Specs: Windows Vista

I'm trying to input rows of data that will be selected using checkboxes into a table on a different sheet in my workbook. I'm managed to get the data to transfer into the table when the checkbox is selected, but it populates the entire table instead of one row.

I'm also looking for a line of code that removes the data from the table when the box is unchecked.

Here is the code on one of the checkboxes:
Private Sub O2Tank_Click()
Sheets("ECLSS").Range("G5:U5").Copy Destination:="TechSum"
End Sub

Thanks in advance!

See More: Copying Selected Data into Excel Table

Report •

September 24, 2009 at 04:46:09

Here is some code for a check box alongside a row of data that will
1. Copy cells in one row and paste them into the same cells, same row, on another worksheet
2. Delete the source cells

In this example when the check box is checked, the data is copied and
when the checkbox is cleared the data is erased.

Private Sub CheckBox1_Click()
If Me.CheckBox1.Value = True Then
    ActiveSheet.Range("G5:L5").Copy Destination:=Worksheets("DestinationSheet").Range("G5:L5")
    ActiveSheet.Range("G5:L5").Value = ""
End If
End Sub

The destination range in this example is the same range as the source, but on a different worksheet.

If you wanted, you could use a fixed range as the destination, so that only one set of data ever shows in the destination sheet

It looks as though you might have been trying that. Is "TechSum" a named range?
If you use a named range it needs to be the same size as the data you are copying, or a single cell.
If it is larger than the source, Excel will copy the source data to fill the range.

If you are building a table on the destination worksheet, you will need some more code to position successive rows.

Please explain or give some examples of how the source data is to be positioned in the destination sheet.


Report •

September 24, 2009 at 06:37:14
Hi Humar,

"TechSum" is the name I gave to a Table I created on the destination sheet. It is the same width as the data I want to copy to it.

What I'm trying to do is the following: I have lists of numerous technologies that I want to be able to select from on other sheets using the checkboxes and have only those selected items show up in the TechSum table without any gaps between the rows of data. That said, I don't think I want to specify that the data from a particular row has to be copied to row C,D, or E (I was hoping that all I had to do was tell it to go "fill up this table with these items").

Hopefully that clarifies what I'm trying to accomplish.

Thanks for the reply!

Report •

September 24, 2009 at 08:38:08

To add your data with each selected and copied row being placed below the last one,
I did the following:

Select the top left cell of the destination area and name it "DestOrigin"
Select all the cells in the first column of the destination area and name it "DestColumn"
(Note this is not the whole column)
In this example "DestOrigin" was $F$5 and "DestColumn" was $F$5:$F$13

I used a search of the cells in "DestColumn" to find the first blank cell, and used its Row number together with the row number from "DestOrigin" to calculate the row offset for the copy operation.

Here is the code for one checkbox:

Private Sub CheckBox1_Click()
Dim intNextRow As Integer
Dim rngCell As Range

With Worksheets("DestinationSheet")
	For Each rngCell In .Range("DestColumn")
	    If IsEmpty(rngCell) Then
	        intNextRow = rngCell.Row
	        Exit For
	    End If
	intNextRow = intNextRow - .Range("DestOrigin").Row
End With
Worksheets("SourceSheet").Range("G5:L5").Copy _
Destination:=Worksheets("DestinationSheet").Range("DestOrigin").Offset(intNextRow, 0)

The only difference for subsequent check boxes is the source address


Report •

Related Solutions

September 24, 2009 at 13:46:27
You are an Excel GOD!

Thank you so much!

Report •

September 24, 2009 at 14:31:21
Your welcome

Report •

Ask Question