bizarre formatting issue

[Andrew]

Hello,
I'm using VBA code to copy and paste some data from one sheet to
another. On sheet 1, there are a few cells which are blank. When
they get pasted to sheet 2, the corresponding cells have the following
format:

0.00 0.00 0.00

This is one cell, not three. I have tried to clear formatting, but
this is how it is getting pasted. Any ideas on how to fix this
through VBA?

thanks

 

[Dave Peterson]

I'd do some checking first.

Maybe the cells that you're copying aren't really empty. The values could be
hidden by a custom number format, a font color that matches the fill color, or
conditional formatting.

And if the cells really are empty, I'd look for some sort of event
(worksheet_change) macro that's "fixing" your empty cells to show what you see.

And one more...

Is there a chance that you're pasting using pastelink--so that you end up with a
formula? (0.00 in each cell or "0.00 0.00 0.00" in a single cell???)

 

[Andrew]

I'd do some checking first.

Maybe the cells that you're copying aren't really empty.  The values could be
hidden by a custom number format, a font color that matches the fill color, or
conditional formatting.

And if the cells really are empty, I'd look for some sort of event
(worksheet_change) macro that's "fixing" your empty cells to show what you see.

And one more...

Is there a chance that you're pasting using pastelink--so that you end upwith a
formula?  (0.00 in each cell or "0.00 0.00 0.00" in a single cell???)

Here's my code for the paste. First I paste column widths, then I
paste values.

Range("A1:Z1000").Copy
Worksheets("SUMM").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone,
_
SkipBlanks:=False, Transpose:=False

Worksheets("COS").Range("A1:Z1000").Copy
Worksheets("SUMM").Range("A1:Z1000")
Range(Cells(1, 1), Cells(startrow - 1, endcol)).Copy
Worksheets("SUMMARY").Cells(1, 1)

Now, once the values are pasted, I then sum them in columns. The
summation is then put into a cell beneath each column. It is in these
cells where the odd formatting is showing up. Here is what makes it
more odd...some of the summation cells show a zero properly as 0.00.
Others show it as 0.00 0.00 0.00 0.00....continuing as wide as you can
stretch the column. Please help.

 

[Dave Peterson]

You didn't answer any of my questions!

But this the second .copy line in this portion:
Worksheets("SUMM").Range("A1:Z1000")
Range(Cells(1, 1), Cells(startrow - 1, endcol)).Copy

Is copying from the current worksheet (SUMM)--well, if the code is in a general
module.

Is that what you wanted to do?

 

[Andrew]

You didn't answer any of my questions!

But this the second .copy line in this portion:
Worksheets("SUMM").Range("A1:Z1000")
Range(Cells(1, 1), Cells(startrow - 1, endcol)).Copy

Is copying from the current worksheet (SUMM)--well, if the code is in a general
module.

Is that what you wanted to do?

"But this the second .copy line in this portion:
Worksheets("SUMM").Range("A1:Z1000")
Range(Cells(1, 1), Cells(startrow - 1, endcol)).Copy

Is copying from the current worksheet (SUMM)--well, if the code is in
a general
module."

This code exists in sheet(1). SUMM is sheet 2. The cells which are
copied are all formatted as Number with 1000 separator and 2 decimal
places. The empty cells are empty. But the cells where this is
happening are within inserted rows. I sum each column, then insert a
row, and in that row I place the calculated values. So, these
inserted cells weren't part of the original copy. Another item worth
noting is that there are 8 columns which get summed. The non zero
values all come out with normal formatting. Of the zero values, about
half of them end up with the odd formatting. There is no apparent
pattern as to which ones come out wrong.

Is that what you wanted to do?

 

[Dave Peterson]

If the code is in the worksheet module for sheets(1), then it's copying the
cells in sheets(1).

Is that what you wanted to do?

It's difficult to guess what you want when you sometimes use the sheet names and
sometimes use the sheet indices.

And if you qualified your ranges, your could would not depend on where it's
located.

 

[Andrew]

If the code is in the worksheet module for sheets(1), then it's copying the
cells in sheets(1).

Is that what you wanted to do?

It's difficult to guess what you want when you sometimes use the sheet names and
sometimes use the sheet indices.

And if you qualified your ranges, your could would not depend on where it's
located.

Well, you have obviously figured out that I'm not very good at
programming in Excel. My background in C, assembly and Basic, so I do
everything in standard programming constructs, such as nested For
loops. I know very little about object-oriented stuff. I have no
doubt that there is an easier method of doing what I am doing. I just
don't know what that easier way is.

Here's what I want to do - I have copied over the values in Rows 1 and
2 from another sheet

Col1 Col 2 Col3 Col4
Row1 1 3 4
Row2 2 4
--------------------------------------------------
Sums 3 7 0 4

The Sums row is an addition of the given column. I have added using
nested For loops.

But here's what I get, or something similar.

Col1 Col 2 Col3 Col4
Row1 1 3 4
Row2 2 4

 

[Dave Peterson]

I don't have any more ideas.

But I wouldn't rely on the current selection. I'd specify the destination
ranges:

Me.Range("A1:Z1000").Copy
Worksheets("SUMM").Range("A1").PasteSpecial _
Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Worksheets("COS").Range("A1:Z1000").Copy _
Destination:=Worksheets("SUMM").Range("A1:Z1000")

Me.Range("A1", Me.Cells(startrow - 1, endcol)).Copy _
Destination:=Worksheets("SUMMARY").Cells(1, 1)

I still don't know what sheet owns the code--is it COS, Summ or Summary or
something else?

 

[Andrew]

I don't have any more ideas.

But I wouldn't rely on the current selection.  I'd specify the destination
ranges:

Me.Range("A1:Z1000").Copy
Worksheets("SUMM").Range("A1").PasteSpecial _
      Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

Worksheets("COS").Range("A1:Z1000").Copy _
  Destination:=Worksheets("SUMM").Range("A1:Z1000")

Me.Range("A1", Me.Cells(startrow - 1, endcol)).Copy _
  Destination:=Worksheets("SUMMARY").Cells(1, 1)

I still don't know what sheet owns the code--is it COS, Summ or Summary or
something else?

Well, here's where I look like a total hack... I would like to write
one code on one sheet, have all the variables global, and go from
there. But what I have done is different. The first sheet ("COS")
has the code for sorting and copying data onto the second sheet
("SUMM"). The SUMM sheet adds the columns of numbers, inserts a new
row, then writes the sums into the new row.

 

[Dave Peterson]

Sorry,

I'm confused and out of suggestions.

Well, here's where I look like a total hack... I would like to write
one code on one sheet, have all the variables global, and go from
there. But what I have done is different. The first sheet ("COS")
has the code for sorting and copying data onto the second sheet
("SUMM"). The SUMM sheet adds the columns of numbers, inserts a new
row, then writes the sums into the new row.

 

[Andrew]

Sorry,

I'm confused and out of suggestions.

I ran the code on another computer. It works fine.