Replies

In the Workbook_BeforePrint event, use a statement like:

Dim strBook_Path+Name as String
strBook_Path+Name = ActiveWorkBook.fullname
ActiveSheet.PageSetup.LeftFooter = strBook_Path+Name

You can vary the “LeftFooter” statement to be any one of the six Header/Footer locations.

Sorry, this sounds serious and all I can offer is empathy, but I thought I’d let you know it’s good to see your signature line back on the boards.

A common problem when moving or copying worksheets is that the defined names get carried along. Check your copied sheet for defined names that have references pointing back to the original workbook. Delete those names, and your ‘Links’ should go away.

If I understand your question, you’re trying to define a Worksheet level name. To do that, you need to prefix your name with “worksheet_name!”(no quotes)
For example, to define a name of ‘TEST’ for three different sheets called Sheet1, Sheet2, Sheet3, you’d Insert / Name / Define on Sheet1. The name you type in would be “Sheet1!TEST” the reference would be whatever range you wanted on Sheet1. Then go to Sheet2 and repeat this process except this time you’d type in “Sheet2!TEST” for your name. Repeat for Sheet3. You would now have three different named ranges all with the name TEST but refering to different ranges depending upon which sheet was active.

If you don’t have too big an area to examine, you could try Tools / Options / View and check on the Formulas box. That would allow you to do a quick visual examination of all formula cells to look for external references. Other than that, there are a couple of VBA tools around that will locate / list links. I don’t have the references handy at the moment.

Not to be picky… but shouldn’t the first line in your example be:

DIM intCounter as Integer
OR
DIM iCounter as Integer

You’d probably be best served by addressing this question to the author, John Walkenbach, at: john@j-walk.com

This situation occurs when someone chooses Window / New Window on the menu bar. A second view of the workbook is made and the (1) and (2) suffixes are generated. If the workbook is closed with both windows active, you’ll get the situation you have. Just open the workbook, choose one of the two names in the Window list and close that view by clicking the ‘X’ in the upper right of the that window — NOT the one in system bar for Excel itself. The easiest way is to go to Window / Arrange. Choose Horizontal and then you’ll see both windows with their respective close buttons. Close one copy then when you close the other, it should remain ‘single’.

I see this occasionally on Novell servers where I work. It seems to have something to do with Novell and antivirus products. We had this problem with some of the updates from McAfee. It seems to come and go with no logical pattern. Usually, the file that was created could be immediately re-opened and saved as the original filename without consequence.

I agree — the examples in the Help files aren’t always what one might expect from ‘pros’.

Leif’s suggestion regarding a residual module is probably your problem, but if it isn’t, you may have inadvertently activated one of the Workbook or Worksheet event stubs.
In the VBE, select the workbook and each sheet in turn and make sure you don’t have a stub that got generated during your ‘experimenting’. Example might look like:
Private Sub Worksheet_SelectionChange(ByVal Target as Excel.range)
End Sub

Yuk! — I agree…
That’s why I like the ‘Advanced Filter’ approach. I’ve also used variations of your suggestion, but my brain was in 1/3 speed when I posted my response.

Two quick methods come to mind:
Sort your data by those three columns and do a manual inspection / deletion – OR – use the Advanced Filter (Data / Filter / Advanced Filter) to filter the list to unique values then copy this list to a new area.

off the topic????

I always thought SUM(IF was the same as SUMIF — just smarter

Another highly flexible approach is to use array formulae constructed in the form of:
=Sum(If(CompareRange operator condition),If(CompareRange2 operator condition2),Sumrange,0),0)).
This example would ‘AND’ the conditions. You could add the two ‘IF’ statements together to ‘OR’ the conditions. Look under TOOLS/Wizard for the Conditional Sum wizard. Play with this and see what formulae it builds for different circumstances and you should be able to get a good idea of how this works.
HTH,