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

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 wrote:

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

On Mar 3, 8:46*am, Dave Peterson wrote:
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 wrote:

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

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.

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 wrote:

On Mar 3, 8:46 am, Dave Peterson wrote:
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 wrote:

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

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

On Mar 3, 10:13*am, Dave Peterson wrote:
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 wrote:

On Mar 3, 8:46 am, Dave Peterson wrote:
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 wrote:

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

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

"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?

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 wrote:

On Mar 3, 10:13 am, Dave Peterson wrote:
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 wrote:

On Mar 3, 8:46 am, Dave Peterson wrote:
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 wrote:

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

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

"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