VBA- Activate Sheet from another Workbook based on Cell value

mariag

New Member
Joined
Nov 4, 2016
Messages
9
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,966
Office Version
365
Platform
Windows
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

New Member
Joined
Nov 4, 2016
Messages
9
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,966
Office Version
365
Platform
Windows
Try
Code:
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
 

Forum statistics

Threads
1,086,088
Messages
5,387,733
Members
402,075
Latest member
COwen

Some videos you may like

This Week's Hot Topics

Top