Array Macro

dennisli

Well-known Member
Joined
Feb 20, 2004
Messages
1,070
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
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

dennisli

Well-known Member
Joined
Feb 20, 2004
Messages
1,070
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

Legend
Joined
Sep 3, 2002
Messages
10,473
You're missing the 1st line -- the Sub SubNameHere() one. Very important to have that!
 

dennisli

Well-known Member
Joined
Feb 20, 2004
Messages
1,070

ADVERTISEMENT

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

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
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

Well-known Member
Joined
Feb 20, 2004
Messages
1,070

ADVERTISEMENT

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

Legend
Joined
Feb 17, 2002
Messages
9,700
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

Well-known Member
Joined
Feb 20, 2004
Messages
1,070
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

Well-known Member
Joined
Feb 20, 2004
Messages
1,070
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,962
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top