I can't get this to work

[E-Town]

I get a runtime error 9 but it should work, its worked in the past.

--------------------------------------------------------------------------------------------

Dim CDetail As String
CDetail = "ClaimsDetail.xls"
Msg1 = "Enter the NDC_Quarter (Excel file name)"
Dfl1 = "00065-0266-25_20041"
MyInput1 = InputBox(Msg1, , Dfl1)
If MyInput1 = Cancel Then GoTo LastLine


Workbooks.Open ("W:\Exports\" & MyInput1 & ".xls")
Workbooks(CDetail).Activate

c = 1
Do While Windows(MyInput1).RangeSelection(1, c) <> 0
If Windows(MyInput1).RangeSelection(1, c) = "orig_qtr_pd" Then c1 = c
If Windows(MyInput1).RangeSelection(1, c) = "dte_quarter" Then c2 = c
If Windows(MyInput1).RangeSelection(1, c) = "id_provider" Then c3 = c
If Windows(MyInput1).RangeSelection(1, c) = "dte_dispensed" Then c4
= c
If Windows(MyInput1).RangeSelection(1, c) = "cde_icn" Then c5 = c
If Windows(MyInput1).RangeSelection(1, c) = "num_prescrip" Then c6 = c
If Windows(MyInput1).RangeSelection(1, c) = "qty_units_billed" Then
c7 = c
If Windows(MyInput1).RangeSelection(1, c) = "hcfa_units" Then c8 = c
If Windows(MyInput1).RangeSelection(1, c) = "amt_alwd" Then c9 = c
If Windows(MyInput1).RangeSelection(1, c) = "amt_reimbursement" Then
c10 = c
c = c + 1
Loop

r = 8
Do While Windows(MyInput1).RangeSelection(r - 6, 1) <> 0
Cells(r, 1) = Left(Windows(MyInput1).RangeSelection(r - 6, c1), 4) &
"/" & Right(Windows(MyInput1).RangeSelection(r - 6, c1), 1)
Cells(r, 2) = Left(Windows(MyInput1).RangeSelection(r - 6, c2), 4) &
"/" & Right(Windows(MyInput1).RangeSelection(r - 6, c2), 1)
Cells(r, 3) = Windows(MyInput1).RangeSelection(r - 6, c3)
Cells(r, 4) = Left(Windows(MyInput1).RangeSelection(r - 6, c4), 4) &
"/" & Right(Left(Windows(MyInput1).RangeSelection(r - 6, c4), 6), 2) & "/" &
Right(Windows(MyInput1).RangeSelection(r - 6, c4), 2)
Cells(r, 5) = Windows(MyInput1).RangeSelection(r - 6, c5)
Cells(r, 6) = Windows(MyInput1).RangeSelection(r - 6, c6)
Cells(r, 7) = Windows(MyInput1).RangeSelection(r - 6, c7)
Cells(r, 8) = Windows(MyInput1).RangeSelection(r - 6, c8)
Cells(r, 11) = Windows(MyInput1).RangeSelection(r - 6, c9)
Cells(r, 12) = Windows(MyInput1).RangeSelection(r - 6, c10)
r = r + 1
Loop

Workbooks(MyInput1).Close
Cells(1, 1) = "W:\ClaimsDetail"
Cells(2, 1) = MyInput1 & "_OK.xls"
Cells(1, 12) = Date
Cells(2, 12) = Time
Cells(5, 1) = "NDC: " & Left(MyInput1, 13)
Cells(6, 1) = "Quarter: " & Cells(8, 1)
Cells(r + 1, 6) = "Total:"
Cells(r + 1, 7) = "=Sum(G8:G" & r - 1 & ")"
Cells(r + 1, 8) = "=Sum(H8:H" & r - 1 & ")"
Cells(r + 1, 11) = "=Sum(K8:K" & r - 1 & ")"
Cells(r + 1, 12) = "=Sum(L8:L" & r - 1 & ")"
Range(Cells(r + 1, 6), Cells(r + 1, 12)).Font.Bold = True
Range(Cells(r + 1, 6), Cells(r + 1, 12)).Borders(xlEdgeTop).LineStyle =
xlContinuous

Msg3 = "Do you want to save this Excel file?"
Stl3 = vbYesNo
Message = MsgBox(Msg3, Stl3)
If Message = vbYes Then
Workbooks("ClaimsDetail").SaveCopyAs "W:\ClaimsDetail\" & MyInput1 &
"_OK.xls"
End If
Windows("ClaimsDetail").Close SaveChanges:=False
LastLine:
End Sub

 

[Jim Thomlinson]

Which line generates the error...

 

[E-Town]

Sorry about that, its this one:


-----------------
Workbooks(CDetail).Activate
-----------------

I've tried Windows(CDetail).Activate but same result.
There's a known issue with default printers but nobody can give me a fix, so
I thought there might be a coding workaround.

thanks,

 

[Jim Thomlinson]

Do you have a file called ClaimsDetail.xls open when this line of code
executes? Is ClaimsDetail.xls the workbook that is executing the code?

 

[Dave Peterson]

That means there is no open workbook named ClaimsDetail.xls.

Maybe it's a simple typo???