CODEWITHSUNDEEP
vbaexcel
Louise Dhainaut
Louise Dhainaut

Reputation: 11

How to replace a sheet using VBA ?

I'd like to copy a sheet from a workbook and paste it in the 2nd sheet in my active workbook. I'm new with vba, it doesn't seem difficult but my code doesn't work. The sheet, which is copied, is opened in a new workbook and not in my active workbook.

Thanks for your help !

My code : 

Sub copyPaste()

  Dim classeur1 As Excel.Workbook
  Dim classeur2 As Excel.Workbook

  Set classeur1 = Workbooks.Open("Macintosh HD:Users:LouiseDhainaut:Documents:Stage:test_modifiable.xlsx")
  Set classeur2 = ThisWorkbook

  classeur1.Sheets(1).Copy
  classeur1.Sheets(1).Paste Destination:=ThisWorkbook.Sheets(2).Range("A1")


  classeur2.Save
  classeur1.Close

End Sub

Upvotes: 1

Views: 6088

Answers (1)

vacip
vacip

Reputation: 5416

Try:

classeur1.Sheets(1).Copy Before:=ThisWorkbook.Sheets(2)

Instead of the 

classeur1.Sheets(1).Copy
classeur1.Sheets(1).Paste Destination:=ThisWorkbook.Sheets(2).Range("A1")

Although note that copying an entire worksheet will copy the entire worksheet, not just the contents of it.

If you are looking to copy only the contents, you will need a different code, for example:

classeur1.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets(2).Range("A1")

Upvotes: 6

Related Questions