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: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: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: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(WksCon tainingSourceData")

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

wksTarget.Range("C:C") = wksSource.Range("D: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: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:D").Value

I ran the code without the Columns("C:C").Value = Columns("D: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:D").Value

I ran the code without the Columns("C:C").Value = Columns("D: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: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" schrieb im Newsbeitrag
...
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.