Pasar datos automáticamente de unos datos a una plantilla: añadir el pie.
A la plantilla, añadir los datos del "pie".
Envío el fichero por correo.
Respuesta de Dante Amor
1
¡Gracias!
Te anexo la macro completa con el "pie"
Sub InvoiceNumber()
'Por.Dante Amor
Set h1 = Sheets("datos")
Set h2 = Sheets("ej1")
Set h3 = Sheets("plantilla")
h2.Cells.Clear
'
j = 1
i = 2
'
ant = h1.Cells(2, "F")
encabezado h1, h2, h3, i, j
For i = 2 To h1.Range("F" & Rows.Count).End(xlUp).Row
If ant <> h1.Cells(i, "f") Then
'j = j + 1
pie h1, h2, h3, i - 1, j
encabezado h1, h2, h3, i, j
End If
ant = h1.Cells(i, "F")
productos h1, h2, h3, i, j
Next
pie h1, h2, h3, i - 1, j
h2.Select
MsgBox "Proceso terminado", vbInformation, "PLANTILLA"
End Sub
Sub encabezado(h1, h2, h3, i, j)
'Por.Dante Amor
h3.Rows(1 & ":" & 3).Copy h2.Rows(j)
'
h2.Cells(j, "B") = h1.Cells(i, "F") 'invoice num
h2.Cells(j, "C") = h1.Cells(i, "J") 'invoice date
h2.Cells(j, "D") = h1.Cells(i, "G") 'CURRENCY
h2.Cells(j, "E") = h1.Cells(i, "AB") 'PO
h2.Cells(j, "F") = h1.Cells(i, "O") 'PAYMENT_TERM
j = j + 1
h2.Cells(j, "B") = h1.Cells(i, "A") 'BILL_TO_CUSTOMER_NAME
h2.Cells(j, "C") = h1.Cells(i, "Y") 'VAT NUMNER PURCHASING COMPANY
j = j + 1
h2.Cells(j, "B") = h1.Cells(i, "Z") 'SELLER
h2.Cells(j, "C") = h1.Cells(i, "AA") 'SELLER VAT
j = j + 1
End Sub
Sub productos(h1, h2, h3, i, j)
'Por.Dante Amor
h3.Rows(4 & ":" & 6).Copy h2.Rows(j)
'
h2.Cells(j, "C") = h1.Cells(i, "P") 'DESCRIPTION
j = j + 1
h2.Cells(j, "C") = h1.Cells(i, "K") 'QUANTITY_INVOICED
h2.Cells(j, "D") = h1.Cells(i, "L") 'UNIT_SELLING_PRICE
'
If h1.Cells(i, "U") = 0 Then
Select Case h1.Cells(i, "AE")
Case "Spain": texto = "Exento"
Case "UK": texto = "Vat exempt"
Case "Germany": texto = "Vat exempt"
End Select
Else
Select Case h1.Cells(i, "AE")
Case "Spain": texto = "IVA"
Case "UK": texto = "Standard rate"
Case "Germany": texto = "Standard rate"
End Select
End If
'
h2.Cells(j, "J") = texto 'text
h2.Cells(j, "K") = h1.Cells(i, "U") 'TAX_PERCENTAGE
j = j + 2
End Sub
Sub pie(h1, h2, h3, i, j)
'Por.Dante Amor
h3.Rows(22 & ":" & 27).Copy h2.Rows(j)
'
h2.Cells(j, "B") = h1.Cells(i, "X") 'END CUSTOMER NAME
j = j + 1
h2.Cells(j, "B") = h1.Cells(i, "E") 'SHIP_TO_ADDRESS
j = j + 1
h2.Cells(j, "B") = h1.Cells(i, "AC") 'INVOICE TYPE
j = j + 1
h2.Cells(j, "B") = h1.Cells(i, "A") 'BILL_TO_CUSTOMER_NAME
j = j + 1
h2.Cells(j, "B") = h1.Cells(i, "B") 'BILL_TO_CUSTOMER_NUMBER
j = j + 1
h2.Cells(j, "B") = h1.Cells(i, "AD") 'BID
j = j + 1
End Sub
