Copy/Paste Adjacent Cell

[ah2024_2024]

So you guys solved my compile error but I'm back again....I actually revamped the prior code that I sent because I have learned a little bit in the last few days (thanks to you all). But I've run into a stall, and I've spent hours trying to figure it out on my own and can't. I have 2 sheets. My source sheet (deals) and my target sheet (ftw). On my source sheet if column F = fort worth AND column O = Active; then copy the employee name in column D and paste only that name into my target sheet (ftw) but need it to start in A9. I also need it only copy NEW data as it updated and not replace.

Here is what I'm using currently and the dang thing is pasting into J1 and pasting ALL the names and not just those that have the correct "fort worth" AND "active"

Sub TAKE_98765()
Dim rcnt As Long
Dim target As Worksheet

Set Source = ActiveWorkbook.Worksheets("deals")
Set target = ActiveWorkbook.Worksheets("ftw")


For Each c In Source.Range("f1:f20")
If c = "FORT WORTH" And c.Offset(0, 9) = "Active" Then
Range("f2:f20" & i).Offset(0, -2).Copy
target.Range("A" & Rows.Count).End(xlUp).Offset(0, 9).PasteSpecial xlPasteAll

End If
Next c

Application.CutCopyMode = False

End Sub


I have tried changing this line target.Range("A" & Rows.Count).End(xlUp).Offset(0, 9).PasteSpecial xlPasteAll to be target.Range("A" & Rows.Count).End(xlUp).Offset(9, 0).PasteSpecial xlPasteAll to no avail.

Thanks in advance folks!

 

[ah2024_2024]

So you guys solved my compile error but I'm back again....I actually revamped the prior code that I sent because I have learned a little bit in the last few days (thanks to you all). But I've run into a stall, and I've spent hours trying to figure it out on my own and can't. I have 2 sheets. My source sheet (deals) and my target sheet (ftw). On my source sheet if column F = fort worth AND column O = Active; then copy the employee name in column D and paste only that name into my target sheet (ftw) but need it to start in A9. I also need it only copy NEW data as it updated and not replace.

Here is what I'm using currently and the dang thing is pasting into J1 and pasting ALL the names and not just those that have the correct "fort worth" AND "active"

Sub TAKE_98765()
Dim rcnt As Long
Dim target As Worksheet

Set Source = ActiveWorkbook.Worksheets("deals")
Set target = ActiveWorkbook.Worksheets("ftw")


For Each c In Source.Range("f1:f20")
If c = "FORT WORTH" And c.Offset(0, 9) = "Active" Then
Range("f2:f20" & i).Offset(0, -2).Copy
target.Range("A" & Rows.Count).End(xlUp).Offset(0, 9).PasteSpecial xlPasteAll

End If
Next c

Application.CutCopyMode = False

End Sub


I have tried changing this line target.Range("A" & Rows.Count).End(xlUp).Offset(0, 9).PasteSpecial xlPasteAll to be target.Range("A" & Rows.Count).End(xlUp).Offset(9, 0).PasteSpecial xlPasteAll to no avail.

Thanks in advance folks!

Let me say that when I tried to change target.Range("A" & Rows.Count).End(xlUp).Offset(0, 9).PasteSpecial xlPasteAll to be target.Range("A" & Rows.Count).End(xlUp).Offset(8, 0).PasteSpecial xlPasteAll it appeared to want to put that data in the correct place but it was all blank.

 

[mumps]

Try:

Sub TAKE_98765()
    Application.ScreenUpdating = False
    Dim target As Worksheet, Source As Worksheet
    Set Source = Sheets("deals")
    Set target = Sheets("ftw")
    For Each c In Source.Range("F1:F20")
        If c = "FORT WORTH" And c.Offset(0, 9) = "Active" Then
            c.Offset(0, -2).Copy target.Cells(target.Rows.Count, "A").End(xlUp).Offset(1)
        End If
    Next c
    Application.ScreenUpdating = True
End Sub

 

[mumps]

Try this version instead:

Sub TAKE_98765()
    Application.ScreenUpdating = False
    Dim target As Worksheet, Source As Worksheet
    Set Source = Sheets("deals")
    Set target = Sheets("ftw")
    For Each c In Source.Range("F1:F20")
        If c = "FORT WORTH" And c.Offset(0, 9) = "Active" Then
            If Range("A9") = "" Then
                c.Offset(0, -2).Copy Range("A9")
            Else
                c.Offset(0, -2).Copy target.Cells(target.Rows.Count, "A").End(xlUp).Offset(1)
            End If
        End If
    Next c
    Application.ScreenUpdating = True
End Sub

 

[ah2024_2024]

Try this version instead:

Sub TAKE_98765()
    Application.ScreenUpdating = False
    Dim target As Worksheet, Source As Worksheet
    Set Source = Sheets("deals")
    Set target = Sheets("ftw")
    For Each c In Source.Range("F1:F20")
        If c = "FORT WORTH" And c.Offset(0, 9) = "Active" Then
            If Range("A9") = "" Then
                c.Offset(0, -2).Copy Range("A9")
            Else
                c.Offset(0, -2).Copy target.Cells(target.Rows.Count, "A").End(xlUp).Offset(1)
            End If
        End If
    Next c
    Application.ScreenUpdating = True
End Sub

This worked like a charm!!! Might I inquire about adding a 3rd data point and it could change my if statement. I really need all employees that are in a given office, active OR term'd within the current month. I am assuming I can change c.Offset(0, 9) = "Active" to be c.Offset(0, 7) = "" (meaning the blank would mean they don't have a termination date (column m) and obviously are ACTIVE but how do I add FORT WORTH and "" (no term date) or Fort Worth and term date of CURRENT month?

 

[ah2024_2024]

This worked like a charm!!! Might I inquire about adding a 3rd data point and it could change my if statement. I really need all employees that are in a given office, active OR term'd within the current month. I am assuming I can change c.Offset(0, 9) = "Active" to be c.Offset(0, 7) = "" (meaning the blank would mean they don't have a termination date (column m) and obviously are ACTIVE but how do I add FORT WORTH and "" (no term date) or Fort Worth and term date of CURRENT month?

Crap, one more issue. This will be run each day and I'd like it to only paste over NEW ones, and currently each time I run it it just gives me the same names starting in the first available cell of A (like first run ended in A20) and 2nd run starts the names all over in A21

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your two sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).