Inconsistent Code?
 

Hi -

This is really strange ... I have a large macro that moves data from
one book to another. The basic code is like this:
basebook.Sheets("Hours Act").Cells.Copy
Destination:=HoursFile.Sheets("Hours Act").Range("A1")
HoursFile.Sheets("Hours Act").UsedRange.Value =
HoursFile.Sheets("Hours Act").UsedRange.Value

This same code is repeated for all sheets in the 'basebook' file and
is used to consolidate approx 35 workbooks (identical). The code
works great for 34 of the workbooks, and really works fine in the 35th
book ... except ONE sheet errors out!? Why would this happen?
(NOTE: it's the 2nd line that gets highlighted on error)

I get the dreaded 'Run-time 1004: Application-Defined or Object-
defined' error. But only for this one sheet for this one workbook.
In looking for the problem, I've tried different versions of the code,
like this:
HoursFile.Sheets("Hours Act").UsedRange.Select
HoursFile.Sheets("Hours Act").UsedRange.Value = 1

These work fine also ... can anybody help to fix this issue for me?

Thanks, ray

Inconsistent Code?
 

When the code works for all but one sheet in one workbook, it falls to reason
that the code is not the problem, but something related to the sheet. Since
you are using UsedRange.Value = 1, it could be that UsedRange is not what you
expect it to be. You could step through the procedure to determine if that
is so or set up a message box to show that value for each iteration, or put a
breakpoint in the code just before that line and step into it. Whatever the
troubleshooting procedure, the result needs to identify whether the values
are what you expect, and if not then you determine why not.

"Ray" wrote:

Hi -

This is really strange ... I have a large macro that moves data from
one book to another. The basic code is like this:
basebook.Sheets("Hours Act").Cells.Copy
Destination:=HoursFile.Sheets("Hours Act").Range("A1")
HoursFile.Sheets("Hours Act").UsedRange.Value =
HoursFile.Sheets("Hours Act").UsedRange.Value

This same code is repeated for all sheets in the 'basebook' file and
is used to consolidate approx 35 workbooks (identical). The code
works great for 34 of the workbooks, and really works fine in the 35th
book ... except ONE sheet errors out!? Why would this happen?
(NOTE: it's the 2nd line that gets highlighted on error)

I get the dreaded 'Run-time 1004: Application-Defined or Object-
defined' error. But only for this one sheet for this one workbook.
In looking for the problem, I've tried different versions of the code,
like this:
HoursFile.Sheets("Hours Act").UsedRange.Select
HoursFile.Sheets("Hours Act").UsedRange.Value = 1

These work fine also ... can anybody help to fix this issue for me?

Thanks, ray