Cut selected data frm sheet1 paste to sheet2??

[bernardng]

In Sheet1, I have data in A2:F2, follow by G2 have checkbox1 (using Control ToolBox

VBA),similiar data in A3:F3 and G3 have another checkbox2. A4:F4 and G4 have another

checkbox3

Example,

If checkbox1 box is untick, the Range("A2:F2") is Select and Cut to A14:F14 (First Row)
If checkbox2 box is tick, the Range("A3:F3") is select and Cut and paste to Sheet2 (First

Row, Example: A2:F2)
If checkbox3 is untick, the Range("A4:F4") is Select and Cut to next row of the A14:F14.

I need the coding to check if checkbox(i) is untick, the selected data will cut and paste

start from A14:F14, no matter which checkbox(i) is tick first, the A14 should be the first

row to paste the value. Program also need to check if first row of A14:F14 contain data,then

paste another value to next rows. (Below is my sample coding)


If CheckBox1.Value = False Then
'checkbox 1 is untick move to A14 (e.g.)
If myCount1 <> 0 Then
Range("$A$2:$E$2").Select
Range("$A$2:$E$2").Cut
NextRow = Range("A65536").End(xlUp).Row + 1
Range("A" & NextRow).Select
ActiveSheet.Paste
End If
Else

'checkbox1 is tick move to sheet2, range start from (A2)
'Question: If i harcoding the paste location, It will replace the value that i paste. (see below)
'Example: If checkbox2=true then, select and Cut checkbox2 Range (E.g. "A3:F3"), then activate sheet2, select Range("A2") and 'paste.

'I need to write a coding the second paste value should be the next row of A2 and following by other.


End If

 

[SkipVought]

Hi,

Next row on Sheet2...

with Worksheets("Sheet2").[A2].CurrentRegion
  lNextRow = .row + .rows.count
end with

then paste referencing that row, and column 1

Worksheets("Sheet2").Paste Worksheets("Sheet2").cells(lNextRow, 1)

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[bernardng]

Yes I love it, Is Work! You are really Expert In VBA.

Below is my coding (but still have some doubt, see below)

If CheckBox1.Value = False Then
If myCount1 <> 0 Then
Range("$A$13:$E$13").Select
Range("$A$13:$E$13").Cut
NextRow = Range("A65536").End(xlUp).Row + 1
Range("A" & NextRow).Select
ActiveSheet.Paste
End If
Else
If myCount1 <> 0 Then
Range("$A$13:$E$13").Select
Range("$A$13:$E$13").Cut
With Worksheets("completed task").[A1].CurrentRegion
lNextRow = .Row + .Rows.Count
End With
Worksheets("completed task").Paste Worksheets("completed task").Cells(lNextRow, 1)

'After paste, Insert some value in start F2 and G2 in (sheet2=completed task)
'Example F2 insert "completed" word, G2 insert date of completed E.g. 5-May-2006


End If
End If

 

[SkipVought]

Is something not working? I can't understand your 'comment.

Please be clear, concise and complete.

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[bernardng]

If CheckBox1.Value = False Then
If myCount1 <> 0 Then
Range("$A$13:$E$13").Select
Range("$A$13:$E$13").Cut
NextRow = Range("A65536").End(xlUp).Row + 1
Range("A" & NextRow).Select
ActiveSheet.Paste
End If
Else
If myCount1 <> 0 Then
Range("$A$13:$E$13").Select
Range("$A$13:$E$13").Cut
With Worksheets("completed task").[A1].CurrentRegion
lNextRow = .Row + .Rows.Count
End With
Worksheets("completed task").Paste Worksheets("completed task").Cells(lNextRow, 1)
End If

The code is running fine. Sorry for that, here is my explaination, I further need the coding to be done, after paste the value to sheet2, I want to automatically insert 2 value into F2 and G2 column.
The data for F2 to insert is "completed", G2 is insert today date.

 

[firefytr]

How about something like this ...

Sub dflflkdfk()
    If CheckBox1.Value = False Then
        If myCount1 <> 0 Then
            Range("A13:E13").Cut Range("A" & Cells(Rows.Count, 1).End(xlUp).Row + 1)
            Range("F2").Value = "Completed"
            Range("G2").Value = Date
        End If
    Else
        If myCount1 <> 0 Then
            Range("A13:E13").Cut
            With Worksheets("completed task").Range("A1").CurrentRegion
                lNextRow = .Row + .Rows.Count
            End With
            Worksheets("completed task").Paste Worksheets("completed task").Cells(lNextRow, 1)
        End If
    End If
End Sub

-----------
Regards,
Zack Barresse

 

[bernardng]

Dim myCount1 As Integer
Set myRange1 = Range("$A$13:$E$13")
myCount1 = Application.Count(myRange1)

If CheckBox1.Value = False Then
If myCount1 <> 0 Then
Range("$A$13:$E$13").Select
Range("$A$13:$E$13").Cut
NextRow = Range("A65536").End(xlUp).Row + 1
Range("A" & NextRow).Select
ActiveSheet.Paste
End If
Else
If myCount1 <> 0 Then
Range("$A$13:$E$13").Select
Range("$A$13:$E$13").Cut
With Worksheets("completed task").[A1].CurrentRegion
lNextRow = .Row + .Rows.Count
End With
Worksheets("completed task").Paste Worksheets("completed task").Cells(lNextRow, 1)
Worksheets("completed task").Range("F2").Value = "Completed"
Worksheets("completed task").Range("G2").Value = Date
End If
End If


I need the "completed" and "today date" paste in to sheet2 on each of the next row (Not Specified Range), Just like what u shown on this command (Worksheets("completed task").Paste Worksheets("completed task").Cells(lNextRow, 1))

How we use the .value = date in above coding ?

 

[firefytr]

Does what you posted not work for you then? Not sure if I'm following you right or not ...

    Dim lNextRow As Long
    If CheckBox1.Value = False Then
        If myCount1 <> 0 Then
            Range("A13:E13").Cut Range("A" & Cells(Rows.Count, 1).End(xlUp).Row + 1)
            Range("F2").Value = "Completed"
            Range("G2").Value = Date
        End If
    Else
        If myCount1 <> 0 Then
            Range("A13:E13").Cut
            With Worksheets("completed task")
                lNextRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
                .Cells(lNextRow, 1).PasteSpecial xlPasteAll
                .Cells(lNextRow, 6).Value = "Completed"
                .Cells(lNextRow, 7).Value = Format(Date, "dd-mmm-yy")
            End With
        End If
    End If

That what you're looking for?

-----------
Regards,
Zack Barresse

 

[bernardng]

Below code shown me the error message.
".Cells(lNextRow, 1).PasteSpecial xlPasteAll"

Run-time error '1004'
PasteSpecial method of Range class failed
-------------------------------------------------------
However I have replace with SkipVought method (let me know if you have the best one), see below coding for my latest ammend code.


On other hand you have shortest my code, you are another expert in VBA. "Range("A13:E13").Cut Range("A" & Cells(Rows.Count, 1).End(xlUp).Row + 1)" -> this coding will replace my 5 line coding.


Latest Coding
-------------

Dim myCount1 As Integer
Dim lNexRow As Long
Set myRange1 = Range("$A$13:$E$13")
myCount1 = Application.Count(myRange1)

If CheckBox1.Value = False Then
If myCount1 <> 0 Then
Range("A13:E13").Cut Range("A" & Cells(Rows.Count, 1).End(xlUp).Row + 1)
End If
Else
If myCount1 <> 0 Then
Range("A13:E13").Cut
With Worksheets("completed task")
lNextRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
Worksheets("completed task").Paste Worksheets("completed task").Cells(lNextRow, 1)
.Cells(lNextRow, 6).Value = "Completed"
.Cells(lNextRow, 7).Value = Format(Date, "dd-mmm-yy")
End With
End If
End If


Yes this is what I WANT...Thanks and I Luv It...