VBA Code for copy and paste entire row to another sheet

[caos88]

Good morning all,

I have data on 1 sheet that i want to copy and paste (clearing the data ) to another sheet. I wish to connect this macro to a "form control button" so when the operator is filling up the form, he clicks on the button for storage and the data will be moved. So far i have the current code for cut and paste which works well. Any suggestions on how to modify it? in the case below, the code works when triggered from the word "Closed" and i don't really need it now. Thank you.

Sub Store()
    Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet


    ' Change worksheet designations as needed
    Set Source = ActiveWorkbook.Worksheets("Sheet1")
    Set Target = ActiveWorkbook.Worksheets("Sheet2")


    j = Target.Range("A" & Rows.Count).End(xlUp).Row + 1     ' Start copying 1 down from the last row on sheet
    For Each c In Source.Range("A1:K1000")   ' Do 1000 rows
        If c = "Closed" Then
           Source.Rows(c.Row).Copy Target.Rows(j)
           Source.Rows(c.Row).Cut Target.Rows(j)
           j = j + 1
      
        End If
    Next c
End Sub

 

[mole999]

for a set of rows
Sheet2.Range("A1:C10000").Copy Destination:=Sheet3.Range("A1")

 

[caos88]

for a set of rows
Sheet2.Range("A1:C10000").Copy Destination:=Sheet3.Range("A1")

Thank you @mole999. Since i am not the best in excel, how to compose the code ?

 

[caos88]

Thank you @mole999. Since i am not the best in excel, how to compose the code ?

Doesn't work anyway

 

[jolivanes]

Re: "Doesn't work anyway"
Very descriptive! Tells us a whole bunch of nothing.

Do you want to copy and paste from sheet2 to sheet3?

Sheet2.Range("A1:C10000").Copy Destination:=Sheet3.Range("A1")

If not, change the references, sheet names and range references as required.

 

[caos88]

Re: "Doesn't work anyway"
Very descriptive! Tells us a whole bunch of nothing.

Do you want to copy and paste from sheet2 to sheet3?

Sheet2.Range("A1:C10000").Copy Destination:=Sheet3.Range("A1")

If not, change the references, sheet names and range references as required.

Hello @jolivanes, i didn't want to upset anyone.
First of all, i am starting now to get a closed look at VBA, second, i understood the code but is not going to work for much more data from other sheets as it will overwrite it.
I have a "completed button" (form control button) on the end of each row from where i can assign this code and execute the following:
Example:
Sheet 1 -row 1 completed. Press the button and Copy and paste to "final sheet"
Sheet 1 -row 3 completed. Copy and paste to "final sheet"
Sheet 1 -row 4 completed. Copy and paste to "final sheet"
Sheet 2 -Sheet 1 -row 1 completed. Copy and paste to "final sheet"
Sheet 2 -row 3 completed. Copy and paste to "final sheet"
......and so on for other 5 sheets
At the same time, i want the copied data in sheets 1,2,3....and so on to be cleared in order to be used next time.

 

[jolivanes]

Re: "I have a "completed button" (form control button) on the end of each row"
I would stay away from that. You could essentially end up with hundreds of buttons.

If I understand you right, you want to move the row that you're in to the next free row in a Sheet named "final sheet".
So would it work for you if you move the row where you have a cell selected to that sheet? Just one Button on each of the five Sheets?

 

[caos88]

Re: "I have a "completed button" (form control button) on the end of each row"
I would stay away from that. You could essentially end up with hundreds of buttons.

If I understand you right, you want to move the row that you're in to the next free row in a Sheet named "final sheet".
So would it work for you if you move the row where you have a cell selected to that sheet? Just one Button on each of the five Sheets?

Yes correct, after filling up the info, i want to move the row to the next avilable in the final sheet and, at the same time i want the copied sheet to be clear.
Pratically copy, paste to final sheet and clear data from sheet 1.
Yes, i was thinking about only 1 button for each sheet, it's a great idea.

 

[jolivanes]

Re: "i want the copied sheet to be clear."
I take the liberty to assume you mean that particular row! If that is not the case, let us know.

It will move the used cells in the row where you have a cell selected anywhere in that row to the "final sheet", first empty cell in Column A.
Does not matter which sheet you're in. Works on all.

Sub Move_It()
With ActiveSheet
    With .Cells(ActiveCell.Row, 1).Resize(, .UsedRange.Columns.Count)
        .Copy Sheets("final sheet").Cells(Rows.Count, 1).End(xlUp).Offset(1)
        .Delete Shift:=xlUp
    End With
End With
End Sub

BTW, do not quote unless it is absolutely needed. Refer to a post # instead if you think it is needed.

 

[caos88]

@jolivanes Thank you, yes i ment the row before....anyway it works great. If possible i have one question:
i see that the row will be moved only if i select any cell of the row. Instead, is it possible to do for all the rows filled with data ? that's why before i was thinking about a button for each row....
I am afraid that people using it will mess up if they don't realize which cell is activated...