Need a VBA Code for selecting multiple sheets

ddubnansky · Oct 21, 2003

I currently have a macro that breaks my data in to 20 tabs(worksheets). As it breaks it down it formats the printing of each page separately (meaning that the formatting is in the loop). Since each tab is formatted exactly the same as the others, I would like to break out the formatting and have the macro, when finished, select the third worksheet through the last worksheet and format them together. This my computer from running in to loss of memory issues and me from losing my mind.

PLEASE NOTE: I cannot specifically state the sheet names within the code (i.e. worksheets(array("Sheet1", "Sheet2", "Sheet3")).Select) because other coworkers will be using this macro for their work. Everyone will have a different number of worksheets based upon the information they are pulling. However, the 3rd sheet will always be the starting point for the formatting.

Thanks for taking the time to read my dilemma and I look forward to hearing your replies.

Andrew Poulsom · Oct 21, 2003

Try this:

Code:

Sub Test()
    Dim x As Integer
    ThisWorkbook.Worksheets(3).Select
    For x = 4 To ThisWorkbook.Worksheets.Count
        Worksheets(x).Select (False)
    Next x
End Sub

It sets the optional Replace argument to False so that the selection is extended.

ddubnansky · Oct 21, 2003

Very nice... It selects all the sheets I need but it only formats one page... Could you have a look at my code and tell me why it's not formatting all the sheets at once. Here is my code (well, your code with a portion of mine attached).

One More Question - Do you happen to know of or would recommend any VBA classes? Right now, I'm learning all my stuff from reading things posted on this site and from several books (Excel 2002 Power Programming with VBA and Excel VBA 2002). I would love to take a class. Thanks.

Sub printformat()

Dim shCount As Integer

ThisWorkbook.Worksheets(3).Select
For shCount = 4 To ThisWorkbook.Worksheets.Count
Worksheets(shCount).Select (False)
Next shCount
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = "&8&Z&F"
.CenterFooter = ""
.RightFooter = "&""Times New Roman,Bold""&T" & Chr(10) & "&D" & Chr(10) & "&26&A"
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0)
.CenterHorizontally = True
.Orientation = xlLandscape
.Zoom = 60
End With

End Sub

ALSO - how do you post items on this site INDENTED?

Drdave1958 · Oct 21, 2003

I know this will do what you want, but doesn't address the out of memory errors.........

code:
Sub FormatMySheets()
MyTotal = Sheets.Count
For x = 3 To MyTotal
Sheets(x).Select
'Format your sheets here
Next x
End Sub

-Dave-

Joe4 · Oct 21, 2003

If you highlight your code and click on the "Code" box (under the Subject line when replying), it will maintain all spaces in your code to give the indented look.

ddubnansky · Oct 21, 2003

DrDave,

Thanks for the help but that will put me in another loop. That's what I'm trying to stay away from. If I use the loop, it will format each sheet individually, which will take more time and memory. My goal is to select all the sheets and format the sheets at once. Andrews code works perfectly for selecting the sheets but there's an error in my formatting code. It will only format the active page. I believe the line that is incorrect is the "With ActiveSheet.PageSetup" line. I think it shouldn't have the "ActiveSheet" line but I can't figure out what to replace it with.

Thanks again for your suggestion.

-----------------------------------------------------------------

JMISKEY,

Thanks for the the help with the posting info.

Andrew Poulsom · Oct 21, 2003

I don't know why, but it works when you do it manually, but not in code. I recorded a macro while doing it manually, but playing it back only affects the active sheet.

I think you will need a loop, as shown here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;213257&Product=xlw2K

You should also eliminate unnecessary settings from the PageSetup call, as explained here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;213654&Product=xlw2K

ddubnansky · Oct 21, 2003

Andrew,

I figured the same thing. When you do it manually, Excel must loop the process. So, I guess the only way you can do it is through a loop. Oh well, nothing's perfect.

Thanks again to all that tried to help. The assistance I received at least will help me in the future. So nothing will go to waste.

Drdave1958 · Oct 23, 2003

ddubnansky,
I found this on another board.......

Code:

Sub FormatMySheets()
Dim x As Integer
With Sheets("Sheet3").PageSetup
        .LeftFooter = "Dave's Format Test "
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0)
        .RightMargin = Application.InchesToPoints(0)
        .TopMargin = Application.InchesToPoints(0)
        .BottomMargin = Application.InchesToPoints(0)
        .HeaderMargin = Application.InchesToPoints(0)
        .FooterMargin = Application.InchesToPoints(0)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintSheetEnd
        .PrintQuality = 600
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
End With
    ThisWorkbook.Worksheets(3).Select 'Select sheet 3
    For x = 4 To ThisWorkbook.Worksheets.Count
        Worksheets(x).Select (False) 'Select the rest of the sheets in the workbook
    Next x
Sheets("sheet3").Activate
SendKeys "{enter}"
Application.Dialogs(xlDialogPageSetup).Show
Sheets("Sheet3").Select  'ungroups the sheets
End Sub

The way it was explained, it formats the first sheet, then selects the other sheets, then "transmits" the format of the 1st sheet to the remaining sheets. You will have to test it to see if it's faster, but it seemed so in my test.

-Dave-

Lunar78 · Aug 5, 2016

I had a similar situation and used the following code, which worked great at first and recently started only saving the first sheet in the workbook. Any ideas why it stopped working?

Sub ReportToPDF()

Dim i As Integer
Dim sTotal As Integer
Dim mPath As String

mPath = ActiveWorkbook.Path
sTotal = Sheets.Count() - 1

For i = 3 To sTotal
Sheets(i).Select Replace:=False

Next i

With Selection
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=mPath & "\" & "VARIANCE" & Format(Date, "MMMMYYYY") & ".pdf"
End With

Sheets("Data").Activate

End Sub

Need a VBA Code for selecting multiple sheets

ddubnansky

Board Regular

Excel Facts

Andrew Poulsom

MrExcel MVP

ddubnansky

Board Regular

Drdave1958

Board Regular

Joe4

MrExcel MVP, Junior Admin

ddubnansky

Board Regular

Andrew Poulsom

MrExcel MVP

ddubnansky

Board Regular

Drdave1958

Board Regular

Lunar78

New Member

Similar threads

Forum statistics

Share this page

Need a VBA Code for selecting multiple sheets

Board Regular

Excel Facts

MrExcel MVP

Board Regular

Board Regular

MrExcel MVP, Junior Admin

Board Regular

MrExcel MVP

Board Regular

Board Regular

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock