Hi Howard,

Am Sun, 19 Apr 2015 14:37:27 -0700 (PDT) schrieb L. Howard:

Having trouble making shtTo be the sheet name to copy to in the If statement.
Where the names in column H are an actual sheet name, (in this case a persons name) Ted, Allen, Kelly etc.

try it with an array. That is faster than looping through the cells:

Sub Column_Check_OneRng()
Dim LRow As Long, i As Long
Dim varData As Variant

Application.ScreenUpdating = False
With Sheets("Bid log")
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
varData = .Range("A2:H" & LRow)
For i = 1 To UBound(varData)
If varData(i, 4) = "D-Col Bid" Then
Sheets(varData(i, 8)).Cells(Rows.Count, 2) _
.End(xlUp)(2) = varData(i, 1)
End If
Next
End With
Application.ScreenUpdating = True

End Sub



Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional