Excel 2010 :: Maintain Different Headers For Different Sheets When Printing
Aug 23, 2013
Excel 2010. I have a workbook that has multiple sheets where some have headers and others do not. Some sheets are static in that no user input is required and others are dynamic so the toal number of pages per worksheet may vary based on how much information the user inputs.
An example:
Sheet 1 is static and requires no input, it does not have a header or a page number. For compliance reasons this sheet must be printed on top of all the other sheets in this policy
Sheet 2 is dynamic. This sheet can range from 2 pages to 5 pages depending on user input. This sheet must have page numbers starting with 1 and it must have a header on page 2 through the last page, but no header on page 1.
I am currently using a worksheet_activate sub to input data into the headers and footers of sheet 2. This works very well and puts the data where I want it to go just by using the "Different First Page" option in the headers design tab.
The problem arises when I select both sheets to print. Excel now thinks Sheet1 is the first page, so the first page of sheet 2 now has a header and the page number beings at 2 instead of 1.
Where I can stop this from happening?
Current sub:
Code:
Private Sub Worksheet_Activate()
' If WS is activated, place information in header
Application.ScreenUpdating = False
'Policy #
[Code] ..........
As I said, this works all well and good for when I'm just printing Sheet2 but when I have to print Sheet 1 & 2 together, it no longer prints the way it should. There are times when Sheet 2 will be the first page of the overall document but there are a few instances where it will be second to Sheet 1 and I run into this issue. Is there a way to specifically reference which headers/footers the code will input the data?
View 1 Replies
ADVERTISEMENT
Oct 23, 2012
Using 2010 - When I paste a chart I can initially retain the format of chart bar colours (Paste Special - source formatting). But when I select a new data source for the chart the formatting of the colours is not retained.
View 1 Replies
View Related
Dec 14, 2011
I don't have Adobe Acrobat (just the reader) and I have Excel 2010.
I'd like a macro to do the following:
Take two ranges from a single sheet.
Print them to the same PDF with different header/footers using the built-in Excel PDF generator (File->Save & Send->Create PDF/XPS Document).
How can I do this? I already have a macro which exports the current sheet to a PDF, but I'm looking to export two ranges with difference header/footers to a single PDF.
Below is my current macro:
Sub PrintActiveSheetToPDF()
Dim strFileName As String, strDir As String, strFullPath As String
Dim fso As Scripting.FileSystemObject
Set fso = CreateObject("scripting.filesystemobject")
[Code] ........
View 1 Replies
View Related
Feb 19, 2014
I have been struggling to find where my code is throwing up an error 1004 on the last line of the below code. I have a number of tables which will update automatically from Pivot tables on another sheet.
So the first part of the code is adding in the new column and then I want to autofill the date into the headers of the column which I thought the below would do, but I just don't understand why I keep getting the error. My data is dynamic as it will grow month by month which is why I am using R1C1 referencing.
Sheets("PNN Table").Cells(9, 16384).Select
ActiveCell.End(xlToLeft).Select
ActiveCell.Offset(0, -2).Select
Selection.AutoFill Destination:=Range(Selection, Selection.Offset(0, 1))
View 3 Replies
View Related
Jan 9, 2013
I have a rolling 12 month (each day in column) tab in Excel 2010 that references variances by ID number in column A, and the column headers are each day for 366 days (2011). I would like to create a tab that shows the past 10 day's variances by ID number. I haven't been able to write a formula that will look at the date headers and the row ID number to return the figure for that day. Here is a sample of the 12 month rolling and the 10 days at a glance that I want to populate.
Rolling 12 Months
IDName12/25/201212/26/201212/27/201212/28/201212/29/2012and so on
1234Employee Name - - - - (11.07)
1235Employee Name - - - 0.20 -
1236Employee Name - - - - -
1237Employee Name - - - - (1.00)
[Code] .........
View 4 Replies
View Related
May 26, 2009
Is there a way to print my columns headers in every page?
Everytime I have to print my spread sheet, I always have to keep page#1 in hands, because I cannot remember the headings.
View 2 Replies
View Related
Nov 23, 2011
Have very recently been upgraded to Windows 7 with Excel 2010 at work. On printing out a 10 page doucment, (all of which are landscape format), when vewing print preview, the first page is previewed as landsacpe, but subsequent pages are portrait.
If you change format of 2nd page to landscape all subsequent pages switch to landscape.
Have looked at a similar format document created last month and it behaves exactly the same. Whole document landscape but on print preview only first page is...
View 3 Replies
View Related
Feb 5, 2014
I have a 2010 Excel spreadsheet where I have a macro set up to print the page. What I need it to do though is to increase a number in a cell and display it each time I print the page.
View 9 Replies
View Related
Jul 29, 2011
We have just refreshed our entire printer fleet to Xerox printers and as part of the project we have selected all defaults to B&W and duplex.
On a multi sheet excel file, If we click print then choose entire workbook and then change the print options to colour, it prints the only first worksheet in colour, and all the others in mono. i have read that excel treats each worksheet separately and does not cascade the changes made made in the first worksheet even if all sheets have been selected.
How can I get them all to print in colour?
We can set up another printer and change the defauts to colour but we dont want to go down that road and have 2 printers installed for each model.
Is there any code out there can will/ can change the properties of the first worksheet and cascade this throughout the workbook.
We use XP and W7 workstations and office 2003, 2007 and 2010. By the end of the year all workstations will be W7 and Office 2010.
View 2 Replies
View Related
Nov 8, 2012
I'm trying to make a simple chart, which maps the value of an investment fund over time. I wanted to use the new 'Table' feature within Excel 2010 to format and maintain the formulae within the Table, and the 'Header' for the table contains the date, which is not at regular intervals.
If I opt not to use the Table feature, I can create a line-chart with ease, and Excel recognises that the Dates are indeed dates and plots the graph correctly. The minute I convert over to a Table, the Date headers are no longer recognised as dates, and are instead plotted as if they were text, at regular intervals.
I've tried multiplying the Date Headers by 1 to force them back to true Dates, but this still does not work. I've also changed the setting on the horizontal axis to Date axis rather than automatic, but still no joy.
View 1 Replies
View Related
Apr 28, 2013
I am using Excel 2010
I have over 800+ pages of chart that only takes up 6 columns and around like 9000+ rows.
I wanted to print this chart on paper and need hardcopies. However, the chart in its current setup prints only on the left half of the page leaving the right half empty.
How do i make use of the full space properly? Each chart has a "page number" on it so I want the chart to print continuously from one half of the page onto the next half and then the second page, third, etc.
Here is a visual demonstration of how things currently are and how i'd like to get them to be:
As you can see, This is the first of many charts and its numbered Page 9 and next one is page 10.
Pic1
How this looks when i try to print, it's only on the left side. right is all blank
Pic2
How i want it to look like upon printing
Pic3
As you can see in the last picture, once page 14 chart has no space it automatically continues chart on right side of page and then moves on to print rest.
[URL]
View 7 Replies
View Related
Mar 15, 2012
I have code to copy sheets from one workbook to another, but how to maintain the order based on the Source Workbook.
Actually what i'm trying to do is copy all the Sheets named start with "_". FYI, i cannot use the Index of Sheets as well.
Code:
i = 0
For Each ws In Workbooks(xWBSource).Worksheets
If ws.Name Like "_*" Then
i = i + 1
sArrSheet(i) = ws.Name
[Code]...
View 1 Replies
View Related
Nov 11, 2012
I have a userform that opens up to full screen, however, when i designed it everything is central and in its place, but when the userform opens full screen everything is pushed to the left and not using all the form.
I know that in VB2010 u can use docking and table layout panels for this sort of thing but VBA 2007 which I am using with excel doesnt seem to have these options?
View 1 Replies
View Related
Jul 26, 2013
How many sheets can be created in Excel 2010? Is it depend on memory PC or is any limit within Excel?
View 2 Replies
View Related
Nov 10, 2008
i have a workbook that has the following sheets
working sheet
job sheet
receipt of deposit letter
completion sheet
delivery note
delivery note (2)
odd
even
t&t
glass
ggf
i want to hide every sheet except the working sheet.
I have tried this but the macros bring up an error when i run the macro
my macros involve printing certain pages dependng on what button is pressed
i get an error whatever
how do i stop this
View 14 Replies
View Related
Jul 28, 2012
I am using Excel 2010.
I found code from the internet attributed to Bob Phillips for VBA to have a dialog box pop up and allow the user to select what worksheet to navigate to.
The code works well except it doesn't exclude hidden or very hidden worksheets.
Code:
Sub BrowseSheets()
Const nPerColumn As Long = 38 'number of items per column
Const nWidth As Long = 13 'width of each letter
Const nHeight As Long = 18 'height of each row
Const sID As String = "___SheetGoto" 'name of dialog sheet
Const kCaption As String = " Select worksheet to goto"
'dialog caption
[Code]....
I know there is the line of "If Sheets(i).Visible And i Mod nPerColumn = 1 Then..." but it still allows all sheets to show up in the dialog box.
How it can be modified to include visible sheets only?
View 9 Replies
View Related
Jan 7, 2014
I have a workbook which needs a new sheet for each day of the month.I normally just create and re-name each sheet with the date...is there a simpler way where i can create the new sheet with a date for each month instead of doing indiviually?
Excel 2010
View 9 Replies
View Related
Jul 18, 2014
I'm working to compare to sheet every days and i have been doing this for 2 years
I need to write macro to compare 2 columns from two sheets (sheet1 and sheet2) on same excel book and display the result on the sheet2. The number of rows and columns may not be equal and it can vary each time.
The result should be written in the sheet2 at column 'NOTES'
If serial number is blank, the result written at NOTES column =*NOT TRACK
If serial number found on sheet 1-serialNumber column, the result written at NOTES column =*Registered
If no serial number found on sheet 1-serialNumber column, the result written at NOTES column =*New registration
Example Sheet1:
PurchaseOrderNo
Manufacturer
ProductNumber
SerialNumber
PO123
Table
453154-B21
[Code] .....
I'm using excel 2010 and window 7.
View 9 Replies
View Related
Apr 1, 2008
I am trying to print the file name as a header and the tab name as a footer automatically when i print an entire workbook. I am following the following steps:
File - Print - Entire Workbook - Preview
once I am in preview, I enter Page Setup and choose custom header (to print file name) and custom footer (to print sheet name). It works on some but not on all of them
View 4 Replies
View Related
Feb 18, 2014
I have a work sheet named "Main_List"...In column D starting with "D2" I would like to list worksheets that I would like to have printed via VBA.
The workbook has several hundred worksheets and I would like to list in column D only worksheets that I would like to print with VBA code.
View 3 Replies
View Related
Nov 13, 2012
Is there a way to sort out the sheets into alphabetical order in excel 2010?
View 5 Replies
View Related
Jan 14, 2014
I am looking up the largest value across various sheets (1 to 99) with the following formula:
=LARGE('1:99'!$B$1:$B$50;$C3)
That correctly returns the largest value in range B1:B50 across sheets 1 to 99.
However now I want to know the sheet name of the value above in a seperate cell, let say in: A3.
I'm using excel 2010.
View 3 Replies
View Related
Mar 6, 2012
I have a workbook that contains two sheets with two separate queries. On the first sheet I have two cells designated to accept the values for the parameters. The first sheet accepts the values and populates the table correctly. The second sheet doesn't seem to accept the values and just returns blank rows.
View 9 Replies
View Related
Nov 4, 2012
How i could this one in formula or macro, first my 1st sheet is just as show below (maybe use for a template)
1st sheet (full list)
fruits
china
orange
0
0
0
0
fruits
china
apple
[Code]...
then 2nd sheet, report i get which something like this :
fruits
china
orange
1
25
1
500
fruits
jiapan
kiwi
2
[Code]...
then i want result like this on 3rd sheet :
fruits
china
orange
1
25
1
599
[Code]....
so basically i want add missing row on sheet 2 from sheet 1
I am using office 2010.
View 4 Replies
View Related
Nov 7, 2012
I have headings across the sheet "Inventory" in cells E5:AA5 with further headings down the sheet D6:D40.Data Validation drop down list is in cells E6:AA40.I want to be able to place a number 1 or number 2 in any cell in the range C6:C40 in the "inventory" sheet and with a macro create 2 new sheets.Sheet 1 will contain all the headings which had a 1 in any of the cells in the range C6:C40 and sheet 2 will have all the headings which had the number 2 from the range C6:40.Both sheets 1 & 2 will contain all the headings from "Inventory" sheet in cells E5:AA5.The data validation drop down list is not required in sheets 1 and 2 but if "inventory" sheet does get updated that it also updates sheet 1 and sheet 2.... Version i am using is Excel 2010
View 1 Replies
View Related
Feb 12, 2014
I am using microsoft excel 2010. I currently have workbooks for various things with 46 worksheets in each one (each worksheet is a store). I usually go into the worksheets daily and dump numbers into each sheet. I was wondering if there was a way to switch worksheets but keep the cell the same when i switch to a different sheet to make it much easier to dump the numbers in.
For example: Say in worksheet1 i am in cell A34 to dump in a number. When i switch to worksheet2 i want to be in the same cell to dump the next number for the next store and so on and so forth for the next 44 sheets.
View 7 Replies
View Related
Apr 8, 2014
I have a sheet that contains a Microsoft Query (SQL) data connection. In cell C3, I have the month name and I also have a year cell (C4), which contains just the year based on what the current month (=YEAR(TODAY())) is.
For this example let's use May (C3) and 2013 (C4)
C3 and C4 are used as parameters for the SQL query, so if I change the month to January and the Year to 2010, data is refreshed to bring in that data.
Works smashingly.
I then want to add 11 more sheets (12 total), so each sheet is a month. The value in cell C3 on each sheet will be the month name.
If I just copy this sheet 11 times, Excel adds 11 extra data connections; 1 for each sheet. As the query in each sheet needs to be exactly the same, I don't want this. I want the 12 sheets to use the same data connection, only with the specified parameters at the sheet level.
Otherwise, if I need to add or remove a column, or change a column name, rather than just doing it once, I'd have to do it 12 times.
This can't be right, surely, as that would be the most ridiculous design flaw I've ever seen in a piece of software. How can having the same query 12 times be a good thing?
I'm a bit concerned that googling 'excel data connections multiple sheets' always wants to be 'excel multiple data connections one sheet' - I want 'excel multiple sheets one data connection'
View 1 Replies
View Related
Jan 22, 2010
I am putting in search items and running a macro to find the items on 'physical servers' WS, copying the header in that WS and the entire line the match appears on, though I cannot get it to do this.... it is really causing me stress
Then next part that is working is the items that return false are showing up on the results page - this is expected and what i want it to continue to do.
What I cant seem to work out also is how to run the search on the 'Virtual Server' WS also and return the results to the results WS as just like the 'physical servers' WS.
I have included some dummy data + code + the expected result on the 'Server Results' WS.
Hope you all can work out a way to make it work.
View 14 Replies
View Related
Feb 10, 2014
I'm using Excel 2010 and would like to know if it's possible to convert selected ranges in multiple sheets into one PDF file? For example, I want to select range("A1:O10) in Sheet1 and range("A1:N25") in Sheet2, then convert both Excel sheets into PDF file with two pages.
View 2 Replies
View Related
Jul 16, 2014
I need to sum a specific range of values between two headers in several dynamic sheets. The values in the sheets always starts in row 10 and column H (Forcast). For example the header could be "Sales" in column A. Then I want to sum values above the header "Sales" in forcast column H. The next header could be "Operations", where I want to sum the values between "Sales" and "Operations" in column H. Next header could be "Rents" then I want to sum the values between "Operations" and "Rents" in column H, and this is how it continues... Is there any macros for this?
View 2 Replies
View Related
