Using SpecialCells for multi-column same-row copy/pasting

Question

Is there a way to, when grabbing all numbers in column "B" by using the .SpecialCells(xlCellTypeConstants, 1), to also copy a cell in the same row?

Example:

Let's say the script found cells B2, B4, B5 with numbers. How would I also copy D2, D4, and D5? Can I do that and still use specialcells? Ultimately, I'd like to copy/paste those values into columns A & B on another sheet.

Thanks!

Dim strOutputFile As Variant
Dim wbkOut As Workbook
Dim tenln As Range
Dim tenlnPaste As Range
Dim wbkVer As Workbook

If strOutputFile(u) Like "*Lines.csv" Then
    With wbkOut.Worksheets(1)
        Set tenln = wbkOut.Worksheets(1).Cells(Rows.Count, 2).End(xlUp)
        Set tenlnPaste = wbkVer.Worksheets("TLines").Range("A" & .Rows.Count).End(xlUp).Offset(1).Resize(tenln.Rows.Count, 1)
        wbkOut.Worksheets(1).Range("B:B").SpecialCells(xlCellTypeConstants, 1).Copy
             With wbkVer.Worksheets("TenLines")
             tenlnPaste.PasteSpecial xlPasteValues
             End With
    End With
End If

Scott Holtzman Scott Holtzman · Accepted Answer · 2013-01-15T16:49:32

Yes. It's actually very easy. Do like below:

Dim rngConst as Range
On Error Resume Next
Set rngConst = wbkOut.Worksheets(1).Range("B:B").SpecialCells(xlCellTypeConstants, 1)
If Not rngConst is Nothing Then 

    rngConst.Copy 
   'do your pasting

    Set rngConts = rngConst.Offset(,2) 'for moving from B to D
    rngConst.Copy
    'do your pasting

End If
On Error Go To 0

You could also do this, to get it all into 1 copy area:

Dim rngConst as Range

On Error Resume Next

If Not rngConst is Nothing

    Set rngConst = wbkOut.Worksheets(1).Range("B:B").SpecialCells(xlCellTypeConstants, 1)
    Set rngConst = Range(rngConst, rngConst.Offset(,2))
    rngConst.Copy
    'do your pasting

End If

On Error Go To 0

But this will copy the data onto the new sheet into two contiguous columns. It will not copy from B to B and D to D, for instance.

Using SpecialCells for multi-column same-row copy/pasting

1 Answers