Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activating sheets with same name in multiple workbooks
I have about 20 workbooks and within these workbooks are about 35 sheets with
identical sheet names. I'd like to review the sheets with the same name rapidly without having to click through the workbooks. Any suggestions? Thanks in advance, Barb Reinhardt |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activating sheets with same name in multiple workbooks
Impossible, the name is unique within a workbook. 35 into 20 doesn't go.
What do you men by review? -- HTH Bob Phillips "Barb Reinhardt" wrote in message ... I have about 20 workbooks and within these workbooks are about 35 sheets with identical sheet names. I'd like to review the sheets with the same name rapidly without having to click through the workbooks. Any suggestions? Thanks in advance, Barb Reinhardt |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activating sheets with same name in multiple workbooks
Dim rng as Range, bk as Workbook
for each bk in Application.Workbooks bk.Activate worksheets("ABC").Activate On error resume Next set rng = Application.Inputbox("Click Cancel to continue", type:=8) On error goto 0 Next -- Regards, Tom Ogilvy "Barb Reinhardt" wrote in message ... I have about 20 workbooks and within these workbooks are about 35 sheets with identical sheet names. I'd like to review the sheets with the same name rapidly without having to click through the workbooks. Any suggestions? Thanks in advance, Barb Reinhardt |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activating sheets with same name in multiple workbooks
How do I identify the workbooks that I'm using and the specific sheet names?
As you can clearly see, I'm a novice at this. If you could direct me to something I could read on this, I'd greatly appreciate it. Regards, Barb Reinhardt "Tom Ogilvy" wrote: Dim rng as Range, bk as Workbook for each bk in Application.Workbooks bk.Activate worksheets("ABC").Activate On error resume Next set rng = Application.Inputbox("Click Cancel to continue", type:=8) On error goto 0 Next -- Regards, Tom Ogilvy "Barb Reinhardt" wrote in message ... I have about 20 workbooks and within these workbooks are about 35 sheets with identical sheet names. I'd like to review the sheets with the same name rapidly without having to click through the workbooks. Any suggestions? Thanks in advance, Barb Reinhardt |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activating sheets with same name in multiple workbooks
Well, you left your description vague. If you want to loop through 20
workbooks for 35 sheets - how you would identify them is pretty much up to you. for example, if you had all the workbooks and only those workbooks in a single directory (C:\Mypath for example) and they were not open in Excel Sub ABCD() Dim v(1 to 20) as String Dim v1(1 to 35) Dim i as Long, sPath as String, sName as String Dim sh as worksheet, j as Long sPath = "C:\Mypath\" sName = Dir(sPath & "*.xls") i = 0 do while sName < "" i = i + 1 set bk = Workbooks.Open(sPath & sName) v(i) = bk.Name sName = Dir() Loop i = 0 for each sh in workbooks(v(1)).Worksheets i = i + 1 v1(i) = sh.Name Next sh for i = 1 to 35 for j = 1 to 20 set bk = Workbooks(v(j)) bk.Activate worksheets(v1(i)).Activate On error resume Next set rng = Application.Inputbox("Click Cancel to continue", type:=8) On error goto 0 Next j Next i for i = 1 to 20 workbooks(v(i)).Close SaveChanges:=False Next End Sub -- Regards, Tom Ogilvy "Barb Reinhardt" wrote in message ... How do I identify the workbooks that I'm using and the specific sheet names? As you can clearly see, I'm a novice at this. If you could direct me to something I could read on this, I'd greatly appreciate it. Regards, Barb Reinhardt "Tom Ogilvy" wrote: Dim rng as Range, bk as Workbook for each bk in Application.Workbooks bk.Activate worksheets("ABC").Activate On error resume Next set rng = Application.Inputbox("Click Cancel to continue", type:=8) On error goto 0 Next -- Regards, Tom Ogilvy "Barb Reinhardt" wrote in message ... I have about 20 workbooks and within these workbooks are about 35 sheets with identical sheet names. I'd like to review the sheets with the same name rapidly without having to click through the workbooks. Any suggestions? Thanks in advance, Barb Reinhardt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Activating Workbooks | Excel Discussion (Misc queries) | |||
activating workbooks | Excel Discussion (Misc queries) | |||
Copy multiple sheets to seperate workbooks | Excel Discussion (Misc queries) | |||
creating multiple sheets, then individualized workbooks for each sheet | Excel Discussion (Misc queries) | |||
activating workbooks | Excel Programming |