Macro Help

[DP7]

I have a macro that copies values from one Worksheet (A) in a Workbook to a
range in another Worksheet (B) in the same Workbook. Some times the values in
workbook A will be empty and the Macro copies over a blank /Zero value. I
would like to modify the macro in such a way that If the Cell In worksheet A
is empty that zero value will not be copied over to worksheet (B) & That the
next value that is pasted in Workbook B will be put below the last value. In
other words I would like no spaces to be left in the range. Below is an
excerpt of my Macro.


Sub CVSCopy()
'
' CVSCopy Macro

Sheets(4).Range("C20").Value = Sheets(2).Range("H6").Value
Sheets(4).Range("C21").Value = Sheets(2).Range("H7").Value
Sheets(4).Range("C22").Value = Sheets(2).Range("H10").Value
Sheets(4).Range("G20").Value = Sheets(2).Range("J6").Value
Sheets(4).Range("G21").Value = Sheets(2).Range("J7").Value
Sheets(4).Range("G22").Value = Sheets(2).Range("J10").Value
Sheets(4).Range("G24").Value = Sheets(2).Range("L39").Value
Sheets(4).Range("G25").Value = Sheets(2).Range("L46").Value
Sheets(4).Range("C28").Value = Sheets(2).Range("H15").Value
Sheets(4).Range("C29").Value = Sheets(2).Range("H28").Value

 

[Sandy Mann]

Add a test before your copy line:

Sub CVSCopy()
'
' CVSCopy Macro

If Sheets(2).Range("H6").Value <> "" Then _
Sheets(4).Range("C20").Value = Sheets(2).Range("H6").Value
If Sheets(2).Range("H7").Value <> "" Then _
Sheets(4).Range("C21").Value = Sheets(2).Range("H7").Value
If Sheets(2).Range("H10").Value <> "" Then _
Sheets(4).Range("C22").Value = Sheets(2).Range("H10").Value
If Sheets(2).Range("J6").Value <> "" Then _
Sheets(4).Range("G20").Value = Sheets(2).Range("J6").Value
If Sheets(2).Range("J7").Value <> "" Then _
Sheets(4).Range("G21").Value = Sheets(2).Range("J7").Value
If Sheets(2).Range("J10").Value <> "" Then _
Sheets(4).Range("G22").Value = Sheets(2).Range("J10").Value
If Sheets(2).Range("L39").Value <> "" Then _
Sheets(4).Range("G24").Value = Sheets(2).Range("L39").Value
If Sheets(2).Range("L39").Value <> "" Then _
Sheets(4).Range("G25").Value = Sheets(2).Range("L39").Value
If Sheets(2).Range("H15").Value <> "" Then _
Sheets(4).Range("C28").Value = Sheets(2).Range("H15").Value
If Sheets(2).Range("H28").Value <> "" Then _
Sheets(4).Range("C29").Value = Sheets(2).Range("H28").Value
End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk