|
Renaming Spreadsheets
I have a couple of problems for which any help would be greatly appreciated.
I have several workbooks where I have to amend the page number on each spreadsheet. The page numbering does not necessarilly start on the first spreadsheet so my first thought was to have an Input Box in which the start position could be entered and for this to loop through the remaining sheets. My first problem is that I can't get it to loop. Secondly, I wish to use the &tab as a page footer which I have managed so long as I enter the array of sheets to be formatted. Is there a way I can use the worksheets.count from earlier in my code to fill this array or some other way? I have pasted my code below. Many thanks. |
Renaming Spreadsheets
Opps! Forgot to paste my code: -
Dim i As Integer Dim Response As Variant Response = Application.InputBox( _ Prompt:="Enter the position of the spreadsheet at which you wish the numbering to start", Type:=1) If Response = False Then Exit Sub For i = Reponse To Worksheets.Count Sheets(i).Name = "Page " & Response - 1 Next 'Correct Page Numbering Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11")).Select Sheets("1").Activate For Each wks In ActiveWindow.SelectedSheets With wks.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "&""Arial Black,Regular""&8&A" .RightFooter = "&""Arial Black,Regular""&8WM PERFORMANCE SERVICES" .LeftMargin = Application.InchesToPoints(0.590551181102362) .RightMargin = Application.InchesToPoints(0.590551181102362) .TopMargin = Application.InchesToPoints(0.393700787401575) .BottomMargin = Application.InchesToPoints(0.393700787401575) .HeaderMargin = Application.InchesToPoints(0.47244094488189) .FooterMargin = Application.InchesToPoints(0.31496062992126) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = 2 .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With Next wks "Alpur" wrote: I have a couple of problems for which any help would be greatly appreciated. I have several workbooks where I have to amend the page number on each spreadsheet. The page numbering does not necessarilly start on the first spreadsheet so my first thought was to have an Input Box in which the start position could be entered and for this to loop through the remaining sheets. My first problem is that I can't get it to loop. Secondly, I wish to use the &tab as a page footer which I have managed so long as I enter the array of sheets to be formatted. Is there a way I can use the worksheets.count from earlier in my code to fill this array or some other way? I have pasted my code below. Many thanks. |
Renaming Spreadsheets
|
Renaming Spreadsheets
I looked again and tested this time but IF you did EXACTLY as I did the
input box should not have been twice but this will be much better. Page setup is inherently SLOW. I can only suggest that you not change what doesn't need changing Sub dopagesetup() numsheets = InputBox("Enter starting sheet") For i = numsheets To Worksheets.Count - numsheets - 1 With Sheets("sheet" & i) '.PageSetup MsgBox .Range("a1").Value 'etc End With Next i End Sub -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... try something like this for i=inputbox("Enter starting sheet") to worksheets.count with sheets("page"&i).pagesetup etc end with next i -- Don Guillett SalesAid Software "Alpur" wrote in message ... Opps! Forgot to paste my code: - Dim i As Integer Dim Response As Variant Response = Application.InputBox( _ Prompt:="Enter the position of the spreadsheet at which you wish the numbering to start", Type:=1) If Response = False Then Exit Sub For i = Reponse To Worksheets.Count Sheets(i).Name = "Page " & Response - 1 Next 'Correct Page Numbering Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11")).Select Sheets("1").Activate For Each wks In ActiveWindow.SelectedSheets With wks.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "&""Arial Black,Regular""&8&A" .RightFooter = "&""Arial Black,Regular""&8WM PERFORMANCE SERVICES" .LeftMargin = Application.InchesToPoints(0.590551181102362) .RightMargin = Application.InchesToPoints(0.590551181102362) .TopMargin = Application.InchesToPoints(0.393700787401575) .BottomMargin = Application.InchesToPoints(0.393700787401575) .HeaderMargin = Application.InchesToPoints(0.47244094488189) .FooterMargin = Application.InchesToPoints(0.31496062992126) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = 2 .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With Next wks "Alpur" wrote: I have a couple of problems for which any help would be greatly appreciated. I have several workbooks where I have to amend the page number on each spreadsheet. The page numbering does not necessarilly start on the first spreadsheet so my first thought was to have an Input Box in which the start position could be entered and for this to loop through the remaining sheets. My first problem is that I can't get it to loop. Secondly, I wish to use the &tab as a page footer which I have managed so long as I enter the array of sheets to be formatted. Is there a way I can use the worksheets.count from earlier in my code to fill this array or some other way? I have pasted my code below. Many thanks. |
Renaming Spreadsheets
Many thanks again, Don, it works fine now.
Regards, Al. "Don Guillett" wrote: I looked again and tested this time but IF you did EXACTLY as I did the input box should not have been twice but this will be much better. Page setup is inherently SLOW. I can only suggest that you not change what doesn't need changing Sub dopagesetup() numsheets = InputBox("Enter starting sheet") For i = numsheets To Worksheets.Count - numsheets - 1 With Sheets("sheet" & i) '.PageSetup MsgBox .Range("a1").Value 'etc End With Next i End Sub -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... try something like this for i=inputbox("Enter starting sheet") to worksheets.count with sheets("page"&i).pagesetup etc end with next i -- Don Guillett SalesAid Software "Alpur" wrote in message ... Opps! Forgot to paste my code: - Dim i As Integer Dim Response As Variant Response = Application.InputBox( _ Prompt:="Enter the position of the spreadsheet at which you wish the numbering to start", Type:=1) If Response = False Then Exit Sub For i = Reponse To Worksheets.Count Sheets(i).Name = "Page " & Response - 1 Next 'Correct Page Numbering Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11")).Select Sheets("1").Activate For Each wks In ActiveWindow.SelectedSheets With wks.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "&""Arial Black,Regular""&8&A" .RightFooter = "&""Arial Black,Regular""&8WM PERFORMANCE SERVICES" .LeftMargin = Application.InchesToPoints(0.590551181102362) .RightMargin = Application.InchesToPoints(0.590551181102362) .TopMargin = Application.InchesToPoints(0.393700787401575) .BottomMargin = Application.InchesToPoints(0.393700787401575) .HeaderMargin = Application.InchesToPoints(0.47244094488189) .FooterMargin = Application.InchesToPoints(0.31496062992126) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = 2 .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With Next wks "Alpur" wrote: I have a couple of problems for which any help would be greatly appreciated. I have several workbooks where I have to amend the page number on each spreadsheet. The page numbering does not necessarilly start on the first spreadsheet so my first thought was to have an Input Box in which the start position could be entered and for this to loop through the remaining sheets. My first problem is that I can't get it to loop. Secondly, I wish to use the &tab as a page footer which I have managed so long as I enter the array of sheets to be formatted. Is there a way I can use the worksheets.count from earlier in my code to fill this array or some other way? I have pasted my code below. Many thanks. |
Renaming Spreadsheets
glad it helped
-- Don Guillett SalesAid Software "Alpur" wrote in message ... Many thanks again, Don, it works fine now. Regards, Al. "Don Guillett" wrote: I looked again and tested this time but IF you did EXACTLY as I did the input box should not have been twice but this will be much better. Page setup is inherently SLOW. I can only suggest that you not change what doesn't need changing Sub dopagesetup() numsheets = InputBox("Enter starting sheet") For i = numsheets To Worksheets.Count - numsheets - 1 With Sheets("sheet" & i) '.PageSetup MsgBox .Range("a1").Value 'etc End With Next i End Sub -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... try something like this for i=inputbox("Enter starting sheet") to worksheets.count with sheets("page"&i).pagesetup etc end with next i -- Don Guillett SalesAid Software "Alpur" wrote in message ... Opps! Forgot to paste my code: - Dim i As Integer Dim Response As Variant Response = Application.InputBox( _ Prompt:="Enter the position of the spreadsheet at which you wish the numbering to start", Type:=1) If Response = False Then Exit Sub For i = Reponse To Worksheets.Count Sheets(i).Name = "Page " & Response - 1 Next 'Correct Page Numbering Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11")).Select Sheets("1").Activate For Each wks In ActiveWindow.SelectedSheets With wks.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "&""Arial Black,Regular""&8&A" .RightFooter = "&""Arial Black,Regular""&8WM PERFORMANCE SERVICES" .LeftMargin = Application.InchesToPoints(0.590551181102362) .RightMargin = Application.InchesToPoints(0.590551181102362) .TopMargin = Application.InchesToPoints(0.393700787401575) .BottomMargin = Application.InchesToPoints(0.393700787401575) .HeaderMargin = Application.InchesToPoints(0.47244094488189) .FooterMargin = Application.InchesToPoints(0.31496062992126) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = 2 .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With Next wks "Alpur" wrote: I have a couple of problems for which any help would be greatly appreciated. I have several workbooks where I have to amend the page number on each spreadsheet. The page numbering does not necessarilly start on the first spreadsheet so my first thought was to have an Input Box in which the start position could be entered and for this to loop through the remaining sheets. My first problem is that I can't get it to loop. Secondly, I wish to use the &tab as a page footer which I have managed so long as I enter the array of sheets to be formatted. Is there a way I can use the worksheets.count from earlier in my code to fill this array or some other way? I have pasted my code below. Many thanks. |
| All times are GMT +1. The time now is 12:51 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com