CODEWITHSUNDEEP
vbaexcel
Daniel Strong
Daniel Strong

Reputation: 213

Paste Special in VBA code not working

I have been reading and trying code, but it still will not work. Can someone tell me what I am doing wrong? My excel file has formulas on one tab, I am trying to copy and paste special onto another tab but everything I try is pasting the formula and not the text output I need. 

Dim rowCount2 As Long, shtSrc As Worksheet
Dim shtDest As Worksheet
Dim rng2 As Range
Dim currentRow As Long

Set shtSrc = Sheets("Data")
Set shtDest = Sheets("Audit")

rowCount2 = shtSrc.Cells(Rows.Count, "A").End(xlUp).Row

Set rng2 = shtSrc.Range("A1:A" & rowCount2)

currentRow = 1

For Each cell2 In rng2.Cells
    If cell2.Value <> "" Then
          cell2.Copy shtDest.Range("B" & currentRow).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
          cell2.Offset(0, 1).Copy ("C" & currentRow)
          cell2.Offset(0, 2).Copy ("G" & currentRow)
        currentRow = currentRow + 1

    End If
Next cell2

Upvotes: 2

Views: 1301

Answers (1)

Rory
Rory

Reputation: 34035

You can't use PasteSpecial with the destination parameter of the Copy method - you have to use separate operations, but as you're only copying individual cells, you can just assign the values:

shtDest.Range("B" & currentRow).Value2 = cell2.Value2
shtDest.Range("C" & currentRow).Value2 = cell2.Offset(0, 1).Value2
shtDest.Range("G" & currentRow).Value2 = cell2.Offset(0, 2).Value2

Upvotes: 3

Related Questions