Application-defined or object-defined error

[pvd5019]

Hello all. First time to the forum, I was hoping for some help with a code I have been slamming my head against the wall over for a couple of days.

The object of the code is simply to retrieve data from a number of workbooks and input this data into one master workbook. I have developed several codes which accomplish this task, however my latest assignment is a bit more complicated. Within the data sheets, there is a table which needs to be input to the master sheet but can contain any number of rows. I am selecting this data using a simple "Do While" loop. However, within this loop I am encountering a 1004 error "Application-defined or object-defined error" when trying to identify the proper cells for the data to be placed in. Here is a sample of my code which shows where I am encountering the problem:

[VBA]' DestSht is the sheet where we will be inputting our data
' Row1 keeps track of where to insert new data in the destination workbook.
Set DestBk = ThisWorkbook
Set DestSht = DestBk.Worksheets(1)
Row1 = 4

' FolderPath is the folder to call the survey files from
FolderPath = "C:\Documents and Settings\zq6135\Desktop\Account Plans\"

' Call Dir the first time, pointing it to all Excel files in the folder path.
FileName = Dir(FolderPath & "*.xl*")

' Loop until Dir returns an empty string.
Do While FileName <> ""
' Open a workbook in the folder
Set WorkBk = Workbooks.Open(FolderPath & FileName)
Set SourceSht = WorkBk.Sheets(2)

'Row2 will be used to identify how many rows or data were contained with the Execution Tracking table
Row1 = Row2
'Row3 refers to the first row of the Execution Tracking table
Row3 = 50
'The first cell of the ET table is activated to begin the Do While loop
SourceSht.Select
Cells(50, 2).Select

Do While ActiveCell.Value <> Empty
'The location of the data is identified using the "SourceRange" variables
Set SourceRange1 = SourceSht.Cells(Row3, 2)
Set SourceRange2 = SourceSht.Cells(Row3, 3)
Set SourceRange3 = SourceSht.Cells(Row3, 9)
Set SourceRange4 = SourceSht.Cells(Row3, 12)
Set SourceRange5 = SourceSht.Cells(Row3, 14)
Set SourceRange6 = SourceSht.Cells(Row3, 15)

'The destination for the data is identified using the "DestRange" variables
Set DestRange1 = DestSht.Cells(Row2, 30)
Set DestRange2 = DestSht.Cells(Row2, 31)
Set DestRange3 = DestSht.Cells(Row2, 32)
Set DestRange4 = DestSht.Cells(Row2, 33)
Set DestRange5 = DestSht.Cells(Row2, 34)
Set DestRange6 = DestSht.Cells(Row2, 35)

'The data is copied from the SourceRange variables to the DestRange variables
DestRange1.Value = SourceRange1.Value
DestRange2.Value = SourceRange2.Value
DestRange3.Value = SourceRange3.Value
DestRange4.Value = SourceRange4.Value
DestRange5.Value = SourceRange5.Value
DestRange6.Value = SourceRange6.Value

'The active cell is moved to the next row, along with variables Row2 and Row3
Row2 = Row2 + 1
Row3 = Row3 + 1
ActiveCell.Offset(1, 0).Select
Loop[/VBA]

The code is obviously much longer than this, so in order to run it you have to alter it somewhat. The error is occurring on the first DestRange line. Most other instances of this error which I have seen include not properly identifying variables, but I can't seem to figure out where my mistake lies. Any help would be greatly appreciated. Thanks in advance!

[Bob Phillips]

The code is obviously much longer than this, so in order to run it you have to alter it somewhat.

That is not really on, you can expect us to have to do work just to see what is happening. Far better you post us the workbook and one of the read workbooks so we can re-create the situation.