Mail Merge Excel however instead of Print Need save as

[sg2209]

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

[georgiboy]

Do you have a column with contiguous data?

is this a block of data in sheet "Data" that always starts in row 2?

[sg2209]

Yes Please data starts from Row 2 when i run a macro my form is getting the data from Row2, then there is a data in Row3 , its upto 15000 sometimes more than 15000. we need Macro to get the data from 1 Row than Auto save , 2nd Row then Auto save upto the last row has data

[georgiboy]

Lets say sheet"Data" was Sheet1 then you could define start and end like this:


StartRow = 2
EndRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

Hope this helps

[georgiboy]

For saving the result sheet as a new workbook you could add a small function like:


Function fn_SaveAs(pth As String)

Dim wb As Workbook
Sheet2.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs pth
.Close False
End With

End Function

and call it in your Sub like:


fn_SaveAs ("C:\Users\Example\" & Customer & ".xlsx")

Hope this helps

[georgiboy]

Try to use the code tags and paste your code in between them, you do this by using the # symbol on the header.

This will make your code easier to read on the forum and may get you a response faster.

Hope this helps

[sg2209]

Should i edit this line

[StartRow = InputBox("Enter the First Record to Print")]
[EndRow = InputBox("Enter the Last Record to Print")]

or i need to paste it somewhere please assist

[georgiboy]

I have provided (above) a method for defining start and end row as well as the function to save the sheet as a new workbook.

You need to work the above code into your code to make it work.

The function will need to be pasted outside of the Sub.

[georgiboy]

Yes them lines.

if you replace them lines with mine but make sure it is pointing to a column of contiguous data on the “Data” sheet.