sg2209
01-22-2018, 11:37 PM
Hi Friends ,
i have written a code where i need the data to be picked up from sheet DATA and filling specific cells in Sheet 1 " INS REFUND CHECK REQ" now i have a data in Sheet from Column A to AI and there are 15000 Rows in it , my code is works only when i enter start records 1 and end record 1 , again for 2 i need to enter Start Row 2 and End record 2 , Could anyone please help me that it would not ask me for the records again and again , below is my code .
Sub PrintLetters()
Dim StartRow As Integer, EndRow As Integer, rowindex As Integer
Dim Msg As String
Dim TotalRecords As String
Dim mydate As Date
Dim Customer As String, CustomerName As String, Account As String, Invoice As String, Primary_Secondary As String, Payer As String, Reason As String, DOS As String, Payable As String, Attention As String, Address As String, City As String, State As String, Zip As String, Refund As String, Attach_EOB As String
Dim Forms As String, Requester As String, Supervisor As String, Approval_Signature As String, Birth_Date As String, Subscriber_ID As String, Claim As String, Check As String, Check_date As String, HCPC As String, Refund_Reason As String, Provider_Tax_Id As String, Provider_Phone As String, HRA As String, Inquiry As String, Ref_ID As String, Vendor_No As String
TotalRecords = "=counta(Data!A:A)"
Range("K11") = TotalRecords
mydate = Date
Sheets("INS REFUND CHECK REQ").Range("C6") = mydate
Sheets("INS REFUND CHECK REQ").Range("C6").NumberFormat = "[$-F800]dddd,mmmm,dd,yyyy"
Sheets("INS REFUND CHECK REQ").Range("C6").HorizontalAlignment = xlLeft
StartRow = InputBox("Enter the First Record to Print")
EndRow = InputBox("Enter the Last Record to Print")
If StartRow > EndRow Then
Msg = "ERROR" & vbCrLf & "The starting row must be less than the endng row!"
MsgBox Msg, vbCritical, "Please Contact Sachin"
End If
For i = StartRow To EndRow
Customer = Sheets("DATA").Cells(i, 1)
CustomerName = Sheets("DATA").Cells(i, 2)
Account = Sheets("DATA").Cells(i, 3)
Invoice = Sheets("DATA").Cells(i, 4)
Primary_Secondary = Sheets("DATA").Cells(i, 5)
Payer = Sheets("DATA").Cells(i, 6)
Reason = Sheets("DATA").Cells(i, 7)
DOS = Sheets("DATA").Cells(i, 8)
Payable = Sheets("DATA").Cells(i, 9)
Attention = Sheets("DATA").Cells(i, 10)
Address = Sheets("DATA").Cells(i, 11)
City = Sheets("DATA").Cells(i, 12)
State = Sheets("DATA").Cells(i, 13)
Zip = Sheets("DATA").Cells(i, 14)
Refund = Sheets("DATA").Cells(i, 15)
Attach_EOB = Sheets("DATA").Cells(i, 16)
Forms = Sheets("DATA").Cells(i, 17)
Requester = Sheets("DATA").Cells(i, 18)
Supervisor = Sheets("DATA").Cells(i, 19)
Approval_Signature = Sheets("DATA").Cells(i, 20)
Birth_Date = Sheets("DATA").Cells(i, 21)
Subscriber_ID = Sheets("DATA").Cells(i, 22)
Claim = Sheets("DATA").Cells(i, 23)
Check = Sheets("DATA").Cells(i, 24)
Check_date = Sheets("DATA").Cells(i, 25)
HCPC = Sheets("DATA").Cells(i, 26)
Refund_Reason = Sheets("DATA").Cells(i, 27)
Provider_Tax_Id = Sheets("DATA").Cells(i, 28)
Provider_Phone = Sheets("DATA").Cells(i, 29)
HRA = Sheets("DATA").Cells(i, 30)
Inquiry = Sheets("DATA").Cells(i, 31)
Ref_ID = Sheets("DATA").Cells(i, 32)
Vendor_No = Sheets("DATA").Cells(i, 33)
Sheets("INS REFUND CHECK REQ").Range("C7") = Customer
Sheets("INS REFUND CHECK REQ").Range("F6") = CustomerName
Sheets("INS REFUND CHECK REQ").Range("C8") = Account
Sheets("INS REFUND CHECK REQ").Range("C9") = Invoice
Sheets("INS REFUND CHECK REQ").Range("C10") = Primary_Secondary
Sheets("INS REFUND CHECK REQ").Range("C11") = Payer
Sheets("INS REFUND CHECK REQ").Range("C12") = Reason
Sheets("INS REFUND CHECK REQ").Range("C13") = DOS
Sheets("INS REFUND CHECK REQ").Range("C14") = Payable
Sheets("INS REFUND CHECK REQ").Range("C15") = Attention
Sheets("INS REFUND CHECK REQ").Range("C16") = Address
Sheets("INS REFUND CHECK REQ").Range("C17") = City & "" & State & "" & Zip
Sheets("INS REFUND CHECK REQ").Range("C18") = Refund
Sheets("INS REFUND CHECK REQ").Range("C20") = Attach_EOB
Sheets("INS REFUND CHECK REQ").Range("C21") = Forms
Sheets("INS REFUND CHECK REQ").Range("C24") = Requester
Sheets("INS REFUND CHECK REQ").Range("C25") = Supervisor
Sheets("INS REFUND CHECK REQ").Range("C26") = Approval_Signature
Sheets("INS REFUND CHECK REQ").Range("F7") = Birth_Date
Sheets("INS REFUND CHECK REQ").Range("F9") = Subscriber_ID
Sheets("INS REFUND CHECK REQ").Range("F10") = Claim
Sheets("INS REFUND CHECK REQ").Range("F11") = Check
Sheets("INS REFUND CHECK REQ").Range("F12") = Check_date
Sheets("INS REFUND CHECK REQ").Range("F13") = HCPC
Sheets("INS REFUND CHECK REQ").Range("F15") = Refund_Reason
Sheets("INS REFUND CHECK REQ").Range("F16") = Provider_Tax_Id
Sheets("INS REFUND CHECK REQ").Range("F17") = Provider_Phone
Sheets("INS REFUND CHECK REQ").Range("F20") = HRA
Sheets("INS REFUND CHECK REQ").Range("F21") = Inquiry
Sheets("INS REFUND CHECK REQ").Range("F23") = Vendor_No
Sheets("INS REFUND CHECK REQ").Range("F6") = CustomerName
Sheets("INS REFUND CHECK REQ").Range("F6") = CustomerName
Sheets("INS REFUND CHECK REQ").Range("F6") = CustomerName
Sheets("INS REFUND CHECK REQ").Range("F6") = CustomerName
Sheets("INS REFUND CHECK REQ").Range("F6") = CustomerName
Sheets("INS REFUND CHECK REQ").Range("F6") = CustomerName
checkbox4 = True
If checkbox4 Then
ActiveSheet.PrintPreview
Else
ActiveSheet.PrintOut
End If
Next i
End Sub
i have written a code where i need the data to be picked up from sheet DATA and filling specific cells in Sheet 1 " INS REFUND CHECK REQ" now i have a data in Sheet from Column A to AI and there are 15000 Rows in it , my code is works only when i enter start records 1 and end record 1 , again for 2 i need to enter Start Row 2 and End record 2 , Could anyone please help me that it would not ask me for the records again and again , below is my code .
Sub PrintLetters()
Dim StartRow As Integer, EndRow As Integer, rowindex As Integer
Dim Msg As String
Dim TotalRecords As String
Dim mydate As Date
Dim Customer As String, CustomerName As String, Account As String, Invoice As String, Primary_Secondary As String, Payer As String, Reason As String, DOS As String, Payable As String, Attention As String, Address As String, City As String, State As String, Zip As String, Refund As String, Attach_EOB As String
Dim Forms As String, Requester As String, Supervisor As String, Approval_Signature As String, Birth_Date As String, Subscriber_ID As String, Claim As String, Check As String, Check_date As String, HCPC As String, Refund_Reason As String, Provider_Tax_Id As String, Provider_Phone As String, HRA As String, Inquiry As String, Ref_ID As String, Vendor_No As String
TotalRecords = "=counta(Data!A:A)"
Range("K11") = TotalRecords
mydate = Date
Sheets("INS REFUND CHECK REQ").Range("C6") = mydate
Sheets("INS REFUND CHECK REQ").Range("C6").NumberFormat = "[$-F800]dddd,mmmm,dd,yyyy"
Sheets("INS REFUND CHECK REQ").Range("C6").HorizontalAlignment = xlLeft
StartRow = InputBox("Enter the First Record to Print")
EndRow = InputBox("Enter the Last Record to Print")
If StartRow > EndRow Then
Msg = "ERROR" & vbCrLf & "The starting row must be less than the endng row!"
MsgBox Msg, vbCritical, "Please Contact Sachin"
End If
For i = StartRow To EndRow
Customer = Sheets("DATA").Cells(i, 1)
CustomerName = Sheets("DATA").Cells(i, 2)
Account = Sheets("DATA").Cells(i, 3)
Invoice = Sheets("DATA").Cells(i, 4)
Primary_Secondary = Sheets("DATA").Cells(i, 5)
Payer = Sheets("DATA").Cells(i, 6)
Reason = Sheets("DATA").Cells(i, 7)
DOS = Sheets("DATA").Cells(i, 8)
Payable = Sheets("DATA").Cells(i, 9)
Attention = Sheets("DATA").Cells(i, 10)
Address = Sheets("DATA").Cells(i, 11)
City = Sheets("DATA").Cells(i, 12)
State = Sheets("DATA").Cells(i, 13)
Zip = Sheets("DATA").Cells(i, 14)
Refund = Sheets("DATA").Cells(i, 15)
Attach_EOB = Sheets("DATA").Cells(i, 16)
Forms = Sheets("DATA").Cells(i, 17)
Requester = Sheets("DATA").Cells(i, 18)
Supervisor = Sheets("DATA").Cells(i, 19)
Approval_Signature = Sheets("DATA").Cells(i, 20)
Birth_Date = Sheets("DATA").Cells(i, 21)
Subscriber_ID = Sheets("DATA").Cells(i, 22)
Claim = Sheets("DATA").Cells(i, 23)
Check = Sheets("DATA").Cells(i, 24)
Check_date = Sheets("DATA").Cells(i, 25)
HCPC = Sheets("DATA").Cells(i, 26)
Refund_Reason = Sheets("DATA").Cells(i, 27)
Provider_Tax_Id = Sheets("DATA").Cells(i, 28)
Provider_Phone = Sheets("DATA").Cells(i, 29)
HRA = Sheets("DATA").Cells(i, 30)
Inquiry = Sheets("DATA").Cells(i, 31)
Ref_ID = Sheets("DATA").Cells(i, 32)
Vendor_No = Sheets("DATA").Cells(i, 33)
Sheets("INS REFUND CHECK REQ").Range("C7") = Customer
Sheets("INS REFUND CHECK REQ").Range("F6") = CustomerName
Sheets("INS REFUND CHECK REQ").Range("C8") = Account
Sheets("INS REFUND CHECK REQ").Range("C9") = Invoice
Sheets("INS REFUND CHECK REQ").Range("C10") = Primary_Secondary
Sheets("INS REFUND CHECK REQ").Range("C11") = Payer
Sheets("INS REFUND CHECK REQ").Range("C12") = Reason
Sheets("INS REFUND CHECK REQ").Range("C13") = DOS
Sheets("INS REFUND CHECK REQ").Range("C14") = Payable
Sheets("INS REFUND CHECK REQ").Range("C15") = Attention
Sheets("INS REFUND CHECK REQ").Range("C16") = Address
Sheets("INS REFUND CHECK REQ").Range("C17") = City & "" & State & "" & Zip
Sheets("INS REFUND CHECK REQ").Range("C18") = Refund
Sheets("INS REFUND CHECK REQ").Range("C20") = Attach_EOB
Sheets("INS REFUND CHECK REQ").Range("C21") = Forms
Sheets("INS REFUND CHECK REQ").Range("C24") = Requester
Sheets("INS REFUND CHECK REQ").Range("C25") = Supervisor
Sheets("INS REFUND CHECK REQ").Range("C26") = Approval_Signature
Sheets("INS REFUND CHECK REQ").Range("F7") = Birth_Date
Sheets("INS REFUND CHECK REQ").Range("F9") = Subscriber_ID
Sheets("INS REFUND CHECK REQ").Range("F10") = Claim
Sheets("INS REFUND CHECK REQ").Range("F11") = Check
Sheets("INS REFUND CHECK REQ").Range("F12") = Check_date
Sheets("INS REFUND CHECK REQ").Range("F13") = HCPC
Sheets("INS REFUND CHECK REQ").Range("F15") = Refund_Reason
Sheets("INS REFUND CHECK REQ").Range("F16") = Provider_Tax_Id
Sheets("INS REFUND CHECK REQ").Range("F17") = Provider_Phone
Sheets("INS REFUND CHECK REQ").Range("F20") = HRA
Sheets("INS REFUND CHECK REQ").Range("F21") = Inquiry
Sheets("INS REFUND CHECK REQ").Range("F23") = Vendor_No
Sheets("INS REFUND CHECK REQ").Range("F6") = CustomerName
Sheets("INS REFUND CHECK REQ").Range("F6") = CustomerName
Sheets("INS REFUND CHECK REQ").Range("F6") = CustomerName
Sheets("INS REFUND CHECK REQ").Range("F6") = CustomerName
Sheets("INS REFUND CHECK REQ").Range("F6") = CustomerName
Sheets("INS REFUND CHECK REQ").Range("F6") = CustomerName
checkbox4 = True
If checkbox4 Then
ActiveSheet.PrintPreview
Else
ActiveSheet.PrintOut
End If
Next i
End Sub