Sleeper: Toggle between two worksheets

[martha555]

I am creating a worksheet in excel (main.xls). I have written a macro in this worksheet to open another worksheet(file.xls). Here I enter some values. These values are then passed to the main worksheet and are subsequently displayed in a few cells there. My problem is, that every time I open the main worksheet a new number is appended to the name, i.e., main1.xls, main2.xls, main3.xls and so on. So when I pass values from (file.xls) to the main worksheet, I need to activate that file first, for which I need the name of that file. however I can't hardcode the filename as I do not know the it's number. How do I go about it.

[Killian]

Here's a quick code example that should help

'declare two workbook object variables
Dim wbFile As Workbook
Dim wbMain As Workbook
'set references to your workbooks
    Set wbFile = ThisWorkbook
    Set wbMain = Workbooks.Add 'a new workbook
'now you can manage the workbooks by referring to the objects
    wbMain.Activate
    wbMain.Worksheets(1).Cells(1, 1).Value = 500
wbFile.Activate
    wbFile.Worksheets(1).Cells(4, 4).Value = 99
'when your done, you should release the references to the objects
    Set wbFile = Nothing
    Set wbMain = Nothing

[martha555]

hi,
thanx for the response. However, the above code adds a new workbook. I do not want to add a new workbook, I just want to activate the existing open workbook, whose name I know, but the document number always changes. The activate command is given from the second workbook, hence I can't hardcode the main workbook name.

[Steiner]

Maybe this small piece of code might help, it loops through all workbooks and activates the first one that starts with the given String:

Option Explicit 

Sub Act()
   Dim Wb As Workbook
'Change this to the Namepart you know
   Const sNamePart As String = "Mappe"
For Each Wb In Workbooks
      ' If workbookname starts with sNamepart
      If InStr(Wb.Name, sNamePart) = 1 Then
         Wb.Activate
         Exit For
      End If
   Next Wb
End Sub

Daniel

[martha555]

thanks, will get back after trying