Define Names inactive worksheet

[dblock247]

Hi thanks for your help in advance. Im simply trying to define a global name in vba so that I can use it in the formula bar. My code works perfect when the sheet that the range is on is active but it breaks when another sheet is active


'Find last row
LastRow = Log.Cells(Log.Rows.Count, 1).End(xlUp).Row

'Setting names
Worksheets("Log").Range(Cells(2, 1), Cells(LastRow, 14)).Name = "LogTable"


can someone please tell me what im am doing wrong

[mikerickson]

The arguments of you expression refer to cells on the active sheet, but the qualification is for sheet Log, hense the problem, try this
With Worksheets("Log")
Range(.Cells(2, 14), .Cells(.Rows.Count, 1).End(xlUp).Offset(0,1)).Name = "LogTable"
End With

[dblock247]

Thanks for the info but i tried that and although it doesnt throw an error it breaks other parts of my code....

how come this doesnt work either


ThisWorkbook.Names.Add Name:="LogTable", _
RefersTo:=Worksheets("Log").Range(Cells(2, 1), Cells(LastRow, 14))


does it matter that im in a code module instead of a workbook module?

[mikerickson]

Again the Cells inside the two argument form of Range are unqualified.
(See the edit above where I inserted a dot.)

What do you mean "breaks other parts of code"?

Or you could just use this dynamic name and not define it in code

Name: LogTable
RefersTo: =Log!$N$2:INDEX(Log!$A:$A,MATCH(9.9E+100,Log!$A:$A),1)

(If there are text entries in column A, then replace 99E+100 with "zzzzzzz")

[dblock247]

after i set the name i also set a range so i can sort it when needed here is the full code


Sub Matrix()

'Turn off screen updating
Application.ScreenUpdating = False

'Creating the worksheet variables
Dim Matrix As Worksheet
Dim Log As Worksheet
Dim LastRow As Integer, Doc As Integer, Rank As Integer, iRate As Integer

'Creating the range variables
Dim MatrixTable As Range
Dim BranchTable As Range
Dim LogTable As Range


'Setting worksheet
Set Matrix = ThisWorkbook.Worksheets("Matrix")
Set Log = ThisWorkbook.Worksheets("Log")

'Find last row
LastRow = Log.Cells(Log.Rows.Count, 1).End(xlUp).Row

'Setting names
ThisWorkbook.Names.Add Name:="LogTable", _
RefersTo:=Worksheets("Log").Range(Cells(2, 1), Cells(LastRow, 14))


'Setting ranges
Set LogTable = ThisWorkbook.Names("LogTable").RefersToRange
Set MatrixTable = ThisWorkbook.Worksheets("Matrix").Range("A15:AF34")
Set BranchTable = ThisWorkbook.Worksheets("Matrix").Range("A6:AF11")

'Setting variables
Doc = 2
Rank = 32
iRate = 11

'Sorting
LogTable.Sort key1:=LogTable.Cells(2, Doc)
MatrixTable.Sort key1:=MatrixTable.Cells(1, Rank), Key2:=MatrixTable.Cells(1, iRate), _
Order1:=xlAscending, Order2:=xlDescending


'Turn on screen updating
Application.ScreenUpdating = True

End Sub




when i use with it breaks when i sort it

[mikerickson]

Have you tried the dynamic name approach I mentioned.

Also, I noticed that I use the wrong column in post #2. This should work
With Worksheets("Log")
Range(.Cells(2, 14), .Cells(.Rows.Count, 1).End(xlUp)).Name = "LogTable"
End With

[dblock247]

ahhh i see thank you i didn't catch the slight mix up in row numbers but the last post works perfect for me thank you very much i really appreciate your help

[dblock247]

could you answer one last off topic question... i have an array forumla (control shift enter) that works perfect but it would not let me substitue the name for the range.. any idea why that is

original code works perfect

=IF($C15="","",IF($C15=0,0,ROUND((SUM((Log!$N$2:$N$1001=$A15)*(Log!$E$2:$E$1001="X"))/$C15)*100,0)))


code names ranges substituted doesnt work


=IF($C15="","",IF($C15=0,0,ROUND((SUM((Initials=$A15)*(DW="X"))/$C15)*100,0)))

[mikerickson]

Those don't look like code, they look like formulas.
What "doesn't work"? does it return an error or the wrong value?

Also are you sure that Initials and DW have the same number of rows?
Is there a header "Initials" on the sheet, it might be confusing the Label for a Name.

[dblock247]

Yea im sorry that is correct and they do work. My question is if i set the names in that manner how do i then hide them form the end user. Usually it would be something like this:


Names.Add Name:=”ProduceNum”, RefersTo:=”=$A$1”, Visible:=False


And one last question. It may be a little off topic but since you have been so helpful I felt i should just give it a shot. I need to make the formatting permanent so even a copy and paste cant change it because people will be taking data from other workbooks and pasting it into this one.

What would be the best is if I could just have the code select a folder and run through all the workbooks in the folders and past the data in from those workbooks not selecting anything that would duplicate a ticket number

[mikerickson]

With Worksheets("Log")
Range(.Cells(2, 14), .Cells(.Rows.Count, 1).End(xlUp)).Name = "LogTable"
End With
ThisWorkbook.Names("LogTable").Visible = False

I need to make the formatting permanent so even a copy and paste cant change it because people will be taking data from other workbooks and pasting it into this one. Names are good for that.

What would be the best is if I could just have the code select a folder and run through all the workbooks in the folders and past the data in from those workbooks not selecting anything that would duplicate a ticket number
That would best be addressed in a new thread, but should be quite doable
Knowing aprox. how many workbooks in the folder and uploading a sample workbook to show the data lay-out would help that thread.

[dblock247]

Okay I will start a new thread then. But let me ask you this is there a maximum size that a name that refers to a range can hold. I have excel 2011 for mac and when i make a really large named range it says when i save if in this format Excel 97-2004 Workbook it says some things will be lost if saved in this format.

If i save it under the Excel Macro enabled workbook .xlsm will that open on older versions of excel?

[mikerickson]

If you exceed the 255 column or 65546 row limits of pre-2007 Excel, you will loose things in .xls format.

There are converters so pre-2007 Excel can open .xlsm or .xlsx files.