One Variable Collect Duplicate Range: Automatically Change Content Based on Selection Cell

To achieve your requirement, you can use VBA (Visual Basic for Applications) in Excel to automate the process. Here's a step-by-step guide on how to create a VBA macro for your specific use case:

1. Open your Excel workbook.

2. Press Alt + F11 to open the VBA editor.

3. In the VBA editor, go to Insert > Module to create a new module.

4. Copy and paste the following code into the module:

Sub SendInvoice()
    Dim wsSheet1 As Worksheet
    Dim wsSheet2 As Worksheet
    Dim rng As Range
    Dim strCellName As String

    ' Set the worksheets
    Set wsSheet1 = ThisWorkbook.Sheets("Sheet1")
    Set wsSheet2 = ThisWorkbook.Sheets("Sheet2")

    ' Find the cell with the name you want to send in the invoice
    Set rng = wsSheet1.Range("A1:Z1").Find(What:="*", After:=wsSheet1.Range("Z1"), LookIn:=xlValues, LookAt:=xlWhole)
    If rng Is Nothing Then
        MsgBox "Cell not found!"
        Exit Sub
    End If
    strCellName = rng.Value

    ' Set the cell in the invoice sheet
    wsSheet2.Range("A1").Value = strCellName

    ' Print the invoice sheet
    wsSheet2.PrintOut

    ' Clear the cell in the invoice sheet
    wsSheet2.Range("A1").ClearContents
End Sub

5. Close the VBA editor.

6. Go back to the Excel sheet, and assign a macro to a button or shortcut key:

   • To assign a macro to a button: a. Click the Developer tab. b. In the Controls group, click Insert. c. Select the button type you want, then click where you want the button to be placed on the sheet. d. In the Assign Macro dialog box, select the macro you just created, then click OK.

   • To assign a macro to a shortcut key: a. Press Alt + F8. b. Select the macro you just created, then click Shortcut Key. c. Press the key combination you want to use, then click OK.

Now, whenever you want to send an invoice, select the cell with the name you want to include in the invoice on Sheet1, and run the macro. The selected cell's value will be moved to cell A1 on Sheet2, the invoice will be printed, and the value will be cleared from cell A1 on Sheet2.