I cannot figure this one out.

I have a simple macro which adds 40 new worksheets and retrieves data
on each of these new sheets in column C.

Here is where the code fails

Columns("C:C").Value = Columns("D").Value

It works for 15 sheets but on the 16th, I get the following error
message:

Run-time error '1004':
Application-defined or object-defined error

Any ideas?

To add to my post, if I use this code

Range(("C1"), Range("C1").End(xlDown)).Value = Range(("D1"),
Range("D1").End(xlDown)).Value

it works.

But I am still curious why it does not work on a column basis

It happens that Frank formulated :
> I cannot figure this one out.
>
> I have a simple macro which adds 40 new worksheets and retrieves data
> on each of these new sheets in column C.
>
> Here is where the code fails
>
> Columns("C:C").Value = Columns("D").Value
>
> It works for 15 sheets but on the 16th, I get the following error
> message:
>
> Run-time error '1004':
> Application-defined or object-defined error
>
> Any ideas?

Your code assumes that the target sheet is the active sheet. If you're
adding new sheets, how does Column("D") get values to populate
Column("C:C"). Why not just put the values directly in Column("C:C")?

If the data is stored on a source sheet then you need an object ref to
that sheet. (ie: wksSource) The new sheet would then be the target for
the data and so you need to ref it in the same way. (ie: wksTarget) So
your code should be something like this:

''''''''''
Dim wksSource As Worksheet, wksTarget As Worksheet

Set wksSource =
Workbooks("WbkContainingSourceData").Sheets(WksContainingSourceData")

Set wksTarget = Workbooks("WbkReceivingSourceData").Sheets.Add

wksTarget.Range("C:C") = wksSource.Range("D")
''''''''''

HTH
Garry

What is the rest of your code?



"Frank" wrote:

> I cannot figure this one out.
>
> I have a simple macro which adds 40 new worksheets and retrieves data
> on each of these new sheets in column C.
>
> Here is where the code fails
>
> Columns("C:C").Value = Columns("D").Value
>
> It works for 15 sheets but on the 16th, I get the following error
> message:
>
> Run-time error '1004':
> Application-defined or object-defined error
>
> Any ideas?
> .
>

the original code was

'remove before and after blanks via TRIM function
Range("D2", Range("C2").End(xlDown).Offset(0, 1)).FormulaR1C1 =
"=TRIM(RC[-1])"
Columns("C:C").Value = Columns("D").Value

I ran the code without the Columns("C:C").Value = Columns("D").Value
and it worked fine
but when included, if fails at the 16th sheet.

I know I could do cell.value = trim(cell).value but I
find .FormulaR1C1 to work faster.

Frank wrote :
> the original code was
>
> 'remove before and after blanks via TRIM function
> Range("D2", Range("C2").End(xlDown).Offset(0, 1)).FormulaR1C1 =
> "=TRIM(RC[-1])"
> Columns("C:C").Value = Columns("D").Value
>
> I ran the code without the Columns("C:C").Value = Columns("D").Value
> and it worked fine
> but when included, if fails at the 16th sheet.
>
> I know I could do cell.value = trim(cell).value but I
> find .FormulaR1C1 to work faster.

Well, having more info about what you're trying to do certainly helps.
In this case I suggest to just trim the values in "C:C" rather than
enter a formula in "D" to do so and then copy the result back to
"C:C". Doesn't make sense to go to all the trouble when you could use
the VBA Trim() function on the "C:C" cells.

Here's an example:

Sub TrimLeftRightSpaces()
Dim c As Range, lLastRow As Long
lLastRow = ActiveSheet.UsedRange.Rows.Count
For Each c In Range(Cells(1, "C"), Cells(lLastRow, "C"))
If Not c = "" Then c.Value = Trim$(c.Value)
Next
End Sub

HTH
Garry

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Hi Garry:

I thought of that but I find the r1c1 option faster.

I opted for the range.value = range.value and it works.

But it still puzzles me that the column.value = column.value works
fine for 15 sheets and yet fails on the 16th.

Regards,

"Frank" <(E-Mail Removed)> schrieb im Newsbeitrag
news:3f503742-914a-42cc-9dbc-(E-Mail Removed)...
> Hi Garry:
>
> I thought of that but I find the r1c1 option faster.
>
> I opted for the range.value = range.value and it works.
>
> But it still puzzles me that the column.value = column.value works
> fine for 15 sheets and yet fails on the 16th.
>
> Regards,


Did you try to change the order of the sheets?
What I mean is:
Is it always the 16th sheet regardless which sheet
is the 16th?
Or is it a particular sheet?
I assume it's a particular sheet.
Did you step through all cells of column C and look
for something unusual?
Or is there something in column D far below your last row
which may cause the problem?
I faintly remember I had once - 15 or more years ago -
a problem which I solved by selecting all rows below my
last row and deleting the selection.

Helmut.

Hi Helmut:

This is really odd and something's wrong in Excel (I'm using 2003,
maybe this bug as been fixed in later versions)

i = 1
For Each cell In Range("my_range")
i = i + 1
'insert new sheet
Worksheets.Add(After:=Sheet1).Name = "sheet" & i
Columns("C:C").Value = Columns("D").Value
Next

Of course, the above code is useless but it proves that there is an
issue with columns.value = columns.value

You will see it fail when i = 17, therefore on the 16th sheet.

Regards,

Frank used his keyboard to write :
> Hi Helmut:
>
> This is really odd and something's wrong in Excel (I'm using 2003,
> maybe this bug as been fixed in later versions)
>
> i = 1
> For Each cell In Range("my_range")
> i = i + 1
> 'insert new sheet
> Worksheets.Add(After:=Sheet1).Name = "sheet" & i
> Columns("C:C").Value = Columns("D").Value
> Next
>
> Of course, the above code is useless but it proves that there is an
> issue with columns.value = columns.value
>
> You will see it fail when i = 17, therefore on the 16th sheet.
>
> Regards,

You need to change from using Columns() to using Range().

Example:
Range("C:C") = Range("D")

You don't have to specify value unless the target range is to receive
the product of a formula in the source range. So...

Range("C:C") = Range("D").Value
where the cells in Range("D") contain a formula.

HTH
Garry

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc