VBA- Activate Sheet from another Workbook based on Cell value

[mariag]

Hi everyone.

I'm having a hard time coding this one.

My objective is to select the sheet name based on a cell value from another work book.

Say, in workbook1, range(a1)= d-4

Then i want sheet(d-4) opened in workbook2.

The catch is, i want to dynamically do it.

That is, once sheet(d-4) is opened i would go back to workbook1 then open again the sheet in workbook2 based on workbook1's range(a2).

My code goes like this:


Dim a as integer
Dim detail as string

Do until isempty(cells(a,1))
Detail=cells(a,1).value

Workbooks(workbook2).sheet(detail).range("a1").select

a=a+1


Loop

Ive tried debugging it and seems hopeless.
Please help. Thanks in advance

 

[Fluff]

What do you mean by

i want sheet(d-4) opened in workbook2

Do you want to make it visible, rather than hidden?
Or do you want to activate it? If you want to activate it then as soon as you do that you will then activate the next sheet in the list, thereby de-activating the previous sheet.

 

[mariag]

What do you mean by
Do you want to make it visible, rather than hidden?
Or do you want to activate it? If you want to activate it then as soon as you do that you will then activate the next sheet in the list, thereby de-activating the previous sheet.

I want it to be hidden.

I also get an error with
Sheets("detail")

 

[Fluff]

Try

Sub HideSht()
Dim a As Integer
Dim detail As String
Dim Wbk As Workbook

Set Wbk = Workbooks("[COLOR=#ff0000]book2.xlsm[/COLOR]")
With Workbooks("[COLOR=#ff0000]Book1.xlsm[/COLOR]").Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
   a = 1
   Do Until IsEmpty(.Cells(a, 1))
      detail = Cells(a, 1).Value
      Wbk.Sheets(detail).Visible = False
      a = a + 1
   Loop
End With
End Sub

Change values in red to suit