Need help with formula

Thank you Matt, this works as I needed. :-)

Try using this instead.  I cleaned up the code for you.  Typically you don't want to use ActiveCell or Activate or Select .  You can work with worksheet objects directly (see below):

Private Sub CommandButton1_Click()

    Dim InvoiceNumber    As Long
    Dim CustomerName     As String
    Dim InvoiceDate      As String
    Dim SampleNumber     As String
    Dim AmountInvoice    As String
    Dim shtInv           As Worksheet
    Dim shtInvList       As Worksheet
    Dim InvListLRow      As Long
    
    'Define worksheets
    Set shtInv = Worksheets("Invoices")
    Set shtInvList = Worksheets("Invoice List")
    
    'Pass values to variables
    InvListLRow = shtInvList.Cells(shtInvList.Rows.Count, "A").End(xlUp).Row 'Define last occupied row on Invoice List Sheet
    InvoiceNumber = shtInv.Range("J11")
    CustomerName = shtInv.Range("B1") ' I think this is the wrong cell reference...may want to check
    InvoiceDate = shtInv.Range("I9")
    SampleNumber = shtInv.Range("I9")
    AmountInvoice = shtInv.Range("J27")

    With shtInvList
        .Range("A" & InvListLRow + 1) = InvoiceNumber
        .Range("B" & InvListLRow + 1) = CustomerName  'This had a typo...
        .Range("C" & InvListLRow + 1) = InvoiceDate
        .Range("D" & InvListLRow + 1) = SampleNumber
        .Range("E" & InvListLRow + 1) = AmountInvoice
    End With
    
    shtInv.Activate 'Activate Invoices Worksheet
    shtInv.PrintOut 'Print
        
End Sub

Hi Matt,

It did not take the data from Invoice Tab to add to the Invoice List Tab :-(

The data from Invoice tab which should be added into the invoice list is Invoice Number, Customer Name, Invoice Date, Sample Number and total amount invoiced incl VAT.

Regards

Adam

It looks like you had your If/End If statement in the wrong place:

   If Worksheets("Invoice_List").Range("A1").Offset(1, 0) <> "" Then
    End If

Try this:

Private Sub CommandButton1_Click()

    Dim InvoiceNumber As Long
    Dim CustomerName As String
    Dim InvoiceDate As String
    Dim SampleNumber As String
    Dim AmountInvoice As String
    
    Worksheets("Invoices").Select
    InvoiceNumber = Range("J11")
    CustomerName = Range("B1")
    InvoiceDate = Range("I9")
    SampleNumber = Range("I9")
    AmountInvoice = Range("J27")
    Worksheets("Invoice_List").Select
    Worksheets("Invoice_List").Range("A1").Select
    
    If Worksheets("Invoice_List").Range("A1").Offset(1, 0) <> "" Then

        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = InvoiceNumber
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = CustomberName
        ActiveCell.Offset(0, 2).Select
        ActiveCell.Value = InvoiceDate
        ActiveCell.Offset(0, 3).Select
        ActiveCell.Value = SampleNumber
        ActiveCell.Offset(0, 4).Select
        ActiveCell.Value = AmountInvoice
        Worksheets("Invoice").Select
        Worksheets("Invoices").Range("J11").Select
        ActiveSheet.PrintOut
        
    End If

End Sub