Columns("C:C").Value = Columns("D:D").Value not working after adding16th page

[Frank]

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?

 

[Frank]

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

 

[GS]

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

 

[Barb Reinhardt]

What is the rest of your code?

 

[Frank]

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.

 

[GS]

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

 

[Frank]

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,

 

[Helmut Meukel]

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.

 

[Frank]

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,

 

[GS]

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

 

[Frank]

Hi Garry:

Using Range("C:C") = Range("D").Value does not work either.

It stops at the 16th sheet as well.

 

[GS]

Frank wrote :

Hi Garry:

Using Range("C:C") = Range("D").Value does not work either.

It stops at the 16th sheet as well.

In this case, I suggest you post your code in its entirety so we can
see how to better help you fix it.

 

[Helmut Meukel]

Hi Frank,

I got curious an made some tests:
(I numered the different code passages for each test, see comments)

In a new workbook with 1 sheet:
Sub Test()
Dim ws As Worksheet
i = 1
Do
i = i + 1
'insert new sheet
Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count))
ws.Name = "Sheet" & i
'let's assign the values of one column to another
'1.Test
'1'ws.Columns("C:C").Value = ws.Columns("D").Value
'2.Test: Range instead Column
'2'ws.Range("C:C").Value = ws.Range("D").Value
Loop Until i = 30

'3. to 6.Test: 1 + 2 (above) commented out, added second loop for the
copying
'3.Test: just a second loop
'4.+ 5.Test: trying to avoid the Out of Memory and 1004 errors
'by setting to Nothing and adding doevents (desperate now)
'6.Test: instead of iterating the sheets perform the same operation multible
times
'for the same sheet

Set ws = Nothing
i = 1
Do
i = i + 1
'3'Set ws = Worksheets("Sheet" & i)
'6'Set ws = Worksheets("Sheet" & 1)
ws.Columns("C:C").Value = ws.Columns("D").Value
'ws.Range("C:C").Value = ws.Range("D").Value
'4'Set ws = Nothing
'5'DoEvents
Loop Until i = 30

End Sub

Guess what happened: the code always stopped after 16 successful
passes with an Out of Memory error message followed by an error 1004
The last test (number 6) run without any error!
Did the tests on an old machine with Excel97 and WinNT4 and
plenty of virtual memory.

Helmut.

 

[Frank]

Hi Garry:

You can try the code I had posted earlier:

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

While the code does not do anything, it just proves my piont that
there is a bug in Excel.

And here is another bug:

I'm developping an application where people can optimize data given a
slew of options.

Each execution requires that an existing sheet with data must be
duplicated in order performe calculations,

I discovered that using Sheets("test").Copy After:=Sheets("test") will
fail after running the execution after about 100 times (It depends how
much data is on Sheets("test").

It forces the user to close Excel. Once reopened, it works again until
the nex time.

So I have used the copy/paste method and it works fine.

Maybe they are solutions to those issues but I am not a programmer by
profession.

 

[GS]

Frank was thinking very hard :

Hi Garry:

You can try the code I had posted earlier:

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

While the code does not do anything, it just proves my piont that
there is a bug in Excel.

And here is another bug:

I'm developping an application where people can optimize data given a
slew of options.

Each execution requires that an existing sheet with data must be
duplicated in order performe calculations,

I discovered that using Sheets("test").Copy After:=Sheets("test") will
fail after running the execution after about 100 times (It depends how
much data is on Sheets("test").

It forces the user to close Excel. Once reopened, it works again until
the nex time.

So I have used the copy/paste method and it works fine.

Maybe they are solutions to those issues but I am not a programmer by
profession.

Hi Frank,

I still don't see where the contents of Columns("D") gets onto the
sheet so as to copy the value to Columns("C"), so I entered this
formula into several rows of Columns("D"):
=Row()*2

Then, in the Immediate Window I entered:
Columns("C").Value=Columns("D").Value

and the values appeared as expected.

I ran your code to add worksheets and populate the data as you are
doing. I got a memory error as did Helmut. (Probably because I had 8
apps running at the time, and so resources were heavily taxed) Just to
verify my hardware, I did this on a XP SP3 machine running 1.6ghz
processors (dual) with 2gb RAM. So nothing special there! I left all as
was to continue testing with the current tax on resources left in
place.

Next, I opened a new workbook with one sheet (my usual default #sheets)
and entered the following in the Immediate Window:
ActiveWorkbook.Sheets.Add After:=Sheets(1), Count:=40

and the 40 sheets appeared instantaneously.

Next, I grouped all the sheets from 2-41 and entered the following in
the Immediate Window:
UpdateSelectedSheets

which ran the following procedure:

Sub UpdateSelectedSheets()
Dim wks As Worksheet, vData() As Variant, c As Range
Dim lRows As Long, r As Long
With ActiveWorkbook.Sheets("Sheet1")
lRows = .UsedRange.Rows.Count
ReDim vData(lRows)
For r = LBound(vData) To UBound(vData)
vData(r) = .Cells(r + 1, "D").value
Next
End With
For Each wks In ActiveWindow.SelectedSheets
wks.Range("C1:C" & CStr(lRows)).value = vData
Next
End Sub

Just as instananeously as the 40 sheets were added, they were all
successfully updated with the data.

I repeated this by adding another 40 sheets, grouping all 80, and
running the UpdateSelectedSheets procedure again. I got the same result
without error!

Not sure this would work for you but I strongly recommend you consider
a different approach to adding AND updating the 40 sheets one at a
time. Setting object refs and maintaining variables adds to the
resources load, and so anything done in quantity should be done as much
as possible without over-taxing available resources.

If you google here in this NG you'll find code here to group sheets. If
not found then post back and I'll provide code.

In case we don't resolve this before M$ shuts this NG down, I'll try
finding this thread in the new location. (Assuming the posts are
archived there) Otherwise, we'll have to figure something out. I
already tried the NNTPbridge deal but when I signed in I was informed
that my credentials were already in use by another member. IOW, it
didn't recognize me as me! Kinda put me off wanting to persist further.

regards,

 

[Frank]

Hi Garry:

I’ll give you the background as to why I used so many sheets:

I am not a programmer by trade but I’m able to create some decent VBA
routine.

On each sheet (variable i) that is being created, I get external data
with two columns: Column A is for date and Column B is for data.

I then need to get further external query to adjust the data. I
previously used the r1c1 method and converted the formula using
Columns(“C:C”).value = Columns(“D”).value and discovered if failed
at the sixteenth sheet. I found a fix around it but still cannot
understand why it fails (hence the useless code I previously posted
and which apparently gives you an error as well.)

One all adjustments are made, I am left with two columns (A for date
and B for data)

I name the used ranged (source + i). The number of rows vary and not
all dates are the same (sheet2 might have 1/1/2000 while sheet3 might
not have that date).

Once I have my 40 sheets, I combine them via a pivot table and I have
all my data according to dates. Basically, I created a database.

I had a previous version using only one sheet but I was limited by the
number of rows (65536) to create the pivot table.

They are maybe better ways to code this but as I mentioned before, I
am not a programmer by trade.

I’ve tried to duplicate the Sheets("test").Copy After:=Sheets("test")
error I get but was not able to. I also found a work around and my
posting was more of a curiosity.

Regards,

 

[GS]

Frank brought next idea :

Hi Garry:

I’ll give you the background as to why I used so many sheets:

I am not a programmer by trade but I’m able to create some decent VBA
routine.

On each sheet (variable i) that is being created, I get external data
with two columns: Column A is for date and Column B is for data.

I then need to get further external query to adjust the data. I
previously used the r1c1 method and converted the formula using
Columns(“C:Câ€).value = Columns(“Dâ€).value and discovered if failed
at the sixteenth sheet. I found a fix around it but still cannot
understand why it fails (hence the useless code I previously posted
and which apparently gives you an error as well.)

One all adjustments are made, I am left with two columns (A for date
and B for data)

I name the used ranged (source + i). The number of rows vary and not
all dates are the same (sheet2 might have 1/1/2000 while sheet3 might
not have that date).

Once I have my 40 sheets, I combine them via a pivot table and I have
all my data according to dates. Basically, I created a database.

I had a previous version using only one sheet but I was limited by the
number of rows (65536) to create the pivot table.

They are maybe better ways to code this but as I mentioned before, I
am not a programmer by trade.

I’ve tried to duplicate the Sheets("test").Copy After:=Sheets("test")
error I get but was not able to. I also found a work around and my
posting was more of a curiosity.

Regards,

Hi Frank,

Thanks for the additional info. This confirms my suspicions about what
it was that you were trying to do. So essentially, it appears you are
parsing out data from a data source (querying a database?) onto
separate sheets so data is grouped according to some logical criteria.

Using Excel as a database is not the best choice, but it does have
valid purpose within its limitations. One major limitation for large
numbers of records is the 65,536 row limit on XL11 and earlier. (I've
heard XL12 has a row limit of about 1 million) Excel and VBA are
storing that data in mem until it's freed or the variables are
destroyed. It all adds up rather quickly when we're working with lots
of objects and large amounts of data.

One thing that stands out to me is that you are trying to work with
entire columns of data. Nothing wrong with that per se, but it taxes
resources heavily when you do lots of it within the same procedure.
Each time you set/load variables with new values it adds to the amount
of space reserved in memory each time you reset/reload. This mem is not
cleared until your procedure ends and/or all the variables/refs are
destroyed. It might prove a more reliable approach to put your
individual steps into separate procedures that are called from your
main procedure. For example, if you look at how I added the 40 sheets,
it used hardly any resources whatsoever because Excel did all the work
and so managed the mem for me. That works well within a single main
procedure. Your approach would work better in a separate procedure so
the mem is cleared when the proc ends.

It appears that your programming methodology also includes proceeding
one step at a time for one sheet at a time. Again, nothing wrong with
that in small quantity. What hurts is that uses more resources until
the file is saved because everything is still in memory. If I didn't
know better I'd think your method of programming involves heavy use of
the macro recorder, which normally generates extremely inefficient code
under the best of circumstances. I'm not saying that's how you work.
It's just that your approach in some ways mimics that same step-by-step
inefficiency.

Another observation is that you seem to be using more columns than
necessary to build the final data you end up with. If the end result is
in Columns("A:B"), and Columns("B") contains the resulting data from
all your other manipulations, then it seems like there's way lots more
work being done than needs to be given the source data is already
located in a database. Not trying to criticize or take away from your
accomplishment, ..just saying (as you stated already) there's probably
lots more efficient ways to do what your doing.

Anyway, I'm always glad to help! -It's available for the asking...

regards,

 

[Frank]

Hi Garry.

All criticisms are quite welcome.

I’m a finance guy by the way.

When I started using VBA 8 years ago, I was using the macro recorder
and then cleaning it.

As I got better with books, my skills improved. I build quite
sophisticated spreadsheet applications. My clients like them. One even
suggested I sell them but the quality of my programming skills is not
high enough to make it commercial.

See, when you write “Each time you set/load variables with new values
it adds to the amount
of space reserved in memory each time you reset/reload. This mem is
not
cleared until your procedure ends and/or all the variables/refs are
destroyed”, that just goes right over my head.

I do make use of running several procedures from the main “button” (my
worksheets involve buttons to execute the code”. I build of lot or
errhandler. Sometimes too many and I had to revert to on error goto 0
to find out about this column.value issue.

Again, thank for responding. I appreciate it.

 

[GS]

Frank brought next idea :

Hi Garry.

All criticisms are quite welcome.

I’m a finance guy by the way.

When I started using VBA 8 years ago, I was using the macro recorder
and then cleaning it.

Hi Frank,

This confirms my suspicion. I didn't see any of that 'garbage' in your
code, so you've done well in that regard. Though, the evidence of its
step-by-step nature still shows.

Well, it sounds familiar. I guess I could consider myself a 'finance
guy' in that my formal schooling was business where I majored in
accounting and management. After college I worked for a Fortune500 firm
as a cost accountant doing production analysis and forensic accounting.
I left employment to put my schooling to use running my own business,
which was working at my hobby.

As I got better with books, my skills improved. I build quite
sophisticated spreadsheet applications. My clients like them. One even
suggested I sell them but the quality of my programming skills is not
high enough to make it commercial.

Sounds familiar, and is why I push myself toward becoming a pro-level
Excel developer. I usually do complex multi-sheet/multi-book projects
and spend a lot of time designing custom spreadsheet solutions. Not
claiming that I'm professional quality yet, but I aspire toward that
nevertheless. My accountant strongly urged me to make some of my
solutions commercially available. I resisted for a long time because I
felt they required more than an average level of skill by users, not

See, when you write “Each time you set/load variables with new values
it adds to the amount
of space reserved in memory each time you reset/reload. This mem is
not
cleared until your procedure ends and/or all the variables/refs are
destroyedâ€, that just goes right over my head.

I do make use of running several procedures from the main “button†(my
worksheets involve buttons to execute the codeâ€. I build of lot or
errhandler. Sometimes too many and I had to revert to on error goto 0
to find out about this column.value issue.

I started using Excel in v4 because that's what was in use where I
worked. I read my first VBA book (Excel 2000 Power Programming with VBA
by J. Walkenback) on Thanksgiving weekend of 2003. In May of 2004 I met
Rob Bovey after downloading his Excel Utilities addin. We quickly
developed a friendship based on common interest in Excel VBA. It wasn't
until several months had passed that I discovered he was one of the
leading Excel program development experts on the planet, who along with
other leading Excel experts, had authored a series of books on the
subject. I immediately added these books to my resources library, and
have purchased the revisions as they published ever since. I highly
recommend you obtain as many of these as is practical. The books I mean
are Wrox's Programmer to Programmer series name "Excel xxxx VBA
Programmer's Reference" by authors Bovey, Bullen, Green. The reason I
urge you to do this is because the series is discontinued. (Excel
2007... is the last edition published, and they'll not be authoring any
more in this series) Another book I highly recommend by the same
authors/publisher is "Professional Excel Development".

I have no doubt these books will take you closer to where you're more
comfortable about commercializing some of your solutions. All the best
wishes for that journey should you choose to go there!

Again, thank for responding. I appreciate it.

You're certainly in the right neighborhood in this NG because there's
some very bright Excel programmers here. I have learned a lot from
reading their posts, and is why I try to give back where I can.

regards,