Replies

RE: All I can say is that is sounds as if the bin settings are somehow stored with the Excel file, although I wouldn’t have thought it to be the case.

It is indeed the case in both Word and Excel. Any item that you save in page setup such landscape or tray setting will stick. Can understand page orientation, but tray setting is strange.

Attachment is opened by double click, and then printed. I just tested sending an Excel worksheet with a setting to print to bin 4 to a co-worker. He normally prints to bin 2 of the same printer that I do, and when he attempted to print it, it did so in bin 4 as I had set it.

Guess the question is, why would an Excel setting over-ride your default printer settings?

Will have to have clients involved, check to see if they have some unusual Excel settings.

Thanks for the clue.

When i go to Edit>Links, Links is greyed out.

Double clicking opens Excel as an object in Word.

Everything that the client wanted is working to his satisfaction. Saves some time in getting the document completed. If he is happy, I’m happy and thanks to the forum for helping me to learn some more Word features.

If all you are trying to do is ignore the zero’s in your column try =Average(if (b2:b120,b2:b12,””)) and enter it as an array CRTL+SHIFT+ENTER. Using this formula on the sample sheet from above, the average works out to 47.28571.

Thanks for the further information. Excel is embedded. However, when I re-open the document I get the message about being linked to another file and do I want to up-date the links. Click OK and everything seems to run OK.

I say that the file is embedded because I have to open Word and then edit the Excel sheet. This seems to correspond to the inforamtion taken from Page 143 of “Using Microsoft Office XP” in the section “Embedding versus Linking”.

This Word document also has an Excel spreadsheet incorporated. Is there a way to link 2 excel cells to the Word portion of the document.? For example, assume that cell D5 in the Excel portion has a sum command. The client would like to have the total from D5 be entered also into a paragraph in the Word portion of the document.

Thanking you in advance.

I have found how to link the cell to the Word document thro’ Paste/Special. However, it will not automatically update in Word. I have to force an update.

W2K, XP and drivers are the latest

Thanks for the replies. I went to Stuart’s suggestion. Works great!

The CenterFooter = Format(Date, “mmm, d, yyyy”) gives the currrent date, not the created date. I noted an earlier reply for a static date is ActiveCell = Date. Can this be put into the CenterFooter, and if so, what would be the syntax. Would CenterFooter = Date return a static date, that is the date the document was created.

Thank you. Didn’t realize the diference between the &N and & P.

Once again, Thanks.

Hans, I tried the code you provided. However, it still does not seem to work properly. I created a 2 page Excel file ran the code you provided. I now get the footer on all pages, but the page number on each page is 2.

I have checked and double checked the code and it is identical to yours.

Sorry to be a bother about this, but as I am very new to using vba in Excel I need all the help I can get.

I must say that this site and all you knowledgeable people are a great resource.

I tried your suggestion of selecting multiple sheets (4). I then ran the macro to insert the footer. I selected all 4 sheets to print. Footer still appears only on the first sheet, but numbered 4.

Is there some other change I have to make?

Thanks.

What changes would have to be made to the vba in order to have the footer appear on each sheet of a spreadsheet with the appropriate page number on each sheet.? For example, sheet 1 would be 1, sheet 2, 2, sheet 3, 3, etc.

When I attempted to do this with 3 sheets, the footer appears only on the first sheet, but the page number on the first sheet reads 3. Footers do no show on sheet 2 and 3.

The code for inserting time and date into an active cell is great. However, is there away to put it into the following code which inserts path/name and page number in the footer? Thanks in advance.

Sub InsertFooter()
‘
‘ InsertFooter Macro
‘ Macro recorded 09-12-2004
‘
‘ Keyboard Shortcut: Ctrl+Shift+F
‘
Range(“G7”).Select
With ActiveSheet.PageSetup
.PrintTitleRows = “”
.PrintTitleColumns = “”
End With
ActiveSheet.PageSetup.PrintArea = “”
With ActiveSheet.PageSetup
.LeftHeader = “”
.CenterHeader = “”
.RightHeader = “”
.LeftFooter = “&Z&F”
.CenterFooter = “”
.RightFooter = “&N”
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
End Sub

What happens if the facilty list has 2 names the same? I have tried to use this formula on a staff list. If 2 people have the same last name the vlookup will only return the first name from the list. From what I have read this is a draw back to using vlookup. The same happens with an Index/Match. Other than assigning some unique number code to each person, is there another way to handle duplicate names from the data validation list?