Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open Event
The follow code worked when I had it in a
CommandButton_Click Event (used that event for testing purposes). But when I transferred the code to the Workbook_Open Event, I receive a "Run-Time error '9': Subscript out of range" at "With ThisWorkbook... " Why?? TIA Mike Private Sub Workbook_Open() Dim numRows As Long 'Refresh data of Sheet2 Workbooks.Open Filename:="C:\CCF\Contracts1.xls" With ThisWorkbook.Worksheets("Sheet1") 'Determine number of rows numRows = Application.CountA(ActiveSheet.Range ("A:A")) ActiveSheet.Range("A1:AI" & numRows).Copy .Range ("Contracts!A1") End With ActiveWorkbook.Close cmbContracts.ListFillRange = "Contracts!A2:C" & numRows End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open Event
Hi
try the following: change the line With ThisWorkbook.Worksheets("Sheet1") to With ActiveWorkbook.Worksheets("Sheet1") Frank Squid wrote: The follow code worked when I had it in a CommandButton_Click Event (used that event for testing purposes). But when I transferred the code to the Workbook_Open Event, I receive a "Run-Time error '9': Subscript out of range" at "With ThisWorkbook... " Why?? TIA Mike Private Sub Workbook_Open() Dim numRows As Long 'Refresh data of Sheet2 Workbooks.Open Filename:="C:\CCF\Contracts1.xls" With ThisWorkbook.Worksheets("Sheet1") 'Determine number of rows numRows = Application.CountA(ActiveSheet.Range ("A:A")) ActiveSheet.Range("A1:AI" & numRows).Copy .Range ("Contracts!A1") End With ActiveWorkbook.Close cmbContracts.ListFillRange = "Contracts!A2:C" & numRows End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open Event
The obvious answer is you no longer have a sheet named Sheet1. Your note
says refresh the data on Sheet2, so I susptect that is the case. Change the name to sheet2 Private Sub Workbook_Open() Dim numRows As Long 'Refresh data of Sheet2 Workbooks.Open Filename:="C:\CCF\Contracts1.xls" With ThisWorkbook.Worksheets("Sheet2") 'Determine number of rows numRows = Application.CountA(ActiveSheet.Range("A:A")) ActiveSheet.Range("A1:AI" & numRows).Copy _ .Range("Contracts!A1") End With ActiveWorkbook.Close SaveChanges:=False ' this may be your next error - you probably need to qualify this cmbContracts.ListFillRange = "Contracts!A2:C" & numRows End Sub -- Regards, Tom Ogilvy Squid wrote in message ... The follow code worked when I had it in a CommandButton_Click Event (used that event for testing purposes). But when I transferred the code to the Workbook_Open Event, I receive a "Run-Time error '9': Subscript out of range" at "With ThisWorkbook... " Why?? TIA Mike Private Sub Workbook_Open() Dim numRows As Long 'Refresh data of Sheet2 Workbooks.Open Filename:="C:\CCF\Contracts1.xls" With ThisWorkbook.Worksheets("Sheet1") 'Determine number of rows numRows = Application.CountA(ActiveSheet.Range ("A:A")) ActiveSheet.Range("A1:AI" & numRows).Copy .Range ("Contracts!A1") End With ActiveWorkbook.Close cmbContracts.ListFillRange = "Contracts!A2:C" & numRows End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open Event
I had a second look at your code and I notices you do
With With ThisWorkbook.Worksheets("Sheet2") .. .. .. copysomething to .Range("Contracts!A1") If contracts is the sheet where you want the data copied to you should use With ThisWorkbook.Worksheets("Contracts") .. .. .. copysomething to .Range("A1") -- Regards, Tom Ogilvy Tom Ogilvy wrote in message ... The obvious answer is you no longer have a sheet named Sheet1. Your note says refresh the data on Sheet2, so I susptect that is the case. Change the name to sheet2 Private Sub Workbook_Open() Dim numRows As Long 'Refresh data of Sheet2 Workbooks.Open Filename:="C:\CCF\Contracts1.xls" With ThisWorkbook.Worksheets("Sheet2") 'Determine number of rows numRows = Application.CountA(ActiveSheet.Range("A:A")) ActiveSheet.Range("A1:AI" & numRows).Copy _ .Range("Contracts!A1") End With ActiveWorkbook.Close SaveChanges:=False ' this may be your next error - you probably need to qualify this cmbContracts.ListFillRange = "Contracts!A2:C" & numRows End Sub -- Regards, Tom Ogilvy Squid wrote in message ... The follow code worked when I had it in a CommandButton_Click Event (used that event for testing purposes). But when I transferred the code to the Workbook_Open Event, I receive a "Run-Time error '9': Subscript out of range" at "With ThisWorkbook... " Why?? TIA Mike Private Sub Workbook_Open() Dim numRows As Long 'Refresh data of Sheet2 Workbooks.Open Filename:="C:\CCF\Contracts1.xls" With ThisWorkbook.Worksheets("Sheet1") 'Determine number of rows numRows = Application.CountA(ActiveSheet.Range ("A:A")) ActiveSheet.Range("A1:AI" & numRows).Copy .Range ("Contracts!A1") End With ActiveWorkbook.Close cmbContracts.ListFillRange = "Contracts!A2:C" & numRows End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open Event
Ok, eventually I will get the code right. I think I am closer than ever
before with your help (if you are ever in Pittsburgh... I owe you a beer). Thanks again.. Mike The problem now is it is copying the contents of the wrong worksheet. It copies data from workbook Settlement.xls, sheet Settlement (first sheet in workbook), instead of workbook Contracts1.xls, sheet Sheet1. Plus it doesnt like my code for the combobox. What am I doing wrong again? The code you gave me earlier this week worked perfect with a command button. But putting it in the Workbook_Open event it doesnt want to work correctly. What is the reason for this? Private Sub Workbook_Open() Dim numRows As Long 'Refresh data of Contracts worksheet in C:\CCF\Settlement4.xls from data contained 'in worksheet Sheet1 in C:\CCF\Contracts1.xls Workbooks.Open Filename:="C:\CCF\Contracts1.xls" With ThisWorkbook.Worksheets("Contracts") 'C:\CCF\Settlement4.xls 'Determine number of rows in C:\CCF\Contracts1.xls Sheet1 numRows = Application.CountA(ActiveSheet.Range("A:A")) 'Copy from C:\CCF\Contracts1.xls!Sheet1 and 'Paste in C:\CCF\Settlement4.xls!Contracts ActiveSheet.Range("A1:AI" & numRows).Copy .Range("A1") 'modify combobox properties to update listfillrange cmbContracts.ListFillRange = "Contracts!A2:C" & numRows End With ActiveWorkbook.Close 'Select Settlement worksheet so this is what the user sees Worksheets("Settlement").Select End Sub "Tom Ogilvy" wrote in message ... I had a second look at your code and I notices you do With With ThisWorkbook.Worksheets("Sheet2") . . . copysomething to .Range("Contracts!A1") If contracts is the sheet where you want the data copied to you should use With ThisWorkbook.Worksheets("Contracts") . . . copysomething to .Range("A1") -- Regards, Tom Ogilvy Tom Ogilvy wrote in message ... The obvious answer is you no longer have a sheet named Sheet1. Your note says refresh the data on Sheet2, so I susptect that is the case. Change the name to sheet2 Private Sub Workbook_Open() Dim numRows As Long 'Refresh data of Sheet2 Workbooks.Open Filename:="C:\CCF\Contracts1.xls" With ThisWorkbook.Worksheets("Sheet2") 'Determine number of rows numRows = Application.CountA(ActiveSheet.Range("A:A")) ActiveSheet.Range("A1:AI" & numRows).Copy _ .Range("Contracts!A1") End With ActiveWorkbook.Close SaveChanges:=False ' this may be your next error - you probably need to qualify this cmbContracts.ListFillRange = "Contracts!A2:C" & numRows End Sub -- Regards, Tom Ogilvy Squid wrote in message ... The follow code worked when I had it in a CommandButton_Click Event (used that event for testing purposes). But when I transferred the code to the Workbook_Open Event, I receive a "Run-Time error '9': Subscript out of range" at "With ThisWorkbook... " Why?? TIA Mike Private Sub Workbook_Open() Dim numRows As Long 'Refresh data of Sheet2 Workbooks.Open Filename:="C:\CCF\Contracts1.xls" With ThisWorkbook.Worksheets("Sheet1") 'Determine number of rows numRows = Application.CountA(ActiveSheet.Range ("A:A")) ActiveSheet.Range("A1:AI" & numRows).Copy .Range ("Contracts!A1") End With ActiveWorkbook.Close cmbContracts.ListFillRange = "Contracts!A2:C" & numRows End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open Event
Private Sub Workbook_Open()
Dim numRows As Long Dim sh1 as Worksheet Dim sh2 as Worksheet 'Refresh data of Contracts worksheet in C:\CCF\Settlement4.xls from data contained 'in worksheet Sheet1 in C:\CCF\Contracts1.xls Workbooks.Open Filename:="C:\CCF\Contracts1.xls" set sh1 = Workbooks("Contracts1.xls").Worksheets("Sheet1") set sh2 = Workbooks("Settlement4.xls").Worksheets("Contracts ") 'Determine number of rows in C:\CCF\Contracts1.xls Sheet1 numRows = Application.CountA(sh1.Range("A:A")) 'Copy from C:\CCF\Contracts1.xls!Sheet1 and 'Paste in C:\CCF\Settlement4.xls!Contracts sh1.Range("A1:AI" & numRows).Copy sh2.Range("A1") 'modify combobox properties to update listfillrange sh2.Parent.Worksheets("Settlement").cmbContracts. _ ListFillRange = sh2.Range("A2"). _ Resize(numRows).Address(External:=True) End With ActiveWorkbook.Close 'Select Settlement worksheet so this is what the user sees Worksheets("Settlement").Select End Sub You don't say where cmbContract is located, so I am guessing it is in Settlement4.xls on sheet Settlement. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message news PokerDude wrote in message news:ttgVb.245320$xy6.1305484@attbi_s02... Ok, eventually I will get the code right. I think I am closer than ever before with your help (if you are ever in Pittsburgh... I owe you a beer). Thanks again.. Mike The problem now is it is copying the contents of the wrong worksheet. It copies data from workbook Settlement.xls, sheet Settlement (first sheet in workbook), instead of workbook Contracts1.xls, sheet Sheet1. Plus it doesnt like my code for the combobox. What am I doing wrong again? The code you gave me earlier this week worked perfect with a command button. But putting it in the Workbook_Open event it doesnt want to work correctly. What is the reason for this? Private Sub Workbook_Open() Dim numRows As Long 'Refresh data of Contracts worksheet in C:\CCF\Settlement4.xls from data contained 'in worksheet Sheet1 in C:\CCF\Contracts1.xls Workbooks.Open Filename:="C:\CCF\Contracts1.xls" With ThisWorkbook.Worksheets("Contracts") 'C:\CCF\Settlement4.xls 'Determine number of rows in C:\CCF\Contracts1.xls Sheet1 numRows = Application.CountA(ActiveSheet.Range("A:A")) 'Copy from C:\CCF\Contracts1.xls!Sheet1 and 'Paste in C:\CCF\Settlement4.xls!Contracts ActiveSheet.Range("A1:AI" & numRows).Copy .Range("A1") 'modify combobox properties to update listfillrange cmbContracts.ListFillRange = "Contracts!A2:C" & numRows End With ActiveWorkbook.Close 'Select Settlement worksheet so this is what the user sees Worksheets("Settlement").Select End Sub "Tom Ogilvy" wrote in message ... I had a second look at your code and I notices you do With With ThisWorkbook.Worksheets("Sheet2") . . . copysomething to .Range("Contracts!A1") If contracts is the sheet where you want the data copied to you should use With ThisWorkbook.Worksheets("Contracts") . . . copysomething to .Range("A1") -- Regards, Tom Ogilvy Tom Ogilvy wrote in message ... The obvious answer is you no longer have a sheet named Sheet1. Your note says refresh the data on Sheet2, so I susptect that is the case. Change the name to sheet2 Private Sub Workbook_Open() Dim numRows As Long 'Refresh data of Sheet2 Workbooks.Open Filename:="C:\CCF\Contracts1.xls" With ThisWorkbook.Worksheets("Sheet2") 'Determine number of rows numRows = Application.CountA(ActiveSheet.Range("A:A")) ActiveSheet.Range("A1:AI" & numRows).Copy _ .Range("Contracts!A1") End With ActiveWorkbook.Close SaveChanges:=False ' this may be your next error - you probably need to qualify this cmbContracts.ListFillRange = "Contracts!A2:C" & numRows End Sub -- Regards, Tom Ogilvy Squid wrote in message ... The follow code worked when I had it in a CommandButton_Click Event (used that event for testing purposes). But when I transferred the code to the Workbook_Open Event, I receive a "Run-Time error '9': Subscript out of range" at "With ThisWorkbook... " Why?? TIA Mike Private Sub Workbook_Open() Dim numRows As Long 'Refresh data of Sheet2 Workbooks.Open Filename:="C:\CCF\Contracts1.xls" With ThisWorkbook.Worksheets("Sheet1") 'Determine number of rows numRows = Application.CountA(ActiveSheet.Range ("A:A")) ActiveSheet.Range("A1:AI" & numRows).Copy .Range ("Contracts!A1") End With ActiveWorkbook.Close cmbContracts.ListFillRange = "Contracts!A2:C" & numRows End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open Event
Tom, Thanks again for all your help. If you are ever in Pittsburgh, I think
I owe you a beer or two! "Tom Ogilvy" wrote in message ... Private Sub Workbook_Open() Dim numRows As Long Dim sh1 as Worksheet Dim sh2 as Worksheet 'Refresh data of Contracts worksheet in C:\CCF\Settlement4.xls from data contained 'in worksheet Sheet1 in C:\CCF\Contracts1.xls Workbooks.Open Filename:="C:\CCF\Contracts1.xls" set sh1 = Workbooks("Contracts1.xls").Worksheets("Sheet1") set sh2 = Workbooks("Settlement4.xls").Worksheets("Contracts ") 'Determine number of rows in C:\CCF\Contracts1.xls Sheet1 numRows = Application.CountA(sh1.Range("A:A")) 'Copy from C:\CCF\Contracts1.xls!Sheet1 and 'Paste in C:\CCF\Settlement4.xls!Contracts sh1.Range("A1:AI" & numRows).Copy sh2.Range("A1") 'modify combobox properties to update listfillrange sh2.Parent.Worksheets("Settlement").cmbContracts. _ ListFillRange = sh2.Range("A2"). _ Resize(numRows).Address(External:=True) End With ActiveWorkbook.Close 'Select Settlement worksheet so this is what the user sees Worksheets("Settlement").Select End Sub You don't say where cmbContract is located, so I am guessing it is in Settlement4.xls on sheet Settlement. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message news PokerDude wrote in message news:ttgVb.245320$xy6.1305484@attbi_s02... Ok, eventually I will get the code right. I think I am closer than ever before with your help (if you are ever in Pittsburgh... I owe you a beer). Thanks again.. Mike The problem now is it is copying the contents of the wrong worksheet. It copies data from workbook Settlement.xls, sheet Settlement (first sheet in workbook), instead of workbook Contracts1.xls, sheet Sheet1. Plus it doesnt like my code for the combobox. What am I doing wrong again? The code you gave me earlier this week worked perfect with a command button. But putting it in the Workbook_Open event it doesnt want to work correctly. What is the reason for this? Private Sub Workbook_Open() Dim numRows As Long 'Refresh data of Contracts worksheet in C:\CCF\Settlement4.xls from data contained 'in worksheet Sheet1 in C:\CCF\Contracts1.xls Workbooks.Open Filename:="C:\CCF\Contracts1.xls" With ThisWorkbook.Worksheets("Contracts") 'C:\CCF\Settlement4.xls 'Determine number of rows in C:\CCF\Contracts1.xls Sheet1 numRows = Application.CountA(ActiveSheet.Range("A:A")) 'Copy from C:\CCF\Contracts1.xls!Sheet1 and 'Paste in C:\CCF\Settlement4.xls!Contracts ActiveSheet.Range("A1:AI" & numRows).Copy .Range("A1") 'modify combobox properties to update listfillrange cmbContracts.ListFillRange = "Contracts!A2:C" & numRows End With ActiveWorkbook.Close 'Select Settlement worksheet so this is what the user sees Worksheets("Settlement").Select End Sub "Tom Ogilvy" wrote in message ... I had a second look at your code and I notices you do With With ThisWorkbook.Worksheets("Sheet2") . . . copysomething to .Range("Contracts!A1") If contracts is the sheet where you want the data copied to you should use With ThisWorkbook.Worksheets("Contracts") . . . copysomething to .Range("A1") -- Regards, Tom Ogilvy Tom Ogilvy wrote in message ... The obvious answer is you no longer have a sheet named Sheet1. Your note says refresh the data on Sheet2, so I susptect that is the case. Change the name to sheet2 Private Sub Workbook_Open() Dim numRows As Long 'Refresh data of Sheet2 Workbooks.Open Filename:="C:\CCF\Contracts1.xls" With ThisWorkbook.Worksheets("Sheet2") 'Determine number of rows numRows = Application.CountA(ActiveSheet.Range("A:A")) ActiveSheet.Range("A1:AI" & numRows).Copy _ .Range("Contracts!A1") End With ActiveWorkbook.Close SaveChanges:=False ' this may be your next error - you probably need to qualify this cmbContracts.ListFillRange = "Contracts!A2:C" & numRows End Sub -- Regards, Tom Ogilvy Squid wrote in message ... The follow code worked when I had it in a CommandButton_Click Event (used that event for testing purposes). But when I transferred the code to the Workbook_Open Event, I receive a "Run-Time error '9': Subscript out of range" at "With ThisWorkbook... " Why?? TIA Mike Private Sub Workbook_Open() Dim numRows As Long 'Refresh data of Sheet2 Workbooks.Open Filename:="C:\CCF\Contracts1.xls" With ThisWorkbook.Worksheets("Sheet1") 'Determine number of rows numRows = Application.CountA(ActiveSheet.Range ("A:A")) ActiveSheet.Range("A1:AI" & numRows).Copy .Range ("Contracts!A1") End With ActiveWorkbook.Close cmbContracts.ListFillRange = "Contracts!A2:C" & numRows End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open Event
Tom, Thanks again for all your help. If you are ever in Pittsburgh, I think
I owe you a beer or two! "Tom Ogilvy" wrote in message ... Private Sub Workbook_Open() Dim numRows As Long Dim sh1 as Worksheet Dim sh2 as Worksheet 'Refresh data of Contracts worksheet in C:\CCF\Settlement4.xls from data contained 'in worksheet Sheet1 in C:\CCF\Contracts1.xls Workbooks.Open Filename:="C:\CCF\Contracts1.xls" set sh1 = Workbooks("Contracts1.xls").Worksheets("Sheet1") set sh2 = Workbooks("Settlement4.xls").Worksheets("Contracts ") 'Determine number of rows in C:\CCF\Contracts1.xls Sheet1 numRows = Application.CountA(sh1.Range("A:A")) 'Copy from C:\CCF\Contracts1.xls!Sheet1 and 'Paste in C:\CCF\Settlement4.xls!Contracts sh1.Range("A1:AI" & numRows).Copy sh2.Range("A1") 'modify combobox properties to update listfillrange sh2.Parent.Worksheets("Settlement").cmbContracts. _ ListFillRange = sh2.Range("A2"). _ Resize(numRows).Address(External:=True) End With ActiveWorkbook.Close 'Select Settlement worksheet so this is what the user sees Worksheets("Settlement").Select End Sub You don't say where cmbContract is located, so I am guessing it is in Settlement4.xls on sheet Settlement. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message news PokerDude wrote in message news:ttgVb.245320$xy6.1305484@attbi_s02... Ok, eventually I will get the code right. I think I am closer than ever before with your help (if you are ever in Pittsburgh... I owe you a beer). Thanks again.. Mike The problem now is it is copying the contents of the wrong worksheet. It copies data from workbook Settlement.xls, sheet Settlement (first sheet in workbook), instead of workbook Contracts1.xls, sheet Sheet1. Plus it doesnt like my code for the combobox. What am I doing wrong again? The code you gave me earlier this week worked perfect with a command button. But putting it in the Workbook_Open event it doesnt want to work correctly. What is the reason for this? Private Sub Workbook_Open() Dim numRows As Long 'Refresh data of Contracts worksheet in C:\CCF\Settlement4.xls from data contained 'in worksheet Sheet1 in C:\CCF\Contracts1.xls Workbooks.Open Filename:="C:\CCF\Contracts1.xls" With ThisWorkbook.Worksheets("Contracts") 'C:\CCF\Settlement4.xls 'Determine number of rows in C:\CCF\Contracts1.xls Sheet1 numRows = Application.CountA(ActiveSheet.Range("A:A")) 'Copy from C:\CCF\Contracts1.xls!Sheet1 and 'Paste in C:\CCF\Settlement4.xls!Contracts ActiveSheet.Range("A1:AI" & numRows).Copy .Range("A1") 'modify combobox properties to update listfillrange cmbContracts.ListFillRange = "Contracts!A2:C" & numRows End With ActiveWorkbook.Close 'Select Settlement worksheet so this is what the user sees Worksheets("Settlement").Select End Sub "Tom Ogilvy" wrote in message ... I had a second look at your code and I notices you do With With ThisWorkbook.Worksheets("Sheet2") . . . copysomething to .Range("Contracts!A1") If contracts is the sheet where you want the data copied to you should use With ThisWorkbook.Worksheets("Contracts") . . . copysomething to .Range("A1") -- Regards, Tom Ogilvy Tom Ogilvy wrote in message ... The obvious answer is you no longer have a sheet named Sheet1. Your note says refresh the data on Sheet2, so I susptect that is the case. Change the name to sheet2 Private Sub Workbook_Open() Dim numRows As Long 'Refresh data of Sheet2 Workbooks.Open Filename:="C:\CCF\Contracts1.xls" With ThisWorkbook.Worksheets("Sheet2") 'Determine number of rows numRows = Application.CountA(ActiveSheet.Range("A:A")) ActiveSheet.Range("A1:AI" & numRows).Copy _ .Range("Contracts!A1") End With ActiveWorkbook.Close SaveChanges:=False ' this may be your next error - you probably need to qualify this cmbContracts.ListFillRange = "Contracts!A2:C" & numRows End Sub -- Regards, Tom Ogilvy Squid wrote in message ... The follow code worked when I had it in a CommandButton_Click Event (used that event for testing purposes). But when I transferred the code to the Workbook_Open Event, I receive a "Run-Time error '9': Subscript out of range" at "With ThisWorkbook... " Why?? TIA Mike Private Sub Workbook_Open() Dim numRows As Long 'Refresh data of Sheet2 Workbooks.Open Filename:="C:\CCF\Contracts1.xls" With ThisWorkbook.Worksheets("Sheet1") 'Determine number of rows numRows = Application.CountA(ActiveSheet.Range ("A:A")) ActiveSheet.Range("A1:AI" & numRows).Copy .Range ("Contracts!A1") End With ActiveWorkbook.Close cmbContracts.ListFillRange = "Contracts!A2:C" & numRows End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
workbook_Open event Procedure | Excel Discussion (Misc queries) | |||
workbook_Open event Procedure | Excel Discussion (Misc queries) | |||
Workbook_Open() Event | Excel Discussion (Misc queries) | |||
OnTime event not firing in Workbook_Open event procedure | Excel Programming | |||
Workbook_Open event not working | Excel Programming |