Runtime Error 9 Help

[jdgalaway]

For some reason I'm getting a Runtime Error 9 in my code. I can't figure out why. Any help would be appreciated.





Sub importPolicy()
Dim riskAssess As Variant
riskAssess = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLSX;*.XLSM;*.XLS), *.XLSX;*.XLSM;*.XLS", Title:="Select Portfolio Risk Assessment")
If riskAssess = False Then Exit Sub
Workbooks.Open Filename:=riskAssess

Dim wbkTemp As Workbook
Set wbkTemp = Workbooks("PerPolicyTemplate.xlsm")

Dim numRows As Integer

' Set numrows = number of rows of data.
numRows = Range("B4", Range("B4").End(xlDown)).Rows.Count <------ Causing Error!

Workbooks(riskAssess).Sheets("Detailed Summary").Cells("B4").Select
' Establish "For" loop to loop "numrows" number of times.

Dim i As Integer
For i = 1 To numRows
' Insert your code here.


Application.Wait (Now + #12:00:02 AM#

Next

riskAssess.Close savechanges:=False

Dim mBox As Variant

mBox = InputBox(Prompt:="Save As:", Title:="Save As:", Default:="Save As:")

wbkTemp.SaveAs Filename:=mBox

End Sub

[Bob Phillips]

Not tested


Sub importPolicy()
Dim riskAssess As Variant
riskAssess = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLSX;*.XLSM;*.XLS), *.XLSX;*.XLSM;*.XLS", Title:="Select Portfolio Risk Assessment")
If riskAssess = False Then Exit Sub
Workbooks.Open Filename:=riskAssess

Dim wbkTemp As Workbook
Set wbkTemp = Workbooks("PerPolicyTemplate.xlsm")

Dim numRows As Integer

' Set numrows = number of rows of data.
numRows = Range("B4", Range("B4").End(xlDown)).Rows.Count

ActiveWorkbook.Sheets("Detailed Summary").Activate
Cells("B4").Select
' Establish "For" loop to loop "numrows" number of times.

Dim i As Integer
For i = 1 To numRows
' Insert your code here.


Application.Wait (Now + #12:00:02 AM#)

Next

riskAssess.Close savechanges:=False

Dim mBox As Variant

mBox = InputBox(Prompt:="Save As:", Title:="Save As:", Default:="Save As:")

wbkTemp.SaveAs Filename:=mBox
End Sub

[jdgalaway]

Worked! Thanks!

[snb]

Sub importPolicy()
With Workbooks.Open(Application.GetOpenFilename("Excel Files (*.XLSX;*.XLSM;*.XLS), *.XLSX;*.XLSM;*.XLS", , "Select Portfolio Risk Assessment"))
sn = .Sheets("Detailed Summary").Cells(1).CurrentRegion
.Close 0
End With

With Workbooks("PerPolicyTemplate.xlsm")
sq = .Sheets(1).Cells(1).CurrentRegion

For j = 4 To UBound(sn)
' your code
Next

.SaveAs Application.GetSaveAsFilename(, "Excel Files (*.XLSX;*.XLSM;*.XLS), *.XLSX;*.XLSM;*.XLS", , "snb")
End With
End Sub