Columns("C:C").Value = Columns("D:D").Value not working after adding 16th page

Columns("C:C").Value = Columns("D:D").Value not working after adding 16th page

Post by Fran » Sat, 22 May 2010 00:31:28


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?
 
 
 

Columns("C:C").Value = Columns("D:D").Value not working after adding 16th page

Post by Fran » Sat, 22 May 2010 01:04:33

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

 
 
 

Columns("C:C").Value = Columns("D:D").Value not working after adding 16th page

Post by GS » Sat, 22 May 2010 01:05:56

It happens that Frank formulated :

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