CODEWITHSUNDEEP
excelvbacopy-paste
PabNav
PabNav

Reputation: 1

How to copy and paste data from a worksheet to another in VBA Excel

I'm having a hard time trying to copy data from worksheet A and paste it in worksheet B. I'm using this simple code.

Sub CopyPasteWorksheet ()

Workbooks("New Data.xlsx").Worksheets("Export").Range("A2:D9").Copy _
    Workbooks("Repots.xlsm").Worksheets("Data").Range("A2")

End Sub

Everytime I tried to run it , it pops this message. enter image description here

I tried different methods to copy and paste it. Still not working.

Upvotes: 0

Views: 130

Answers (1)

NewLFC
NewLFC

Reputation: 92

You have to set the destination folder and open the target file first

    Sub CopyPasteWorksheet ()

    Dim wb As Workbook
    Dim wbtarget As Workbook
    Set wb = Workbooks.Open(Filename:= "Location of the file\New Data.xlsx",
    Set wbtarget = Workbooks.Open(Filename:= "Location of the file\report.xlsx",

    With Worksheets("export")
    
        .Range("A2:d9").SpecialCells(xlCellTypeVisible).Copy


        wbtartget.Worksheets(data).Range("A2").PasteSpecial xlPasteValues
End With

Upvotes: 1

Related Questions