0

Sorry if this is a simple question, but I have a For Next loop to swap from the currently activated Workbook to a sheet in a separate workbook based on an Input Box and then re-activating the first workbook. The code worked fine when I opened the originally activated workbook first then the second workbook, but if it is opened inversely the macro does not work. I can successfully get through the InputBox but then it runs into a 'Select method of Worksheet class failed' error. Is the Input Box worksheet selection not working?

Sub Test()

Dim IB As String
Dim Wb As Workbook, Wb2 As Workbook

Set Wb = ActiveWorkbook
    For Each Wb2 In Application.Workbooks
        Wb2.Activate
    Next
        IB = InputBox("Enter Month number", "Worksheet selection")
        Workbooks("2021 Shipments.xlsx").Worksheets(IB).Select
    Wb.Activate

End Sub
akroeker
  • 21
  • 4
  • 1
    Try adding `Workbooks("2021 Shipments.xlsx").Activate` before `Workbooks("2021 Shipments.xlsx").Worksheets(IB).Select`. – BigBen Feb 17 '22 at 19:54
  • Also I'm not sure what use the `For Each Wb2 In Application.Workbooks` has. – BigBen Feb 17 '22 at 19:55
  • Thanks for the help. Adding the .Activate before the InputBox seemed to do the trick. – akroeker Feb 17 '22 at 20:02
  • Could you explain what you are trying to do? In which workbook is the code located? Is `Workbooks("2021 Shipments.xlsx")` wb or wb2? Why are you selecting the worksheet and then activating another workbook? – VBasic2008 Feb 18 '22 at 00:32
  • 1
    I ***highly*** recommend reading, and applying, [How to avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – BruceWayne Feb 18 '22 at 04:06

0 Answers0