It would be easier to read & debug if you set worksheet
object to the sheets etc...


Function Copy(ByVal RowNum As Long)
Dim WBTest As Workbook
Dim WSRegion As Worksheet
Dim WSMacro As Worksheet

Set WBTest = Workbooks("Test.xls")
Set WSRegion = _
Workbooks("Test.xls").Worksheets("Region")
Set WSMacro = WBTest.Worksheets("Macro")


C1 = 2
For S2 = 2 To 50000
With WSR
If .Cells(S2, 1) = "" Then Exit For
If Right(.Cells(S2, 3), 3) _
= WSMacro.Cells(RowNum, 4) _
And Right(.Cells(S2, 3), 3) _
< "South" Then

For S3 = 1 To 30
.Cells(C1, S3) = .Cells(S2, S3)
Next S3
C1 = C1 + 1
ElseIf Left(.Cells(S2, 3), 4) _
= WSMacro.Cells(RowNum, 5) Then

For S3 = 1 To 30
.Cells(C1, S3) _
= .Cells(S2, S3)
Next S3
C1 = C1 + 1
End If
Next S2
End Function



Try this.
By the way otice that I changed the parameter to Long
In the code where you test the value of a string, VBA
will coerce the value. eg
If "123" 100 then
This is a text value ("123") which VB coerces to a number
123 then tests againt 100.
This isn't an error. However if the value was "A" then an
error would occur. By passing the parameter as long, you
enforce the rule and the coercion cannot happen as the
variable is now a number by definition.





-----Original Message-----
I've got Type Mismatch error message while I'm running
the following code:
(It happens half way through, so I think the code can
work ok, but I don't know why it stops right in the
middle!!!!)
How can I improve the code and solve the problem? Pls
Help!!! Thanks!!!!

Function Copy(ByVal RowNum As String)
Sheets("Region").Select
C1 = 2
For S2 = 2 To 50000
If Workbooks("Test.xls").Worksheets
("Region").Cells(S2, 1) = "" Then Exit For
If Right(Workbooks("Test.xls").Worksheets
("Region").Cells(S2, 3), 3) _ <----- It stop right here
= Workbooks("Test.xls").Worksheets
("Macro").Cells(RowNum, 4) _
And Right(Workbooks("Test.xls").Worksheets
("Region").Cells(S2, 3), 3) _
< "South" Then

For S3 = 1 To 30
Worksheets("Region").Cells(C1, S3) _
= Workbooks
("Test.xls").Worksheets("Region").Cells(S2, S3)
Next S3
C1 = C1 + 1
ElseIf Left(Workbooks("Test.xls").Worksheets
("Region").Cells(S2, 3), 4) _
= Workbooks("Test.xls").Worksheets
("Macro").Cells(RowNum, 5) Then

For S3 = 1 To 30
Worksheets("Region").Cells(C1, S3) _
= Workbooks
("Test.xls").Worksheets("Region").Cells(S2, S3)
Next S3
C1 = C1 + 1
End If
Next S2
End Function

.