Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro / Bob Phillips if you are out there
I tried to post on the original thread but did not seem to work.
Here it is again: Here's the code that I am actually using (I changed the cell reference A10 to B19). Option Explicit Sub maketable3() Dim SheetList As Sheets Dim RngToName As Range Dim LastRow As Long Dim LastCol As Long Dim NameToUse As String Dim sh As Worksheet Dim I As Long Set SheetList = ActiveWindow.SelectedSheets For Each sh In SheetList I = I + 1 If Application.Range("SheetNames").Cells(I, 1).Value < "" Then With sh LastRow = .Range("b19").End(xlDown).Row LastCol = .Range("b19").End(xlToRight).Column Set RngToName = .Range("b19", .Cells(LastRow, LastCol)) RngToName.Name = Application.Range("SheetNames").Cells(I, 1).Value End With End If Next sh End Sub Bob asked: When you get the error, hover over the variables LastRow and LastCol and see what the values are. LastRow=109 Last Column=6 Bob asked: Also type ?RngToName.Address in the immediate window and see what it returns. ?RngToName.Address $B$19:$F$109 Bob asked: Finally, what is in A1:A12 in that problem sheet. Nothing. Completely blank. Thanks again. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro / Bob Phillips if you are out there
Carl,
Those values look okay. The only other thing to look at is the value being assigned. What does Application.Range("SheetNames").Cells(I, 1).Value hold at the point of error? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "carl" wrote in message ... I tried to post on the original thread but did not seem to work. Here it is again: Here's the code that I am actually using (I changed the cell reference A10 to B19). Option Explicit Sub maketable3() Dim SheetList As Sheets Dim RngToName As Range Dim LastRow As Long Dim LastCol As Long Dim NameToUse As String Dim sh As Worksheet Dim I As Long Set SheetList = ActiveWindow.SelectedSheets For Each sh In SheetList I = I + 1 If Application.Range("SheetNames").Cells(I, 1).Value < "" Then With sh LastRow = .Range("b19").End(xlDown).Row LastCol = .Range("b19").End(xlToRight).Column Set RngToName = .Range("b19", .Cells(LastRow, LastCol)) RngToName.Name = Application.Range("SheetNames").Cells(I, 1).Value End With End If Next sh End Sub Bob asked: When you get the error, hover over the variables LastRow and LastCol and see what the values are. LastRow=109 Last Column=6 Bob asked: Also type ?RngToName.Address in the immediate window and see what it returns. ?RngToName.Address $B$19:$F$109 Bob asked: Finally, what is in A1:A12 in that problem sheet. Nothing. Completely blank. Thanks again. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I email amacro? | Excel Worksheet Functions | |||
link to combobox | Excel Discussion (Misc queries) | |||
Search, Copy, Paste Macro in Excel | Excel Worksheet Functions | |||
Editing a simple macro | Excel Worksheet Functions | |||
Closing File Error | Excel Discussion (Misc queries) |