Hi Experts,

I'm copying values from one sheet and pasting onto other sheet. In the cells
that I'm copying, there are cells with no values, however while pasting I
want to replace blank cells with 0.

My sample code which copies and pastes the values is as below:
For example, Column A has few blanks cells. I want to substitute blank cells
with 0.
*****************
Sub CopyValues()

Dim NewSet As String
Dim NewSet1 As String
Dim NewSet2 As String
Dim NewSet3 As String
Dim NewSet4 As String
Dim NewSet5 As String
Dim CurLocation As String

CurLocation = ActiveCell.Address

Sheets("Source").Select
Columns("A:G").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Target").Select
Columns("A:G").Select

ActiveSheet.Paste

End Sub
************************

Maybe adding before the End Sub Line:

With Sheets("Target").Range("A:G")
..CurrentRegion.SpecialCells(xlCellTypeBlanks) = 0
End With

Back up File Before you try !!


"Joe" wrote:

Hi Experts,

I'm copying values from one sheet and pasting onto other sheet. In the cells
that I'm copying, there are cells with no values, however while pasting I
want to replace blank cells with 0.

My sample code which copies and pastes the values is as below:
For example, Column A has few blanks cells. I want to substitute blank cells
with 0.
*****************
Sub CopyValues()

Dim NewSet As String
Dim NewSet1 As String
Dim NewSet2 As String
Dim NewSet3 As String
Dim NewSet4 As String
Dim NewSet5 As String
Dim CurLocation As String

CurLocation = ActiveCell.Address

Sheets("Source").Select
Columns("A:G").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Target").Select
Columns("A:G").Select

ActiveSheet.Paste

End Sub
************************

After you have completed the copy, run:

Sub blanks_to_zeros()
Dim r As Range
Set r = Sheets("Target").Columns("A:G").SpecialCells(xlCel lTypeBlanks)
r.Value = 0
End Sub
--
Gary's Student


"Joe" wrote:

Hi Experts,

I'm copying values from one sheet and pasting onto other sheet. In the cells
that I'm copying, there are cells with no values, however while pasting I
want to replace blank cells with 0.

My sample code which copies and pastes the values is as below:
For example, Column A has few blanks cells. I want to substitute blank cells
with 0.
*****************
Sub CopyValues()

Dim NewSet As String
Dim NewSet1 As String
Dim NewSet2 As String
Dim NewSet3 As String
Dim NewSet4 As String
Dim NewSet5 As String
Dim CurLocation As String

CurLocation = ActiveCell.Address

Sheets("Source").Select
Columns("A:G").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Target").Select
Columns("A:G").Select

ActiveSheet.Paste

End Sub
************************