PDA

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



Panda
11-24-2010, 09:52 AM
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
11-25-2010, 02:04 AM
Bump

Can anyone help?

Thanks

Bob Phillips
11-25-2010, 02:14 AM
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

Panda
11-25-2010, 02:25 AM
Thanks xld, it works a treat =:)

Thanks again for your help