Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Copy Sheet From Closed Workbook Into Existing Sheet (Overwrite) on another Workbook

I’ve tried some VBA codes in here but always end up with a code that create a new sheet on destination workbook.
What I want is to copy a sheet from closed workbook into existing sheet on an open workbook (overwrite).

Example:

  • "Sheet1" from closed workbook "Book1"
  • Copied to opened workbook "Book2" existing sheet named "Sheet2" without creating a new sheet.

Thanks

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

Sub COPYSHEET()

Dim wbTarget As Workbook
Dim wbSource As Workbook

Application.ScreenUpdating = False

Set wbTarget = ActiveWorkbook
Set wbSource = Workbooks.Open("C:\Users\user1\Downloads\Book1.xlsx")

wbSource.Sheets(1).Copy before:=wbTarget.Sheets(1)
wbSource.Close SaveChanges:=False

End Sub

>Solution :

wbSource.Sheets(1).Copy before:=wbTarget.Sheets(1) copies/inserts the respective sheet before the first sheet of wbTarget.

To copy its content, please try the next way:

 With wbTarget.Sheets(1)
   .UsedRange.Clear
   wbSource.Sheets(1).UsedRange.Copy .Range("A1")
   .UsedRange.EntireColumn.autofit
 End With
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading