-1

The control exists the sub procedure the moment it opens the workbook and throws the error as select method of Range class failed without executing the statements below the workbooks.open as shown below.

Dim FileToOpenAll As Variant
Dim SelectedIMRObook As String
Dim oWorkbook As New Workbook
Dim lastrowIMRObook As Long
         
SelectedIMRObook = "C:\Users\mahima.r\OneDrive\Automations\Hul Feb\testing\Templates\B_test_Soup.xlsx"
Set oWorkbook = Workbooks.Open(SelectedIMRObook)
oWorkbook.Activate
oWorkbook.Sheets(1).Range("A2").Select
lastrowIMRObook = Range("A" & Rows.Count).End(xlUp).Row
Range("a2:h" & lastrowIMRObook).Select
Selection.ClearContents
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
Mahima Ramachandra
  • 1
  • 1
  • 1
    Suggested reading: [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – BigBen Mar 30 '22 at 15:57
  • Before the line "oWorkbook.Activate" add a [DoEvents](https://stackoverflow.com/questions/45492573/am-i-supposed-to-always-add-doevents-after-opening-a-large-excel-file-in-vba). If you need a more robust control, [try this approach](https://stackoverflow.com/a/67496326/3221380) – Sgdva Mar 30 '22 at 16:01
  • Thanks @sgdva for the suggestion, but I have 2 other workbooks opened using Application.GetOpenFilename which I intend to use as a base file to edit the oWorkBook sheets. Any suggestion on how to use the above code without the Me.Applications? – Mahima Ramachandra Mar 30 '22 at 16:10
  • You forgot to activate or select `Sheets(1)`. It obviously isn't the `ActiveSheet` (in `oWorkbook`). On the other hand, you shouldn't select anything as already suggested by BigBen. – VBasic2008 Mar 30 '22 at 16:18
  • The issue here is the control is exiting the procedure the moment it executes the workbook.open – Mahima Ramachandra Mar 30 '22 at 16:21
  • @MahimaRamachandra use the doevents approach, it is the one that I use and so far hasn't failed. I would suggest you that you don't have as many open wb, I'd work one by one (this means open the wb, do the stuff you need to and then close it) go for the other one, and so on – Sgdva Mar 30 '22 at 16:33
  • Please [stop using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba), and you won't have this problem. – BigBen Mar 30 '22 at 16:41

1 Answers1

1

As far as I understand, you just want to clear the data in columns A:H on the first sheet of the opened workbook, starting from row 2.

Do not use Activate and Select. There is no reason to do so. Also, no need for any DoEvents.

There is almost no reason at all to use Select and/or Activate when programming VBA for Excel.

Set oWorkbook = Workbooks.Open(SelectedIMRObook)
With oWorkbook.Sheets(1)
    lastrowIMRObook = .Range("A" & .Rows.Count).End(xlUp).Row
    If lastrowIMRObook > 1 Then
        Dim RangeToClear As Range
        Set RangeToClear = .Range(.Cells(2, "A"), .Cells(lastRow, "H"))
        RangeToClear.ClearContents
    End If
End With

Read (and understand) How to avoid using Select in Excel VBA

FunThomas
  • 23,043
  • 3
  • 18
  • 34