Prev: Reminder - Microsoft Responds to the Evolution of Community
Next: Columns("C:C").Value = Columns("D:D").Value not working after addi
From: Frank on 20 May 2010 11:31
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?
From: Frank on 20 May 2010 12:04
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
From: GS on 20 May 2010 12:05
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(WksContainingSourceData")

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

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

HTH
Garry


 | 
Pages: 1
Prev: Reminder - Microsoft Responds to the Evolution of Community
Next: Columns("C:C").Value = Columns("D:D").Value not working after addi