Calling an inactive sheet...

[RJH]

This may be a dumb questions but here goes:
If I have 2 sheets open, is there a way to refer to the inactive sheet
without using it's name?
The problem I'm having is that this file is run locally by many people and
everybody wants to rename their file. If I use the original file name in
the macro and the file gets renamed, the macro fails.

If ActiveSheet.Name = Workbooks("XXX.xls").Sheets(1).Name Then
ActiveWorkbook.Close 'close doc sheet if date already exists
Else: 'insert sheet if new date
Sheets(1).Move Before:=Workbooks("XXX.xls").Sheets(1)
End If

Thanks in advance.

RJH

 

[NickHK]

If you have no way to know the filename beforehand, you will have to ask the
user to select it. They use that name:

Dim SourceWB As Workbook
Dim RetVal as Variant
Retval=Application.GetOpenFilename()
If RetVal=False then exit sub
set SourceWB=Workbooks.open(retval)
'Now you can refer to the file as
SourceWB.Worksheets("Sheet1").....

You mentions "sheets" and "files". I guess you mean "workbook" for both of
these.
In Excel, "sheet" normally refers to a worksheet.
In passing, you can use the .CodeName of a worksheet (which the user cannot
change) instead of the .Name (which they may be can).

NickHK