variable not declared error & syntax error

Can anybody shed some light on this. I'm getting a variable not declared
error at:

wsTarget.Range("K1") = sum

When I take out the "Option Explicit" I then get a syntax error at:

Set SourceData = .Range(.Cells(SourceRowindex, "A"),

Here's my code which copies data from different sheets in the same workbook
to sheet1:


Option Explicit
Sub CopyData()
Dim wsTarget As Worksheet
Dim TargetRowindex As Long
Dim SourceRowindex As Long
Dim sheetindex As Long
Dim wsSource As Worksheet
Dim SourceData As Range
Set wsTarget = Worksheets("Sheet1")
wsTarget.Cells.ClearContents
wsTarget.Range("K1") = sum
For sheetindex = 2 To 9
Set wsSource = Worksheets("Sheet" & sheetindex)
SourceRowindex = 1
With wsSource
Do Until .Cells(SourceRowindex, "H") = ""
If .Cells(SourceRowindex, "H") < 100 Then
Set SourceData = .Range(.Cells(SourceRowindex, "A"),
..Cells(SourceRowindex, "I"))
TargetRowindex = TargetRowindex + 1
wsTarget.Range(wsTarget.Cells(TargetRowindex, "A"), _
wsTarget.Cells(TargetRowindex,
"I")).Value = _
SourceData.Value

End If

SourceRowindex = SourceRowindex + 1
Loop
End With
Next

End Sub



Any help would be greatly appreciated,
Gary

What is Sum. It is not declared as a variable in the local procedure so
unless it is declared globally then that is your missing declaration. If it
is declared globally then I would suggest that it should probably be changed
since sum is a reserved word (function application.sum())
--
HTH...

Jim Thomlinson


"G. Beard sbcglobal.net>" wrote:

> Can anybody shed some light on this. I'm getting a variable not declared
> error at:
>
> wsTarget.Range("K1") = sum
>
> When I take out the "Option Explicit" I then get a syntax error at:
>
> Set SourceData = .Range(.Cells(SourceRowindex, "A"),
>
> Here's my code which copies data from different sheets in the same workbook
> to sheet1:
>
>
> Option Explicit
> Sub CopyData()
> Dim wsTarget As Worksheet
> Dim TargetRowindex As Long
> Dim SourceRowindex As Long
> Dim sheetindex As Long
> Dim wsSource As Worksheet
> Dim SourceData As Range
> Set wsTarget = Worksheets("Sheet1")
> wsTarget.Cells.ClearContents
> wsTarget.Range("K1") = sum
> For sheetindex = 2 To 9
> Set wsSource = Worksheets("Sheet" & sheetindex)
> SourceRowindex = 1
> With wsSource
> Do Until .Cells(SourceRowindex, "H") = ""
> If .Cells(SourceRowindex, "H") < 100 Then
> Set SourceData = .Range(.Cells(SourceRowindex, "A"),
> ..Cells(SourceRowindex, "I"))
> TargetRowindex = TargetRowindex + 1
> wsTarget.Range(wsTarget.Cells(TargetRowindex, "A"), _
> wsTarget.Cells(TargetRowindex,
> "I")).Value = _
> SourceData.Value
>
> End If
>
> SourceRowindex = SourceRowindex + 1
> Loop
> End With
> Next
>
> End Sub
>
>
>
> Any help would be greatly appreciated,
> Gary
>
>
>