Copy If

[plwandatw]

Good afternoon,
I am trying to copy a cell based on another cells value and paste on another worksheet in column A at the next available cell.
Worksheet1 If F10="A" and if J10=0 then Copy C10 to Worksheet2 Column A at next available cell.

Thank you in advance.
Johnboy

 

[rlv01]

If you are talking about a VBA solution, then it would be something like this.

'I am trying to copy a cell based on another cells value and paste on another worksheet in column A at the next available cell.
'Worksheet1 If F10="A" and if J10=0 then Copy C10 to Worksheet2 Column A at next available cell.
Sub CopyCell()
    Dim WS1 As Worksheet, WS2 As Worksheet
    
    Set WS1 = Worksheets("MyWorkSheet1")
    Set WS2 = Worksheets("MyWorkSheet2")
    
    If WS1.Range("F10").Value = "A" And WS1.Range("J10").Value = 0 Then
        WS1.Range("C10").Copy WS2.Range("A" & WS2.Rows.Count).End(xlUp).Offset(1)
    End If
End Sub

 

[plwandatw]

Thank you for the quick response, this works great, Next question, is there a way to check each row on sheet1 until last row with data?

Johnboy

 

[rlv01]

There probably is, but you have not provided enough information about what that actually means for me to do anything with.

 

[plwandatw]

There probably is, but you have not provided enough information about what that actually means for me to do anything with.

instead of a static cell use dynamic cell...
For each row of data on worksheet1 check the values in Column F and K, and copy data in Column C to worksheet2... I tried to Loop, but just not enough VBA experience.

 

[rlv01]

'I am trying to copy a cell based on another cells value and paste on another worksheet in column A at the next available cell.
'Worksheet1 If Fn="A" and if Jn=0 then Copy Cn where n = row number to Worksheet2 Column A at next available cell.
Sub CopyCell()
    Dim WS1 As Worksheet, WS2 As Worksheet
    Dim I As Long

    Set WS1 = Worksheets("MyWorkSheet1")
    Set WS2 = Worksheets("MyWorkSheet2")

    Set WS1 = ActiveSheet
    Set WS2 = ActiveSheet

    For I = 1 To 100                                  'rows
        If WS1.Range("F" & I).Value = "A" And WS1.Range("J" & I).Value = 0 Then
            WS1.Range("C" & I).Copy WS2.Range("A" & WS2.Rows.Count).End(xlUp).Offset(1)
        End If
    Next I
End Sub

 

[plwandatw]

'I am trying to copy a cell based on another cells value and paste on another worksheet in column A at the next available cell.
'Worksheet1 If Fn="A" and if Jn=0 then Copy Cn where n = row number to Worksheet2 Column A at next available cell.
Sub CopyCell()
    Dim WS1 As Worksheet, WS2 As Worksheet
    Dim I As Long

    Set WS1 = Worksheets("MyWorkSheet1")
    Set WS2 = Worksheets("MyWorkSheet2")

    Set WS1 = ActiveSheet
    Set WS2 = ActiveSheet

    For I = 1 To 100                                  'rows
        If WS1.Range("F" & I).Value = "A" And WS1.Range("J" & I).Value = 0 Then
            WS1.Range("C" & I).Copy WS2.Range("A" & WS2.Rows.Count).End(xlUp).Offset(1)
        End If
    Next I
End Sub

The Copy / Paste function copies WS1 column C to same WS1 column A, WS2 no populating from WS1

 

[plwandatw]

Not sure why the second "Set WS1 & WS2" but I removed, and the macro works Great... Thank you for your help.

Sub CopyCell()
Dim WS1 As Worksheet, WS2 As Worksheet
Dim I As Long

Set WS1 = ActiveSheet
Set WS2 = Worksheets("Income")

'Set WS1 = ActiveSheet
'Set WS2 = ActiveSheet

For I = 1 To 100 'rows
If WS1.Range("F" & I).Value = "Deposit" And WS1.Range("J" & I).Value = 0 Then
WS1.Range("C" & I).Copy WS2.Range("A" & WS2.Rows.Count).End(xlUp).Offset(1)
End If
Next I
End Sub

 

[rlv01]

Not sure why the second "Set WS1 & WS2" but I removed, and the macro works Great... Thank you for your help.

Well, that is what we in the programming community like to refer to as a "mistake" . The second set was something I put in there to make testing easier for me then forgot to take out again. Cheers.

 

[Peter_SSs]

Not sure why the second "Set WS1 & WS2" but I removed, and the macro works Great... Thank you for your help.

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.