VBA - Copy & Paste

[MartinST]

I have a workbook, lets assume Workbook 1 that contains data in column B (Range B1:B30, not all rows will necessarily contain text but that is my default range) that I Copy & Paste into Workbook 2,3 and 4 by using the following code.

Application.Goto .Range("B" & Rows.Count).End(xlUp).Offset(1), True
    End With
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

The problem that I experience now is the following, If I have copied data (Text) in Range B1:B30 and paste that into workbook 2, the next time that I ran the Macro, VBA sees that there is text up to row 30 in Workbook 2 which is not true, the rows let say B1:B10 contains text and Rows B11:B30 is empty. VBA runs down to Row B31 and paste the next round of text there.

If I delete Range B11:B30 prior to running the Macro, then it works perfectly. What am I doing wrong?

 

[VoG]

Presumably the cells that you are copying contain formulas that return "". Excel does not see those as blank. See http://www.mrexcel.com/forum/showthread.php?t=553333

 

[MartinST]

Thanks VoG for the reply and help. The copied cells do not contain any formulas it is plain text, but the macro from jonmo1 on the reference thread does the trick.

Sub ClearNullStrings()
Selection.Value = Selection.Value
End Sub