How to select Multiple Sheet at once VBA

[Debraj]

Hi Friends,


I am unable to select Multiple Sheets at once. I don't know, where I am missing something, but its just ruining my day


Main Problem is in ShtAry (Array Of Sheet). Its adding Sheets to array. I can check the same at Immediate window by

? ShtAry(78).Name

But Unable to select all the sheet's at once...


Please can someone go through the below coding and provide some knowledge in Array..


PS: Due to pressure of BOSS, I have solved the issue, but not satisfied with bypass method...

[pre]Sub PrintLabels(Filename As String)
Dim shtAry()
Application.ScreenUpdating = False
With ActiveWorkbook
lrints = .Sheets(1).Range("A" & .Sheets(1).Cells.Rows.Count).End(xlUp).Row
ThisWorkbook.Sheets("SL").Visible = True
For i = 2 To lrints
ThisWorkbook.Sheets("SL").Copy After:=ActiveWorkbook.Sheets(1)
'----------------------
'               Some useless filling in the Sheet..
'               You can assume Sheet("SL") as a template of Shipping Label
'               .Sheets("sl").Range("A14") = .Sheets(1).Range("T" & i)
'               .Sheets("sl").Range("C14") = .Sheets(1).Range("U" & i)
'               and Many more...
'----------------------

'              Adding New Sheet to Sheet Array
Order = .Sheets(1).Range("K" & i)
Sheets("sl").Name = Order
ReDim Preserve shtAry(i - 2)
shtAry(i - 2) = Order
Next i
'         Unable to select Multiple Sheet as I need to Print all Labels into a single PDF

'=====
Sheets(shtAry).Select
'=====

'----------------------
'         Currently using the below to overcome the issue.
'          For d = 2 To ActiveWorkbook.Worksheets.Count
'               ActiveWorkbook.Sheets(d).Select (False)
'          Next d
'----------------------
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ThisWorkbook.Path & "Vendor Order Sheet " & Format(Now(), "DDMMYY") & "" & Filename & " - Chandoo's Orders - " & _
Format(Now(), "DDMMYY") & ".pdf"

Application.DisplayAlerts = False
'=====
Sheets(shtAry).Delete
'=====
'----------------------
'              Currently using the below to overcome the issue.
'              ActiveWindow.SelectedSheets.Delete
'----------------------
Application.DisplayAlerts = True
End With
End Sub

[/pre]

Regards,

Deb

 

[anupamtiwari05]

Hi Deb,


Hope below hint will help to resolve your proble.


Sheets(shtAry()).Select


just tweak your code line "Sheets(shtAry).Select" in your macro to above, as I have checked, its working fine for me.


Thanks & Regards,

Anupam Tiwari

 

[anupamtiwari05]

Hi Deb,


Below is my tested code for your problem.

Sub SelectMultipleSheetsAtOnce()

Application.DisplayAlerts = False

Dim shtAry()

For d = 2 To ActiveWorkbook.Worksheets.Count

ReDim Preserve shtAry(d - 2)

Order = ActiveWorkbook.Sheets(d).Name

shtAry(d - 2) = Order

Next d

Sheets(shtAry()).Select

''Sheets(shtAry()).Delete

Application.DisplayAlerts = True

End Sub


Thanks & Regards,

Anupam Tiwari

 

[shrivallabha]

Hi Deb,


Your basic idea looks fine to me.

I tested code below and it works OK. Can you test it please.

[pre]

Sub Macro1()
Dim shtAry()
ReDim shtAry(Sheets.Count - 1)
For i = 1 To Sheets.Count
shtAry(i - 1) = Sheets(i).Name
Next i
Sheets(shtAry).Select
End Sub

[/pre]

 

[Hui]

How about simply:

ThisWorkbook.Sheets.Select

 

[Debraj]

Hi Guru's,


Thanks a lot for all valuable suggestion.


I just realize.. that single pixel (.) can F@#$ (Fill) your day by scratching your Head..


Please please please.. take care of (.) at the time of With.. End With

Specially when you are working with Multiple Workbook..


Problem solved with just a DOT

[pre]

'=====
.Sheets(shtAry).Select
'=====

[/pre]
Regards,

Deb

 

[Hui]

Had I been quicker I could have posted the shortest correct forum response ever as a single:


.


of course if anybody asked a question that required the range intersection operand, which is a space, that would be shorter but also very hard to read

 

[Debraj]

Hi Hui,


I am really sorry, if I have done something wrong..


I am really feeling hopeless, when after a long time, program is always halting at the same error line..

due to screen updating, I not even able to realize, which one is current active sheet.. and why I am unable to select multiple non-contiguous sheet at once..


The very first thing, came in my mind is NINJA..


As soon as I solve the issue, I just come back, and post the solution.. specially to myself..


I doesn't mean to check ability of someone.. I was really seeking for help.. Sorry to community also..


Next time, I will try harder.. and will elaborate briefly, If face / solve any problem




Regards,

Deb

 

[Hui]

Debraj


You've done nothing wrong, in fact quite the opposite!

You have at least answered your own question, which is great as it means your learning

You have also posted the answer here which should mean your potentially helping somebody else


I was just making a comment that had I been quicker I could have answered the question with a single "."

Which would have been the shortest correct answer ever posted on the Forums

 

[SirJB7]

@Hui

Hi!

The shortest wouldn't have been a blank anwser (not a null string as someone might allege that chr$(0) has length 1, which it'd be debatable), assuming that the user had answered his question in the same post? Surely oldchippy would agree

Regards!