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.

Thanks,
Howard


Sub Column_Check_OneRng()

Dim rngB As Range
Dim OneRng As Range
Dim shtTo As Worksheet

Application.ScreenUpdating = False

Set OneRng = Sheets("Bid log").Range("A2:A" & Cells(Rows.Count,
"A").End(xlUp).Row)

For Each rngB In OneRng

If rngB.Offset(, 3) = "D-Col Bid" Then
'set shtTo to the name in column H which is a sheet name to
copy to shtTo.Range("B" & Rows.Count).End(xlUp)(2) = rngB

End If

Next

Application.ScreenUpdating = True

End Sub

shtTo is an object that *must* be initialized via a 'Set' statement!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

shtTo is an object that *must* be initialized via a 'Set' statement!

--
Garry


Hi Garry,

It is the syntax I can't get together with, this still errors out.


Sub Column_Check_OneRng()

Dim rngB As Range
Dim OneRng As Range
Dim shtTo As Worksheet

Application.ScreenUpdating = False

Set OneRng = Sheets("Bid log").Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)

For Each rngB In OneRng

If rngB.Offset(, 3) = "D-Col Bid" Then

Set shtTo = rngB.Offset(, 7)

If Not shtTo Is Nothing Then
shtTo.Range("B" & Rows.Count).End(xlUp)(2) = rngB
End If

End If

Next

Application.ScreenUpdating = True

End Sub

Howard

shtTo is dimmed 'As Worksheet', but you're trying to 'Set' it to a
range and VBA knows the difference between the two objects. I know you
also know the difference but for some reason you're a bit confused
right now. Take a step back to clear your mind, then have another go at
it!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion