0

I get an excel (xlsx) file with two sheets everyday from our business partner. I would like to analyze this data but in my macro when

Sheets(2).Select

I get Run-time error '1004'

I tried with the sheet name, then I add a new sheet but I can't seletc that one either with macro. I recorded a macro 'select sheet' then I ran that macro and the same error message. Then I opened the file with

_corruptload:=XlCorruptLoad.xlRepairFile

but it didn't help

Danhadnagy
  • 19
  • 9
  • If you are doing this with a macro then there is no need to use `Select`. If you provide your code and possibly a snapshot of your workbook, it might help to resolve your problem – Zac May 15 '19 at 09:20
  • Like @Zac suggested you should avoid working with `Select` here is how: [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) but you can't select a worksheet unless you have as active workbook the workbook containing it, so you should first focus that workbook. – Damian May 15 '19 at 09:25
  • if i just use this basic sub, i get error message Sub data() Sheets(1).Select End Sub – Danhadnagy May 15 '19 at 09:32
  • @Damian: Not sure that's true Damian.. i.e. if my active workbook is `WB1` and I want reference `Sheet1` from `WB2`, all I have to do is: `Workbooks("WB2").Worksheets("Sheet1")`. I could also use something lik: `With Workbooks("WB2").Worksheets("Sheet1")` I don't ever need to activate the WB2 – Zac May 15 '19 at 09:32
  • @Zac of course, but if you want to `Select` `Sheet1` you must activate `WB2` that's for sure. – Damian May 15 '19 at 09:35
  • I know there are exceptions to rules but my question would be: why is OP attempting to select a sheet in the first place? More often then not it's because people don't realise that they don't have to and infect it's more efficient just to reference a sheet – Zac May 15 '19 at 09:41
  • Does the error occur all the time or just with corrupt files? – AcsErno May 15 '19 at 09:47
  • Are there 2 (or more) sheets in the workbook? Try opening the file with Excel, start IDE with Alt+F11, and issue the `Sheets(2).Select` command in the immediate window. What happens then? – AcsErno May 15 '19 at 09:50
  • @AcsErno yes, only with corrupt file. in every file there are exactly 2 sheets. When I use immediate windows, it works but then the macro fails again. – Danhadnagy May 15 '19 at 10:04
  • You need to post your code and possibly a screenshot of your project window. It is not giving you a `Subscript out of range` which would indicate it doesn't exist or recognize it. There may be other things involved. – Darrell H May 15 '19 at 10:11
  • Would also help to know where you are running your macro from, presumably not from the .xlsx file that you receive, unless you are pasting the code into the workbook when received. – Darrell H May 15 '19 at 10:44
  • If the second sheet is hidden this would occur. Try using the name of the sheet rather than the index: Sheets("mysheet").activate ought to work – Harassed Dad May 15 '19 at 11:45

0 Answers0