Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro Modification - Bob Philips Are You Out There ?
I use this macro on one of my workbooks and it worked awesome - it creates
tables from selected worksheets and names them according to a list of names. Asuming that the list is named SheetNames, then Sub maketable2() 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("a10").End(xlDown).Row LastCol = .Range("a10").End(xlToRight).Column Set RngToName = .Range("a10", .Cells(LastRow, LastCol)) RngToName.Name = Application.Range("SheetNames").Cells(i,1).Value End With End If Next sh End Sub I've tried to use this same macro to create another workbook and it it gets hung up he RngToName.Name = Application.Range("SheetNames").Cells(i,1).Value The text that appears when I hover the curser over the line is: RngToName=<Application-defined or object-defined error The only difference I can tell between this new workbook and the other workbook is the "SheetNames". Any Thoughts ? Thank you again. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro Modification - Bob Philips Are You Out There ?
Hi Carlo,
When you get the error, hover over the variables LastRow and LastCol and see what the values are. Also type ?RngToName.Address in the immediate window and see what it returns. Finally, what is in A1:A12 in that problem sheet. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "carl" wrote in message ... I use this macro on one of my workbooks and it worked awesome - it creates tables from selected worksheets and names them according to a list of names. Asuming that the list is named SheetNames, then Sub maketable2() 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("a10").End(xlDown).Row LastCol = .Range("a10").End(xlToRight).Column Set RngToName = .Range("a10", .Cells(LastRow, LastCol)) RngToName.Name = Application.Range("SheetNames").Cells(i,1).Value End With End If Next sh End Sub I've tried to use this same macro to create another workbook and it it gets hung up he RngToName.Name = Application.Range("SheetNames").Cells(i,1).Value The text that appears when I hover the curser over the line is: RngToName=<Application-defined or object-defined error The only difference I can tell between this new workbook and the other workbook is the "SheetNames". Any Thoughts ? Thank you again. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro Modification - Bob Philips Are You Out There ?
Thanks Bob.
I'll try and recall - I won't be able to get the workbook until monday. I did not mention that the A10 in the code was replaced with B19 - if that makes a difference. I think when hobering over the varioables last row/last col the value 65000 and 256 respectively. I do not think there is any data in A1:A12 although not sure. I'll check that. Thanks Again. "Bob Phillips" wrote: Hi Carlo, When you get the error, hover over the variables LastRow and LastCol and see what the values are. Also type ?RngToName.Address in the immediate window and see what it returns. Finally, what is in A1:A12 in that problem sheet. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "carl" wrote in message ... I use this macro on one of my workbooks and it worked awesome - it creates tables from selected worksheets and names them according to a list of names. Asuming that the list is named SheetNames, then Sub maketable2() 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("a10").End(xlDown).Row LastCol = .Range("a10").End(xlToRight).Column Set RngToName = .Range("a10", .Cells(LastRow, LastCol)) RngToName.Name = Application.Range("SheetNames").Cells(i,1).Value End With End If Next sh End Sub I've tried to use this same macro to create another workbook and it it gets hung up he RngToName.Name = Application.Range("SheetNames").Cells(i,1).Value The text that appears when I hover the curser over the line is: RngToName=<Application-defined or object-defined error The only difference I can tell between this new workbook and the other workbook is the "SheetNames". Any Thoughts ? Thank you 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 | |||
Macro Modification Help | Excel Worksheet Functions |