Need to copy certain cells from one worksheet and paste into another worksheet

I am trying to find out if there is some kind of VBA/Macro code that will allow me to use a button to copy certain cells from one worksheet and paste them into another in one action. My issue is that I don't know how many cells will need to be copied each time. For example, I could need to copy 10 rows from WS1 (Worksheet 1) to WS2 (Worksheet 2), and then right after need to only copy 5 rows. Then shortly after that, I could need to copy 50. I don't know if I'm asking the impossible here or not, but I figured it's better to ask than assume no. I tried recording the macros, but I'm not really sure how that works either. >.< I appreciate any help that can be offered.

Thanks.

Each time you copy the rows, do you want them added to any rows previously copied? How do you decide which rows to copy each time? If you could post your file with a detailed explanation of what you would like to do, it would be easier to help.

I'm attaching what I currently have. As I use the table, it adds cells to the listed columns. I only want the cells that are used to be copied from Sheet1 to Sheet2.

To answer your question, I would want them added to Sheet2 as a separate page. So for example I would have the current information added to Sheet2 as Page 1. Then I would generate new numbers and add them to Sheet2 as Page 2. Or if it gets to the point where Sheet1 has 2 pages, they would get added after the last page on Sheet2.

I'm not sure how to decide which rows to copy each time. That's kind of what I was hoping to get help on here.

Let me know if this helps or if you need more information.

Can I assume that the purpose of the form is to gather the data that defines the range of cells you want to copy? For example: if you enter C in the column box and then 3 and 10, this means you want to copy the range C3:C10 to Sheet2. Then if you enter another range, you want that range added to what has already been copied to Sheet2 and so on. Is this correct?

No. I want to copy the list of cells. Basically all of the cells that have listed ranges will be copied to Sheet2. Then I will clear out the data and input new information. Then that will be copied to Sheet2. And then the process will repeat.

So basically the difference between my description and yours is that you don't want data added to existing data. You want only the last range of cells to appear in Sheet2. Would this be correct?

Sorry I'm a little confused by your response. I'm attaching an updated copy of the file. Sheet1 will have the list of columns that are going to be added. Then once they are added to Sheet2, I will clear the cells from Sheet1 and enter the next set of data. Then that will get added to Sheet2 as a separate page. Then I will repeat the process however many times as needed. Hope this helps

I had something similar to this and i used to check boxes in order to copy the ows of data, have a look at this and see if you can change it to help what you are doing. It involves three command buttons, one to add check boxes, one to remove them and one to copy the selected rows onto another sheet.

Private Sub CommandButton1_Click()
Dim cell, LRow As Single
Dim chkbx As CheckBox
Dim CLeft, CTop, CHeight, CWidth As Integer
Application.ScreenUpdating = False
LRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For cell = 2 To LRow
If Cells(cell, "A").Value <> "" Then
CLeft = Cells(cell, "B").Left
CTop = Cells(cell, "B").Top
CHeight = Cells(cell, "B").Height
CWidth = Cells(cell, "B").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

Private Sub CommandButton2_Click()
Dim chkbx As CheckBox
For Each chkbx In ActiveSheet.CheckBoxes
chkbx.Delete
Next
End Sub

Private Sub CommandButton3_Click()
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 & ":T" & LRow) = _
Worksheets("Sheet1").Range("A" & r & ":T" & r).Value
End With
Exit For
End If
Next r
End If
Next
End Sub

No I don't think that is what I am looking for. Thanks, though.

Hi ddouget1. Try the attached file.

Thanks a million! That's it!

My pleasure.