• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel Macro: Closing the Source workbook after Copy Paste [SOLVED]

Asingham

New Member
Hi,


Requirements:

I have a workbook by name Source with a worksheet by name Data. It has columns from A to E, but I don't mind how many rows and columns it has.

I have a workbook by name DestBook with 3 worksheets - Dest1, Dest2, and Dest3


Purpose:

Delete the existing contents in the sheet Dest1 in workbook DestBook

Copy entire rows and columns from sheet "Data" in workbook "Source"


Here is the excel macro i took from web and customized to achieve this purpose.

[pre]
Code:
Sub MoveSourceData()

Dim wbCopy As Workbook
Dim wsCopy As Worksheet
Dim rngCopy As Range
Dim wbPaste As Workbook
Dim wsPaste As Worksheet
Dim rngPaste As Range

Worksheets("Dest1").Cells.Delete

Set wbCopy = Workbooks. _
Open("C:DocumentsSource.csv ")
Set wsCopy = wbCopy.Worksheets("Data")
Set rngCopy = wsCopy.Range("a:e").EntireColumn
Set wbPaste = Workbooks("DestBook.xlsm")
Set wsPaste = wbPaste.Worksheets("Dest1")
Set rngPaste = wsPaste.Range("a1")

rngCopy.Copy
rngPaste.PasteSpecial
' Section Ends
End Sub
[/pre]
Issue:

Once I run this macro from the "DestBook" workbook, I'm able to get the task done. However, at the end the "Source" workbook pops up and the cursor is not returning back to the "DestBook" workbook from where I initiated this macro.


Please suggest what additional logic should I add to my code. Thanks in advance
 
I think you can just add the line

Code:
wbCopy.Close

you shouldn't need to activate the other workbook, but just in case, you could then have the line:

wbPaste.Activate
 
Back
Top