VBA: Copy specific cells from sheet to another based on a value in a cell

[lunatu]

Hi,

Im trying to run a very basic code but something is wrong with it.
Im want to copy cells B:G from sheet1 to sheet2 if there is text "close" in sheet1 column M.

My current code below is working but it also copies the cells where value in column M is something else than "close". Any ideas what gap I am having in my code?

Sub CopyWon()

Dim LR As Long, i As Long
With Sheets("Sheet1")
 LR = .Range("M" & Rows.Count).End(xlUp).Row
 For i = 1 To LR
 
 If .Range("M" & i).Value = "close" Then
 dlr = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
.Range("B" & i).Resize(2, 6).Copy Destination:=Sheets("Sheet2").Range("A" & dlr)
    End If
    Next i
End With
 End Sub

 

[Joe4]

I think the issue lies here in the range you are copying:

.Range("B" & i).Resize(2, 6)

The "Resize" command is expanding your selection to multiple rows (instead of just the row meeting the conditions).
Is that what you are really intending to do?

See here for an explanation of "Resize": Resize Property in Excel VBA

 

[lunatu]

I think the issue lies here in the range you are copying:

.Range("B" & i).Resize(2, 6)

The "Resize" command is expanding your selection to multiple rows (instead of just the row meeting the conditions).
Is that what you are really intending to do?

See here for an explanation of "Resize": Resize Property in Excel VBA

Oh yes I had left that one there! Deleting that reisize everything is working again. Thanks for your help

 

[Joe4]

You are welcome.