CODEWITHSUNDEEP
excelvba
babou
babou

Reputation: 237

Button disapear and code don't work anymore

I want to copy/past one sheet from another excel file. Nothing hard, I have this fonction (which worked before).

But now it say that there is a problem at the "ThisWorkbook.Activate" line.

How is it possible ? The file can't find itself ?

Sub Bouton1_Cliquer()


Workbooks.Open ("the way to the excel source")

    Sheets("produits").Activate
    Sheets("produits").Range("A1:AZ200").Copy

    ThisWorkbook.Activate
    Sheets("Produits").Select
    ActiveSheet.Range("A5").Select
    ActiveSheet.Paste


End Sub

Upvotes: 1

Views: 60

Answers (2)

babou
babou

Reputation: 237

Another function doesn't work :

Sub UpdateData()
    Dim WsDest As Worksheet 'destination workbook to write in
Set WsDest = Workbook("YES").Worksheets("maybe")

Dim WsSrc As Worksheet 'source workbook to match with
    Set WsSrc = Workbook("YES").Worksheets("Perhaps")

It worked before, it is the same problem, the file can't find himself. I tried to replace ThisWorkbook by the full name like in the exemple.. before it was :

Set WsDest = ThisWorkbook.Worksheets("maybe")
Set WsSrc = ThisWorkbook.Worksheets("Perhaps")

Upvotes: 0

QHarr
QHarr

Reputation: 84465

You can replace with the following which is faster as doesn't have the overhead of .Select and .Activate. You should also include the workbook name for the range you are copying from or set the workbook you have opened into a variable and use that. You would replace Activeworkbook with the variable.

ActiveWorkbook.Worksheets("produits").Range("A1:AZ200").Copy ThisWorkbook.Worksheets("Produits").Range("A5")

With workbook variable:

Dim wb As Workbook
Set wb = Workbooks.Open("the way to the excel source")
wb.Worksheets("produits").Range("A1:AZ200").Copy ThisWorkbook.Worksheets("Produits").Range("A5")

Upvotes: 1

Related Questions