set workbook depending on the workbook that is open

Question

I have 2 workbooks that I run macros on "Air.xlsx" and "Ocean.xlsx", they are basically the same but for different purpose. I want to check if one of them is open , and set one of them as Wsht . I can't set them as set Wsht = activesheet because the macro starts from a different sheet.

Set Wsht = Workbooks("Air").ActiveSheet Set Wsht = Workbooks("Ocean").ActiveSheet

an error would occur on this because i would only have one of them open.

I was suggested using below method, but i don't think it's an efficient way to do it

 For Each wb In Workbooks If wb.Name = "Air.xlsx" Then Set PASsht = Workbooks("Air").ActiveSheet End If Next

Is there a way to check if Air or Ocean sheet is open and set one as Wsht?

Thanks

There is only one ActiveSheet. A Workbook does not have a property ActiveSheet. — GSerg
– GSerg, Commented Jun 15, 2020 at 6:26

Darren Bartrup-Cook · Accepted Answer · 2020-06-15 10:08:21Z

You can specify a sheet on whichever workbook is open. Try the code below.

Sub Test() Dim wrksht As Worksheet If WorkbookIsOpen("Air.xslx") Then Set wrksht = Workbooks("Air.xlsx").Worksheets("Sheet1") ElseIf WorkbookIsOpen("Ocean.xlsx") Then Set wrksht = Workbooks("Ocean.xlsx").Worksheets("Sheet1") Else 'Neither book is open, throw an error or something. End If End Sub Public Function WorkbookIsOpen(FileName As String) As Boolean Dim TestBk As Workbook 'Trying to set a reference to a closed workbook will 'throw an error - Err.Number = 0 will return TRUE or FALSE. On Error Resume Next Set TestBk = Workbooks(FileName) WorkbookIsOpen = (Err.Number = 0) On Error GoTo 0 End Function

Collectives™ on Stack Overflow

set workbook depending on the workbook that is open

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related