Referring to worksheets 1

[sjh]

Hi,

I have an Excel application which opens another excel app. Here's a snippet of the code. How can I refer to these two open worksheets now? Is it possible to give names to the worksheets, so I can distinguish them?

Private Sub btnOpenPreviousVersion_Click()
        Workbooks.Open "C:\", ReadOnly:=True

Thanks for your valuable help!
SJH

 

[TonyJollans]

Hi sjh,

It rather depends on exactly what you are doing but setting references is probably the easiest ..

[blue]Private Sub btnOpenPreviousVersion_Click()
        Dim WB1 as Workbook, WB2 as workbook
        Set WB1 = activeworkbook
        Workbooks.Open "C:\", ReadOnly:=True
        Set WB2 = activeworkbook[/blue]

You can now act as you will on wb1 and/or wb2

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[sjh]

Thanks Tony for your tip!

I have another question. Now that I know how to refer to the workbooks, I have trouble with worksheets.

After I have opened the two workbooks, I want to refer to the worksheets in each workbooks. They both have a worksheet whose code name is wBudget (I don't want to use the tab name because they could be different). I tried doing the following but it doesn't work.

WB1.Worksheets(WB1.wBudget.Name).Range("A:A").Copy
WB2.Worksheets(WB2.wBudget.Name).Range("A1").Paste

Any suggestions would be great!

Thank you,
SJH

 

[TonyJollans]

Hi sjh,

I don't think you can reference sheets by codename outside of code in the workbook they belong to.

So I think you'll have to write your own function along these lines to get the reference you want ..

[blue]Function GetSheetByCodeName(wb As Workbook, CodeName As String) As Worksheet
Dim ws As Worksheet
For Each ws In wb.Worksheets
    If ws.CodeName = CodeName Then
        Set GetSheetByCodeName = ws
        Exit For
    End If
Next
Set ws = Nothing
End Function[/blue]

Which you can then call like this ..

[blue]Set ws1 = GetSheetByCodeName(wb1, "wBudget")[/blue]

Not ideal, and I'm happy to be proved wrong, but it should enable you to do what you want

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.