VBA macros for copy and pasting files from one workbook to another

%3CLINGO-SUB%20id%3D%22lingo-sub-2795203%22%20slang%3D%22en-US%22%3EVBA%20macros%20for%20copy%20and%20pasting%20files%20from%20one%20workbook%20to%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2795203%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Team%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20new%20with%20VBA%20macros%20and%20I'm%20struggling%20with%20completing%20a%20macro%20code%20to%20automate%20the%20copy%20and%20pasting%20of%20data%20from%20one%20workbook%20to%20another.%20I%20think%20I%20got%20the%20code%20for%20copying%2C%20but%20I'm%20having%20issues%20with%20pasting%20the%20file.%20Here's%20the%20code%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWorkbooks.Open%20%22Source%20File.xlsx%22%3CBR%20%2F%3EApplication.Goto%20Workbooks(%22Source%20File.xlsx%22).Sheets(%22Apple%22).Range(%22A1%3AA1%22)%3CBR%20%2F%3EApplication.Goto%20_%3CBR%20%2F%3EWorkbooks(%22Source%20File.xlsx%22).Sheets(%22Apple%22).Range(%22A1%22%2C%20%22A1%22)%3CBR%20%2F%3ECells.Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20my%20plan%20is%20to%20copy%20worksheet%20%22apple%22%20from%20workbook%20%22source%20file%22%20and%20paste%20it%20to%20worksheet%20%22banana%22%20which%20is%20on%20another%20workbook.%20We%20can%20name%20that%20workbook%20as%20%22destination%20file%22.%26nbsp%3B%20Hoping%20for%20your%20positive%20feedback.%20Thank%20you%20so%20much.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2795203%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2795509%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20macros%20for%20copy%20and%20pasting%20files%20from%20one%20workbook%20to%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2795509%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1067340%22%20target%3D%22_blank%22%3E%40Poly2021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20could%20look%20like%20this%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3ESub%20CopyData()%0A%20%20%20%20Dim%20wbkSource%20As%20Workbook%0A%20%20%20%20Dim%20wbkTarget%20As%20Workbook%0A%20%20%20%20Set%20wbkSource%20%3D%20Workbooks.Open(%22Source%20File.xlsx%22)%0A%20%20%20%20Set%20wbkTarget%20%3D%20Workbooks.Open(%22Destination%20File.xlsx%22)%0A%20%20%20%20wbkSource.Worksheets(%22Apple%22).Range(%22A1%22).Copy%20_%0A%20%20%20%20%20%20%20%20Destination%3A%3DwbkTarget.Worksheets(%22Banana%22).Range(%22A1%22)%0A%20%20%20%20Application.CutCopyMode%20%3D%20False%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2795631%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20macros%20for%20copy%20and%20pasting%20files%20from%20one%20workbook%20to%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2795631%22%20slang%3D%22en-US%22%3EThanks.%20I%20will%20try%20this%20one%20tomorrow.%20Its%20late%20now%20here%20in%20my%20place.%20I%20really%20appreciate%20this%20a%20lot.%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi Team,

 

I'm new with VBA macros and I'm struggling with completing a macro code to automate the copy and pasting of data from one workbook to another. I think I got the code for copying, but I'm having issues with pasting the file. Here's the code:

 

Workbooks.Open "Source File.xlsx"
Application.Goto Workbooks("Source File.xlsx").Sheets("Apple").Range("A1:A1")
Application.Goto _
Workbooks("Source File.xlsx").Sheets("Apple").Range("A1", "A1")
Cells.Select
Selection.Copy
End Sub

 

So my plan is to copy worksheet "apple" from workbook "source file" and paste it to worksheet "banana" which is on another workbook. We can name that workbook as "destination file".  Hoping for your positive feedback. Thank you so much. 

 

 

2 Replies

@Poly2021 

It could look like this:

Sub CopyData()
    Dim wbkSource As Workbook
    Dim wbkTarget As Workbook
    Set wbkSource = Workbooks.Open("Source File.xlsx")
    Set wbkTarget = Workbooks.Open("Destination File.xlsx")
    wbkSource.Worksheets("Apple").Range("A1").Copy _
        Destination:=wbkTarget.Worksheets("Banana").Range("A1")
    Application.CutCopyMode = False
End Sub
Thanks. I will try this one tomorrow. Its late now here in my place. I really appreciate this a lot.