• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Creating an object of a subset of worksheets [SOLVED]

Jack

Member
Hi all


I have a large Workbook with about 60 worksheets. But of these I have only 20 or so that have to be updated with new data.


Can I or how can I create an object of these 20 worksheets which I need to define by tab name as the index numbers for these sheets are not in one group. I know I could change the names to say end in "Data" and then use Right(wsname,4) = "data" but this is not my workbook and ideally I would prefer to leave it as is. I want to refer to this object in a For each loop ws i.e. for each ws in ...

After a fair bit of googling I am not finding much on this topic. I tried an approach a few days back that didn't work - it was something like...

Dim DataWS as variant

Dim ws as worksheet

DataWS = ({"Sheet1"},{"sheet15"}, {and so on})


for each ws in DataWS

my code

next ws


Was I on the right track?


Thanks

John
 
Hi John ,


You can declare DataWS as a variant , and define it using a statement such as :

[pre]
Code:
Dim DataWS as Variant
DataWS = Array("Sheet1" , "Sheet15" , .... )
Since each of these elements is a string , you cannot use a statement such as :

For each ws in DataWS
[/pre]
where ws has been declared as Worksheet.


Narayan
 
OK thanks Narayank991 wow you are fast - Thanks I will try that syntax and play with it on getting to see the names as worksheets and let you know if it works.


Cheers

John
 
Well this works if it helps anyone....

[pre]
Code:
Sub testwssubset()
Dim vWSsubset As Variant
Dim vws As Variant
Dim ws As Worksheet
vWSsubset = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")
For Each vws In vWSsubset
For Each ws In ThisWorkbook.Worksheets
If Worksheets(vws).Name = ws.Name Then
MsgBox "Found one! " & ws.Name & ":)"
Else
End If
Next ws
Next vws
End Sub
[/pre]
thanks again Narayank991 you gave me the idea of using an array of strings to do this.


regards

John
 
Back
Top