Run Time error 1004: Select Method of Worksheet class failed

[Kaouther]

I have a template which worked for a while and I was even able to generate reports with a modified version of it, but now I am getting this “Run Time error 1004: Select Method of Worksheet class failed” message when trying to view results in Excel.


when I click debug I have obtained the attachement file

Visual_basic.jpg

Any idea what this can be?

Thanks in advance !!

[Don]

Hi there!

This is most likely a very simple issue with the vba where you are not telling it the correct sheet name or number in order for it to select data. If you can include the code you use here or just include the code that is highlighted when you hit the button "Debug", it will bee much easier to point out the potential issue in the code.

[Kaouther]

Code: Select all

Private Sub Workbook_Open()
    Call MyMacro
End Sub

Code: Select all

Private Sub MyMacro()

    If Sheets("MyReport").Range("E1").Value = "" Then
   
        Application.DisplayAlerts = False
        Application.ScreenUpdating = False
       
        Sheets("C1").Select
   
        ActiveSheet.Shapes.Range(Array("Picture 1")).Select
        Selection.Copy
        Sheets("MyReport").Select
        Range("B2").Select
        ActiveSheet.Paste
        Range("B1").Select
   
        Sheets("MyReport").Range("E1").Value = "Ok"
   
        ActiveSheet.EnableCalculation = False
        ActiveSheet.EnableCalculation = True
   
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
       
    End If
   
End Sub

Please note that the following line is highlighted in yellow when VB opens : Sheets ("C1").Select

Thanks !!!


Edited by Sisyphus: Code tags added. It wasn't difficult.

[Sisyphus]

The reason for the failure is that you don't have a worksheet by the name of "C2". Therefore you can't select it.
"C1" looks like a cell address.
Before you can select a cell address you should indeed activate the sheet on which you want to select C1.
I prefer to use the sheet's Activate property (rather than Select) because in the next line you will refer to that sheet as ActiveSheet. This code would seem to make sense:-

Code: Select all

        Sheets("Sheet Name").Activate
        ActiveSheet.Shapes.Range(Array("Picture 1")).Select
        Selection.Copy

        ' You can also replace it with this:-
        Sheets("Sheet Name").Activate
        ActiveSheet.Shapes.Range(Array("Picture 1")).Copy

Your next thought should be similar:

Code: Select all

        Sheets("MyReport").Activate
        Range("B2").Select
        ActiveSheet.Paste

However, you should only activate the sheet you want to see. For the Copy/Paste operation you don't need to activate or select anything. So, this will also work:-

Code: Select all

       Sheets("MyReport").Activate
       Sheets("Sheet Name").Shapes.Range(Array("Picture 1")).Copy Destination:=ActiveSheet. Range("B2")

[Kaouther]

You mean changing sheets ("C1").select by Sheets("C1").Activate

I am going to test this correction and keep you posted

Thanks!!!

[Kaouther]

C1 is the sheet name and not a cell address. I have tested your solution but the problem still remains (:

[Sisyphus]

Since the line referring to Sheet("C1") is highlighted it is the Sheet("C1") that is causing the problem. That much for sure.
Next, in most cases the sheet doesn't exist. However, I believe that you have already checked for that. So, there must be something else wrong with the sheet. If it is hidden, for example, you won't be able to select or activate it. You could still access it for read or write, but you can't make it the active sheet.
If this doesn't help, either, I'm afraid I need to ask you to post your template.

[Kaouther]

The error still remains (:

[Sisyphus]

You have two ways to proceed.

1. Don't select
   VBA doesn't need to select anything. The Select statement is for users' benefit only.Therefore, if you replace the Selection object created by the Select statement with a Range object your code can run without selecting the sheet.
2. Post the workbook
   You can take out all contents, since it is only a matter of being able to select the sheet.

[Apcbr]

Try

Code: Select all

ActiveWorkbook.Sheets("C1").Activate