Auto Copy > Paste Values

**evamurphy** · 06-14-2016, 06:57 PM

Hiya,

Not sure if I am in the right sub-forum but I will give it a crack.

I do processing for an insurance company. When we process new clients, we have to allocate them an original policy number. We also have to provide them with a series of documents that contain information specific to the client (including the policy number).

To do this, we have two workbooks.
POLICY NUMBERS TO ALLOCATE.xlxs is used to find and allocate unused policy numbers. Column B contains the policy numbers, and column C is where you enter in the corresponding client name.
DOCUMENTATION ENTRY SHEET.xlxs is the template we use to produce the required documentation. We enter the required information into the first tab, then print PDFs of the other 5 sheets. The allocated policy number is entered into F8 on Sheet1. We save a completed workbook template under each client name so we can go back and edit/reproduce documentation when necessary.

Currently we are manually allocating the policy number in POLICY NUMBERS TO ALLOCATE.xlxs, then we are manually entering the number into DOCUMENTATION ENTRY SHEET.xlxs (F8).

In order to automatically find the next available policy number and copy it into F8, I have entered the function =INDEX('POLICY NUMBERS TO ALLOCATE'!$B:$B,COUNTA('POLICY NUMBERS TO ALLOCATE'!$C:$C)) into (F8) DOCUMENTATION ENTRY SHEET.xlxs. This is working, however it continues to update. If the next available number is 12345, then every individually saved completed template that contains the above formula will produce 12345 in F8.

IMO, the most basic solution to this is to manually Copy > Paste Values. However, having to remember to do this increases the margin for error and will most likely lead to multiple clients being allocated the same number.

Is there any way I can make it so F8 automatically Pastes Values? Or perhaps produce an error message if the user has not done so themselves?

Thank you in advance

**mehmetcik** · 06-15-2016, 04:04 PM

A simple Macro will do this for you however you have not explained how you update the workbook "POLICY NUMBERS TO ALLOCATE.xlxs".

I have made some assumptions:

This Macro may do the trick


Sub Test()

customer = InputBox("Customer Name:  Surname - Firstname ", "Customer Name?")
If customer = "" Then Exit Sub

Application.ScreenUpdating = False
Windows("POLICY NUMBERS TO ALLOCATE.xlsx").Activate
With Sheets("Policies")
    Lr = .Cells(Rows.Count, 3).End(xlUp).Row + 1
Policy = .Cells(Lr, 2).Value
.Cells(Lr, 3).Value = customer
.Cells(Lr, 4).Value = Date
End With

ActiveWorkbook.Save

Windows("Document Entry.xlsm").Activate

Application.ScreenUpdating = True

Range("F8").Value = Policy

For i = 1 To 5
    Sheets(i).ExportAsFixedFormat Type:=xlTypePDF, Filename:=customer & Replace(Date, "/", "-") & " - " & i & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
Next

End Sub

Both Workbooks must be open