1

I have a macro to copy data based on filters. But some cells that are being copied from already have formulas in them, so I want to copy that and paste only as text or values.I tried using the below. I tried xlValues and Format text, both of them giving errors - Run Time error '438' . Object doesn't support this property or method. 

Sub DS()

    Dim sourceWorkbook As Workbook
    Dim targetWorkbook As Workbook
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet

    Dim sourceWorkbookPath As String
    Dim targetWorkbookPath As String
    Dim lastRow As Long



    ' Define workbooks paths
    sourceWorkbookPath = "Exceptional Transfer -2020 v2.xlsm"
    targetWorkbookPath = "template2.xlsx"

    ' Set a reference to the target Workbook and sheets
    Set sourceWorkbook = Workbooks.Open(sourceWorkbookPath)
    Set targetWorkbook = Workbooks.Open(targetWorkbookPath)

    ' definr worksheet's names for each workbook
    Set sourceSheet = sourceWorkbook.Worksheets("A")
    Set targetSheet = targetWorkbook.Worksheets("B")



    With sourceSheet

        ' Get last row
         lastRow = .Range("K" & .Rows.Count).End(xlUp).Row

        .Range("A1:Q1").AutoFilter Field:=14, Criteria1:="PENDING"
        .Range("A1:Q1").AutoFilter Field:=11, Criteria1:="U3R", Operator:=xlOr, Criteria2:="U2R"

        .Range("K2:K" & lastRow).SpecialCells(xlCellTypeVisible).Copy _
                                     Destination:=targetSheet.Range("A1")
        .Range("C2:C" & lastRow).SpecialCells(xlCellTypeVisible).Copy _
                                     Destination:=targetSheet.Range("B1")
        .Range("E2:E" & lastRow).SpecialCells(xlCellTypeVisible).Copy _
                                     Destination:=targetSheet.Range("E1").PasteSpecial xlValues
        .Range("G2:G" & lastRow).SpecialCells(xlCellTypeVisible).Copy _
                                     Destination:=targetSheet.Range("F1")
        .Range("S2:S" & lastRow).SpecialCells(xlCellTypeVisible).Copy _
                                     Destination:=targetSheet.Range("I1")
        .Range("T2:T" & lastRow).SpecialCells(xlCellTypeVisible).Copy _
                                     Destination:=targetSheet.Range("J1")
        .Range("U2:U" & lastRow).SpecialCells(xlCellTypeVisible).Copy _
                                     Destination:=targetSheet.Range("C1")



    End With

    On Error Resume Next
    sourceSheet.ShowAllData
    On Error GoTo 0

End Sub
Roy
  • 85
  • 8
  • Hi @BigBen ......I used this link to find out xlPasteValues.....but it is not working in my code...that is why I posted a question. – Roy Mar 12 '20 at 19:17
  • https://stackoverflow.com/a/34886033/4539709 – 0m3r Mar 12 '20 at 19:18
  • Don't use the `Destination` method. use `Range.PasteSpecial` (i.e. copy on one line and paste on completely separate line) – urdearboy Mar 12 '20 at 19:19
  • Hi @0m3r It doesn't work.....I tried this .Range("E2:E" & lastRow).SpecialCells(xlCellTypeVisible).Copy _ Destination:=targetSheet.Range("E1").PasteSpecial Paste:=xlPasteValues – Roy Mar 12 '20 at 19:22
  • 2
    remove the ` _ Destination:=` and have two separate lines – Scott Craner Mar 12 '20 at 19:25

1 Answers1

6

Drop the one-liner copy/paste with Destination and switch over to the multi-line copy/paste method which gives you access to the .PasteSpecial property

'Copy here
.Range("K2:K" & lastrow).SpecialCells(xlCellTypeVisible).Copy

'Paste here
targetSheet.Range("A1").PasteSpecial xlPasteValues
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
urdearboy
  • 14,439
  • 5
  • 28
  • 58
  • Hi @urdearboy This worked....Can you give me the reason why was this happening? Also I'm only changing this for the Range which has formulas....that is fine , right? – Roy Mar 12 '20 at 19:35
  • 1
    `Range.Copy Destination` does not have access to the `PasteSpecial` properties so you were calling a property that did not exist – urdearboy Mar 12 '20 at 19:36
  • 1
    Well technically the `PasteSpecial` method, not property. – BigBen Mar 12 '20 at 19:59