sheets array

[mn12]

How can I define sheets array ?
I want to create an array of 3 sheets.
I did something like that:

Dim sheetsArray(1) As XlSheetType
Xl.Sheets(sheetsArray("Sheet1", "Sheet2", "Sheet3&quot).Select
Xl.Sheets("Sheet1&quot.Activate

but it really doesn't work.

could anyone help please?

 

[TonyJollans]

Hi mn12,

You're making it harder than it needs to be. I just recorded this, which should be what you need.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 18/05/2003 by Tony Jollans
'

'
    Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
    Sheets("Sheet1").Activate
End Sub

Enjoy,
Tony

 

[mn12]

Hi Tony,
first of all thank you for your reply.
I tried to do what you wrote, but I get this massage:

run-time error '9':
subscript out of range

what is the problem?

 

[TonyJollans]

Do you have sheets called "Sheet1", etc? Try recording a macro yourself.

Just in case you're not sure ..

1. To record a macro go to Tools > Macro > Record Macro
2. To select multiple sheets hold down <Ctrl> while selecting the sheet tabs at the bottom of the window.

Enjoy,
Tony

 

[mn12]

Hi Tony,

Thank you again.
I recorded this macro and got what you have wrote.
but when I try to write it in my vb program,I get the error
massage I have mentioned above.
this is what I have done:

Xl.Application.SheetsInNewWorkbook = 3
Xl.Workbooks.Add
Xl.Cells.Select

ActiveCell.FormulaR1C1 = &quot;aaaa&quot;
Sheets(&quot;sheet2&quot.Select
activeCell.FormulaR1C1 = &quot;bbbb&quot;
Sheets(&quot;sheet3&quot.Select
ActiveCell.FormulaR1C1 = &quot;cccc&quot;

Xl.Sheets(Array(&quot;Sheet1&quot;, &quot;Sheet2&quot;, &quot;Sheet3&quot).Select
Xl.Sheets(&quot;Sheet1&quot.Activate

Xl.ActiveWindow.SelectedSheets.PrintPreview

But I got that error(subscript out of range).
the vb editor points at &quot;Sheets(&quot;sheet2&quot.Select&quot;.

what is wrong here???

 

[TonyJollans]

Hi

Looking at the code you must be running from another application - hence the &quot;xl.&quot; qualification. But ...

... the lines ...

ActiveCell.FormulaR1C1 = "aaaa"
Sheets("sheet2").Select
activeCell.FormulaR1C1 = "bbbb"
Sheets("sheet3").Select
ActiveCell.FormulaR1C1 = "cccc"

... are not qualified with &quot;xl.&quot;. That's all I can see wrong with code.

Enjoy,
Tony

 

[SkipVought]

Hi,

Use

With Activeworkbook  
   .Sheets(1).Cells(1, 1).Value = "aaaa"
   .Sheets(2).Cells(1, 1).Value = "bbbb"
   .Sheets(3).Cells(1, 1).Value = "cccc"
   .Sheets(Array(1, 2, 3)).Select
....
End With

Hope this helps


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[mn12]

thank you, both tony and skip!
now it works, but I still have a little problem:
I use the &quot;printPreview&quot; command.
but I see 6 sheets instead of 3.
after each sheet I wrote in-there is one empty.
what do you think the problem is?

 

[TonyJollans]

What I guess you're seeing is two pages per sheet. Excel can't fit all it wants to on a single page. Either change the print area or the page margins or use fit to page (in Page Setup).

Enjoy,
Tony

 

[mn12]

tony,thank you again!
You helped me a lot...
now it works perfectly!