Solved: Copy Data from One Sheet to another if cell is NOT empty

[Panda]

Hi There,

I have found some code to copy data from worksheet to another, however I am struggling to tweak it so that it only copies data from the cells that are not blank.

Any ideas? Below is the code that I am trying to tweak.

 
Sub Copy_Input_Data()
    Dim Ws1     As Worksheet
    Dim Ws2     As Worksheet
    Dim Dest    As Range
 
    Set Ws1 = Sheets("Input_Screen")
    Set Ws2 = Sheets("Live_Screen")
    Set Dest = Ws2.Range("B6").End(xlDown).Offset(1)
 
    Ws1.Range("B11:J17").Copy
    Dest.PasteSpecial xlPasteValues
    Application.CutCopyMode = False
End Sub

Thanks in advance

Phil

[Panda]

Bump

Can anyone help?

Thanks

[Bob Phillips]

[vba]

Sub Copy_Input_Data()
Dim Ws1 As Worksheet
Dim Ws2 As Worksheet
Dim Dest As Range
Dim i As Long

Set Ws1 = Sheets("Input_Screen")
Set Ws2 = Sheets("Live_Screen")
Set Dest = Ws2.Range("B6").End(xlDown).Offset(1)

Ws1.Range("B11:J17").Copy
Dest.PasteSpecial xlPasteValues
Application.CutCopyMode = False
For i = 7 To 2 Step -1

If Dest.Cells(1, i - 1).Value = "" Then

Dest.Cells(1, i - 1).Delete Shift:=xlToLeft
End If
Next i
End Sub
[/vba]

[Panda]

Thanks xld, it works a treat =

Thanks again for your help