Copy / paste problem.. any ideas?

[srosk]

I have a macro that performs the following function:

Range("L2").Formula = "=VLOOKUP(C2,'152083'!B:C,2,FALSE)"Range("L2", "K" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
Range("L:L").Copy
Range("L:L").PasteSpecial xlPasteValues
Application.CutCopyMode = False

The problem, row 1 has a header, and row 2 has actual data. Sometimes, the header ends up in the value for L2. I notice this happens especially when there are only two rows on the sheet.

Any idea why / how to combat this problem? It is impacting the results of my code. Thank you

 

[DanteAmor]

This line get last item from column 1:
Cells(Rows.Count, 1).End(xlUp).Row)

Change 1 by 2 , to take the last item from column B

 

[srosk]

This line get last item from column 1:
Cells(Rows.Count, 1).End(xlUp).Row)

Change 1 by 2 , to take the last item from column B

Thanks for looking at this. For whatever reason, it doesn't work after making the change. The actual formula works perfectly fine as is, with Rows.Count, 1. The problem occurs when the copy, then paste function take place.

Any idea why that would happen?

 

[DanteAmor]

try this

    Range("L2").Formula = "=VLOOKUP(C2,'152083'!B:C,2,FALSE)"
    u = Range("C" & Rows.Count).End(xlUp).Row
    If u = 1 Then u = 3
    Range("L2:K" & u).FillDown
    Range("L:L").Copy
    Range("L:L").PasteSpecial xlPasteValues
    Application.CutCopyMode = False

 

[srosk]

try this

    Range("L2").Formula = "=VLOOKUP(C2,'152083'!B:C,2,FALSE)"
    u = Range("C" & Rows.Count).End(xlUp).Row
    If u = 1 Then u = 3
    Range("L2:K" & u).FillDown
    Range("L:L").Copy
    Range("L:L").PasteSpecial xlPasteValues
    Application.CutCopyMode = False

This is SO weird. Same outcome.

Lr = WS1.Cells(Rows.Count, "A").End(xlUp).Row
Range("A2:B" & Lr).Copy
Range("A2:B" & Lr).PasteSpecial xlPasteValues

The above seems to work though (column B for this specific scenario), just thought I would share.

 

[DanteAmor]

ok. Thanks for the feedback.