CODEWITHSUNDEEP
excelvbacopypaste
Rob O
Rob O

Reputation: 27

Copy/Paste on another sheet excluding blank cells or cells where a formula result = ""

The code below is working as designed, with one exception:

Range b4:b100 are lookup formulas from another sheet where everything below B34 is a #value! error where I'm specifying =iferror(formula),""

It is copying the resulting "" and so the next time I it runs, it begins to paste on the target sheet on B101 rather than B35.

How can I specify "Do not use any space on the target sheet with blanks where formulas existed on the source sheet"?

Sub COPYTOSAVEDWORK()

Sheets("FORMULAWORKED").Range("B4:Q100").Select
Selection.Copy
Sheets("WORKED_CLAIMS").Cells(Rows.Count, "A").End(xlUp).Offset(1). _
  PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, _
  SkipBlanks:=True, Transpose:=False
Exit Sub
End Sub

Upvotes: 1

Views: 2157

Answers (2)

JvdV
JvdV

Reputation: 75870

A value transfer as per @BigBen would work great, but you seem to want to copy/paste numbers and NumberFormat. Maybe something like the below would work:

Sub Test()

Dim rng As Range
Dim arr As Variant

With Sheets("FORMULAWORKED")
    .Range(Join(Filter(.[TRANSPOSE(IF(B4:B100<>"","B"&ROW(B4:B100)&":Q"&ROW(B4:B100),"|"))], "|", False), ",")).Copy
    Sheets("WORKED_CLAIMS").Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With

End Sub

Upvotes: 0

BigBen
BigBen

Reputation: 50008

You can call .Value = .Value on the pasted range, and that will eliminate cells with the empty string:

Sub Test()
    Dim formulaRng As Range
    Set formulaRng = ThisWorkbook.Sheets("FORMULAWORKED").Range("B4:Q100")

    With ThisWorkbook.Sheets("WORKED_CLAIMS")

        Dim nextRow As Long
        nextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1

        formulaRng.Copy
        .Cells(nextRow, "A").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        Application.CutCopyMode = False

        With .Cells(nextRow, "A").Resize(formulaRng.Rows.Count, formulaRng.Columns.Count)
            .Value = .Value
        End With

    End With

End Sub

Upvotes: 1

Related Questions