PDA

View Full Version : Sleeper: Toggle between two worksheets



martha555
07-19-2005, 10:58 PM
:hi: 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.:banghead:

Killian
07-20-2005, 01:47 AM
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
07-21-2005, 10:51 PM
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
07-22-2005, 12:32 AM
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
07-22-2005, 12:43 AM
thanks, will get back after trying