Excel XP Query

  Desert Andy 17:47 24 May 04
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 04

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 04

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 04

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 04

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.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

The Pantone Colour of the Year 2017 is Green

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…