Excel XP Query

  Desert Andy 17:47 24 May 2004
Locked

I'm trying to write code in Visual Basic within Excel XP to select certain worksheets, so that I can select print preview. All the names of the sheets are in variables, however, I can't select all the worksheets since there are some I do not want to print. I was hoping to make one variable, which contained all the worksheet names I wanted, and select them using an array; however, I don't seem to be able to do this.
Any ideas.

Thanks

(and probably to VoG!)

  VoG II 18:04 24 May 2004

Option Base 1

Sub test()

Dim ShtName

Dim i As Integer

ShtName = Array("Model Inputs", "Emissions", "PlotSheet")

For i = 1 To UBound(ShtName)

Sheets(ShtName(i)).PrintPreview

Next i

End Sub

  VoG II 20:25 24 May 2004

Via e-mail

Thanks for your help again, about the third time now. Very much appreciated. Unfortunately, I don't know what the worksheet names are until the user has input the names of people at work. The names of the people at work are stored in the array Workname(Numberofpeopleatwork). I was hoping to set up a loop to make a variable to input into the array ie allworknames=workname(1) for iloop = 2 to numberofpeopleatwork allworknames=allworknames & ", " & workname(iloop) WorkSheets(Array(Allworknames)).Select However, this code does not work, any other suggestions. Thanks again for your time Andy

================================

First, please respond by adding a response in the Add a new response box at the bottom. The e-mail facility is for private correspondence between forum users. Plus my e-mail is playing up at the moment and it was "lucky" that I managed to get this one.

What actually determines whether you want to PrintPreview a particular sheet? Is it just whether their names are in the array Workname. And you only want to print the sheets whose name appears in that array?

VoG's tip of the day: try at all costs to avoid using .Select (applied to anything). Using .Select will really slow your code down. If you need to get access to an object use .Activate or, preferably, just apply the method to the object directly, as in the code I posted above.

  VoG II 22:18 25 May 2004

The way I would handle this is as follows

Declare a public dynamic array:

Option Base 1

Public Titles()

Then when users log on or whatever they do

ReDim Preserve Titles(1 to Ubound(Titles)+1)

Titles(Ubound(Titles)) = the_new_name

and so on. Then proceed as above.

  Desert Andy 18:45 26 May 2004

Thanks for all your help, you have given me some good ideas. Sorry for the late responsse, been a terrible week at work.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5T review: Hands-on

Illustrator Andrés Lozano on his improv line work, brazen use of colours & hand sketching

iPhone X review

Comment envoyer gratuitement des gros fichiers ?