Excel 2010 macro vb error

[CJ]

Hi,
I have around 30 Macro's all designed to pick up a data files from a server import it make pivot table then E-Mail out.
All has worked very well for several years now using Excel 2002, well someone made the bold decsion to upgarde
so we have jumped to Excel 2010 yesterday,
Bad news the 1st Macro I tried today failed.

Error Message.
Microsoft Visual Basic

Run Time 'Error 1004'
The name that you entered is not valid
Reason for this:
1) The name does not begin witha letter or underscore
2) The name contains a space or other invalid character
3) The name conflicts with a Excel built in name or name of another object in the workbook

It fails at this point where it's naming the range.

'Select Active Range

ActiveWorkbook.Names.Add Name:="POP2", RefersToR1C1:= _
"=OFFSET(POP!R1C1,0,0,COUNTA(POP!C1),12)"

Has anyone else encountered this message when running OLd VBA Mascro's in Excel 2010

Any Idea's

 

[Ruddles]

You're falling foul of Rule 3 - you can't use a name for a range which is also a valid cell reference. In versions of Excel before 2007 the maximum column was IV so you could have a range called IW-something but not anything below IV.

In 2007 onwards you can have columns all the way to XFD so "POP2" is a valid cell reference and is therefore not allowed.

Use something 'after' XFD alphabetically or start the name with an underscore: "_POP2".

 

[CJ]

BIG BIG Thanks.......

Thanks for the detailed explanation, I see it now, I can't test it now I'm at home will
rename ranges tomorrow.

Can I ask does this only apply to named ranges, are sheet names ok, can my sheet still be
called POP.

Well big task ahead changes around odd named ranges in all my Macro Templates.

Thanks once again.....

 

[Ruddles]

It only applies to named ranges because they are of type Range like cells/rows/columns/ranges in a worksheet are of type Range.

Worksheets are of type Worksheet: Excel knows the difference between ranges and worksheets so it wouldn't even begin to be confused if a range and a worksheet had the same name.

Try renaming a worksheet "A1", for example: not a problem.

 

[CJ]

Thank you for your answer, I tried your _POP2 idea it worked a dream, I simply did a Find & Replace in my Macro and replaced all 4 instances of POP.

Can I as one more question, today I found out I have to is issue this POP TEMPLATE so several other new users some these cant see file extensions so this code won't close the template

Windows("POP TEMPLATE.xls").Activate
ActiveWorkbook.Close False

I want to amend the code to close anything contains this string POP TEMPLATE I've tried

Windows("POP TEMPLATE.*").Activate
ActiveWorkbook.Close False

This does not work

 

[Norie]

You can't use wildcards when trying to specify workbooks/windows by name.

Is the template actually being opened earlier in the code?

If it is then you could create a reference to it when that's done and then later on when you want to close it use that reference.

That should eliminate any problems with names, you would only need the name once - when you open the workbook.

If you are actually trying to close more than one workbook or the workbook isn't being opened in text then you would probably need to set up some sort
of loop to search through the names of all open workbooks.

By the way, are you using the named ranges in formulas on a worksheet(s).

Just wondering because there's another, perhaps simpler, way you could create them.

 

[Ruddles]

I'm not sure that the user's inability to see the file extension is what's causing the issue. However you could try something like this:-

Option Explicit
Option Compare Text
 
Sub CloseBooks()
 
  Dim wb As Workbook
 
  For Each wb In Workbooks
    If wb.Name Like "*[COLOR=red][B]pop template*.xl*[/B][/COLOR]" Then wb.Close True
  Next wb
 
End Sub

Change the bit in red to cover the files you want to close.

Is that what you mean?

 

[Norie]

Ruddles

I'm not sure either but it does happen especially when trying to refer to a window by name.