Copy and Paste Special #Value Error
 

I have some code that I use to make a new worksheet and I need to copy and
paste the values from one column to another in the new worksheet. The cells
that I copy from have formulas and are dependent on other cells that may or
may not have values in them. The problem that I am having is that the cells
that have no values on the old worksheet return a #value when pasted on the
new sheet. My preference would be to have a blank cell. The code that I am
using at present is below and I am running Office 2003.

Sub NewInventory()


ActiveSheet.Copy After:=ActiveSheet
Range("A1") = Range("A1") + 1
Range("G5:G507").Select
Selection.Copy
Range("E5").Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=True, _
Transpose:=False
Range("H5:H507,J5:J507,S5:S507").Select
Selection.ClearContents
Range("A1").Select
End Sub
--
Thanks and Regards,
Don

Copy and Paste Special #Value Error
 

It is not the paste special action that causes the problem, but rather the
formulae in the original cells. If the formula result in the original cells
evaluates to an error, your paste special operation has no option but to copy
that formula result to the destination cell.

Rather post the formula in the original cell, ad we can suggest a
workaround. Normally, a simple IF statement will take care of your problem.
Something like
=IF(ISERROR(your formula),"",your formula).
Iow, =IF(ISERROR(B6*C6),"",B6*C6), will give a result where possible, but
when one of the cells contain nothing, or text, the result will be nothing,
iso #NUM, #N/A or #WHATEVER
--
HTH

Kassie

Replace xxx with hotmail


"SSDSCA" wrote:

I have some code that I use to make a new worksheet and I need to copy and
paste the values from one column to another in the new worksheet. The cells
that I copy from have formulas and are dependent on other cells that may or
may not have values in them. The problem that I am having is that the cells
that have no values on the old worksheet return a #value when pasted on the
new sheet. My preference would be to have a blank cell. The code that I am
using at present is below and I am running Office 2003.

Sub NewInventory()


ActiveSheet.Copy After:=ActiveSheet
Range("A1") = Range("A1") + 1
Range("G5:G507").Select
Selection.Copy
Range("E5").Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=True, _
Transpose:=False
Range("H5:H507,J5:J507,S5:S507").Select
Selection.ClearContents
Range("A1").Select
End Sub
--
Thanks and Regards,
Don

Copy and Paste Special #Value Error
 

Kassie
Being reasonably versed with formulas and a struggling novice with writing
macros, I had assumed that my problem was the macro. Thanks a million for the
response - works like a charm.
--
Thanks and Regards,
Don


"Kassie" wrote:

It is not the paste special action that causes the problem, but rather the
formulae in the original cells. If the formula result in the original cells
evaluates to an error, your paste special operation has no option but to copy
that formula result to the destination cell.

Rather post the formula in the original cell, ad we can suggest a
workaround. Normally, a simple IF statement will take care of your problem.
Something like
=IF(ISERROR(your formula),"",your formula).
Iow, =IF(ISERROR(B6*C6),"",B6*C6), will give a result where possible, but
when one of the cells contain nothing, or text, the result will be nothing,
iso #NUM, #N/A or #WHATEVER
--
HTH

Kassie

Replace xxx with hotmail


"SSDSCA" wrote:

I have some code that I use to make a new worksheet and I need to copy and
paste the values from one column to another in the new worksheet. The cells
that I copy from have formulas and are dependent on other cells that may or
may not have values in them. The problem that I am having is that the cells
that have no values on the old worksheet return a #value when pasted on the
new sheet. My preference would be to have a blank cell. The code that I am
using at present is below and I am running Office 2003.

Sub NewInventory()


ActiveSheet.Copy After:=ActiveSheet
Range("A1") = Range("A1") + 1
Range("G5:G507").Select
Selection.Copy
Range("E5").Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=True, _
Transpose:=False
Range("H5:H507,J5:J507,S5:S507").Select
Selection.ClearContents
Range("A1").Select
End Sub
--
Thanks and Regards,
Don

Copy and Paste Special #Value Error
 

The pleasure is all mine Don

--
HTH

Kassie

Replace xxx with hotmail


"SSDSCA" wrote:

Kassie
Being reasonably versed with formulas and a struggling novice with writing
macros, I had assumed that my problem was the macro. Thanks a million for the
response - works like a charm.
--
Thanks and Regards,
Don


"Kassie" wrote:

It is not the paste special action that causes the problem, but rather the
formulae in the original cells. If the formula result in the original cells
evaluates to an error, your paste special operation has no option but to copy
that formula result to the destination cell.

Rather post the formula in the original cell, ad we can suggest a
workaround. Normally, a simple IF statement will take care of your problem.
Something like
=IF(ISERROR(your formula),"",your formula).
Iow, =IF(ISERROR(B6*C6),"",B6*C6), will give a result where possible, but
when one of the cells contain nothing, or text, the result will be nothing,
iso #NUM, #N/A or #WHATEVER
--
HTH

Kassie

Replace xxx with hotmail


"SSDSCA" wrote:

I have some code that I use to make a new worksheet and I need to copy and
paste the values from one column to another in the new worksheet. The cells
that I copy from have formulas and are dependent on other cells that may or
may not have values in them. The problem that I am having is that the cells
that have no values on the old worksheet return a #value when pasted on the
new sheet. My preference would be to have a blank cell. The code that I am
using at present is below and I am running Office 2003.

Sub NewInventory()


ActiveSheet.Copy After:=ActiveSheet
Range("A1") = Range("A1") + 1
Range("G5:G507").Select
Selection.Copy
Range("E5").Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=True, _
Transpose:=False
Range("H5:H507,J5:J507,S5:S507").Select
Selection.ClearContents
Range("A1").Select
End Sub
--
Thanks and Regards,
Don