Array Macro

[dennisli]

I tried to use Array to include from the first sheet to the last sheet in one workbook. Is something wrong with this code:
Sheets(Array(first sheet, last sheet)).Select

 

[just_jon]

If you want 'em all --

Sheets.Select

 

[dennisli]

Thanks, This is all the code.


Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Dim cSheets As Integer
Dim sSheets() As String
Dim i As Integer


' CoverageDevelopReplace Macro
' Macro recorded 12/2/2003 by User

'CWI IS ACTIVATED
Windows("Test.xls").Activate
Set wb = ActiveWorkbook
cSheets = wb.Sheets.Count
ReDim sSheets(1 To cSheets)

For i = 1 To cSheets
Sheets(i).Select

Sheets(i).PageSetup.LeftFooter = "Page &P of &A"
Sheets(i).PageSetup.RightFooter = "Page &P of &F"


Next

Sheets(Array(Sheets(1), Sheets(cSheets))).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

End Sub

 

[just_jon]

You're missing the 1st line -- the Sub SubNameHere() one. Very important to have that!

 

[dennisli]

Sorry, I have the sub name code. but it still does not work.

 

[NateO]

An array's not necessary, try the following:

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> sdfdsmfkdsjl()
<SPAN style="color:darkblue">Dim</SPAN> wb <SPAN style="color:darkblue">As</SPAN> Workbook, ws <SPAN style="color:darkblue">As</SPAN> Worksheet
<SPAN style="color:darkblue">Set</SPAN> wb = Workbooks("Test.xls")
<SPAN style="color:darkblue">For</SPAN> <SPAN style="color:darkblue">Each</SPAN> ws <SPAN style="color:darkblue">In</SPAN> wb.Worksheets
    <SPAN style="color:darkblue">With</SPAN> ws
        .PageSetup.LeftFooter = "Page &P of &A"
        .PageSetup.RightFooter = "Page &P of &F"
        .PrintOut copies:=1
    <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">With</SPAN>
<SPAN style="color:darkblue">Next</SPAN>
<SPAN style="color:darkblue">Set</SPAN> wb = <SPAN style="color:darkblue">Nothing</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN>
</FONT>

Might as well take care of all your business in the loop eh.

 

[dennisli]

Hi, Nate
Thank you very much.

I test your Macro code and still find some problems. My main purpose is to show at the bottom of each page, the Left Footer shows like Page 1 of Sheet3, Sheet 3 is the third sheet of the workbook named Test.

The Right Footer shows like Page 10 of Test. Test is the workbook name. Page 10 is sequenced from the first page of Sheet1 to the first page of Sheet 3.

In your code processing result, the Left Footer is ok. But the Right Footer shows like the first Page 1 of Test, not the Page 10 of Test.

Is possible for you to get it?

Best regards.
Dennis

 

[NateO]

Not of the top of my head, the left footer gets bolloxed up with the following:

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> sdfdsmfkdsjl2()
<SPAN style="color:darkblue">Dim</SPAN> wb <SPAN style="color:darkblue">As</SPAN> Workbook, ws <SPAN style="color:darkblue">As</SPAN> Worksheet
<SPAN style="color:darkblue">Set</SPAN> wb = Workbooks("Tab.xls")
Application.ScreenUpdating = <SPAN style="color:darkblue">False</SPAN>
<SPAN style="color:darkblue">For</SPAN> <SPAN style="color:darkblue">Each</SPAN> ws <SPAN style="color:darkblue">In</SPAN> wb.Worksheets
    <SPAN style="color:darkblue">With</SPAN> ws
        .PageSetup.LeftFooter = "Page &P of &A"
        .PageSetup.RightFooter = "Page &P of &F"
    <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">With</SPAN>
<SPAN style="color:darkblue">Next</SPAN>
<SPAN style="color:darkblue">With</SPAN> wb
    Application.Goto .Sheets(1).[a1]
    .Sheets.<SPAN style="color:darkblue">Select</SPAN> <SPAN style="color:darkblue">False</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">With</SPAN>
ActiveWindow.SelectedSheets.PrintOut
wb.Sheets(1).<SPAN style="color:darkblue">Select</SPAN> <SPAN style="color:darkblue">True</SPAN>
<SPAN style="color:darkblue">Set</SPAN> wb = <SPAN style="color:darkblue">Nothing</SPAN>
Application.ScreenUpdating = <SPAN style="color:darkblue">True</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

 

[dennisli]

It’s perfect, Nate,
You are really so dedicated to help others Macro layman, like me.
Hopefully in the near future I can still get your help.
Dennis

 

[dennisli]

Hi, Nati,
The problem is the page number count of LeftFooter is cumulative through the whole book, not from the first page to the last page of the current sheet.
Is it possible for you to fix it again?
Thank you very much.
Dennis