The sheet name to copy to is in column H of the If Statement row
 

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

The sheet name to copy to is in column H of the If Statement row
 

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

The sheet name to copy to is in column H of the If Statement row
 

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

The sheet name to copy to is in column H of the If Statement row
 

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

The sheet name to copy to is in column H of the If Statement row
 

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

The sheet name to copy to is in column H of the If Statement row
 

Hi Claus,

I was able to make it work "the loop way" with this.
(some slight name differences etc. from my test sheet)


But your array is for sure a more solid code, and works well!
Thanks,

Howard

Sub Column_Check_OneRng()

Dim rngB As Range
Dim OneRng As Range
Dim shtTo As Worksheet
Dim shtNme As String

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) = "Bidding" Then

If Not rngB.Offset(, 7) Is Nothing Then
Set shtTo = Worksheets(rngB.Offset(, 7).Value)
shtTo.Range("B" & Rows.Count).End(xlUp)(2) = rngB
End If

End If

Next

Application.ScreenUpdating = True

End Sub