Excel VBA application defined Error 1004

[handyjim]

Hi All,

Hoping someone can point me in the right direction with some code i am writing.

Background:

I currently have 8 workbooks saved in a given location.
Each workbook is in the same format and has a worksheet called "Stock"

I have written code that loops through the file and opens each workbook and finds the usedrange on Worksheet "stock" and copies it to a new workbook. It then finds the next available row and takes the usedrange from the second workbook and pastes it underneath and so on.....

Now this all works fine until i have tried to add .offset to my usedrange code to exclude the copying of the top row.

The code line that gets the error is:

Set SourceRange = WorkBk.Worksheets("Stock").UsedRange.Offset(1, 0)

Now this is working perfectly for 7 out of the 8 files in the location. However when it gets to one file in particular it returns the runtime error '1004'. Application defined or object defined Error.

really not sure why it only happens to one file. If i remove the .Offset(1,0) from the code it runs without error but i obviously get 8 x header rows somewhere in my data that i dont want.

Any help appreciated.

Thanks

 

[s hal]

I have not used this method of selecting before so I haven't had a specific error, but what does your code do before it reaches the line it errors at? I've found that often an error occurs prior to reaching the line that finally causes the program to cease functioning. Sometimes stepping through the code and watching what is going on points to what is really causing the error. Perhaps pasting more of your code would result in a more helpful response from someone as well

 

[rakeshp2010]

I tried this small piece of code as a sanity check.
Sub usedrange()
Dim Rng As Range
Set Rng = Worksheets("Sheet1").usedrange.Offset(1, 0)
Rng.Select
End Sub

The code offsets the used range by a row.

Maybe if you could post your entire code here, it will be easy to debug.