0

It is a simple task, but I don't know why it is not working…

This is my code :

Dim chemin As String
Workbooks.Open ("Z:\ext_Workbook.xlsx") 
Sheets("produits").Activate
Sheets("produits").Range("A1:CZ500").Copy 

ThisWorkbook.Activate
Sheets("STT").Select
ActiveSheet.Range("A1").Select
ActiveSheet.Paste

Application.DisplayAlerts = False
Workbooks("ext_Workbook.xlsx").Close

The run stop at the line : 

 Sheets("STT").Select

But I verified, and there is a sheet named STT, with the same spelling

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
Bitoku
  • 23
  • 5
  • 2
    A must read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Siddharth Rout Jan 03 '20 at 12:16
  • ThisWorkbook refers to the workbook running the macro(not the ext_Workbook.xlsx), I assume the STT sheet is in ext_Workbook.xlsx, correct? – Stachu Jan 03 '20 at 12:19
  • **1.** `Set Wb = Workbooks.Open ("Z:\ext_Workbook.xlsx")` **2.** And then `Wb.Sheets("produits").Range("A1:CZ500").Copy ThisWorkbook.Sheets("STT").Range("A1")` – Siddharth Rout Jan 03 '20 at 12:20
  • the STT sheet is in the workbook who is running the macro – Bitoku Jan 03 '20 at 12:32

1 Answers1

0

The below should work for you. 

Dim SourceWBK As Workbook, DestinationWBK As Workbook

Set SourceWBK = Workbooks.Open("Z:\ext_Workbook.xlsx")
Set DestinationWBK = ThisWorkbook

SourceWBK.Worksheets("produits").Range("A1:CZ500").Copy
DestinationWBK.Worksheets("STT").Range("A1:CZ500").PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False

Application.DisplayAlerts = False
SourceWBK.Close False
manoj0790
  • 473
  • 4
  • 10
  • No.. You cannot copy and paste like this. One way is to do it in the same line as I showed in the comment below the quesiton. If you want to paste it in separate line then use `.PasteSpecial Paste:=xlPasteAll` instead of `.Paste` – Siddharth Rout Jan 03 '20 at 12:47
  • I just removed "dim chemin as String" and now it's working… some mystery come with this solution.. – Bitoku Jan 03 '20 at 12:52
  • 1
    @SiddharthRout - Edited the Code as suggested. – manoj0790 Jan 03 '20 at 12:52