PDA

View Full Version : Print all invoices macro



jqagsweb
10-31-2016, 06:36 AM
I have a workbook with two worksheets Sheet1 and Invoices.

Everyday we get a spreadsheet of ecommerce orders, one order per line.

I have a macro that imports it into the Sheet1 worksheet
then I have a macro I am trying to get working that loads each order one at a time into the invoice page and prints all orders.

I have 2 major issues
Data not loading into invoice (a deal killer)
Looping to the next order when the first one is finished

The info below is as far as Ive gotten and Im stuck. Any assistance is much appreciated.
The oeder number is in column G on Sheet1 and upon import, column A is created with id numbers if that helps any,

Sub PrintInvoices()
'Fill out INVOICE template and print with current active printer
Dim LR As Long, NR As Long, i As Long
Dim Cell As Range, Rng As Range
Dim ArrStrings


'Setup print area
With Sheets("Invoice").PageSetup
.PrintArea = "$A$1:$F$43"
.PrintTitleRows = ""
.PrintTitleColumns = ""
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlPortrait
.Draft = False
End With


Sheets("Sheet1").Activate
LR = Range("G" & Rows.Count).End(xlUp).Row
Set Rng = Sheets("Sheet1").Range("A2:AO58" & LR)


For Each Cell In Rng
With Sheets("Invoice")
.Range("D2").Value = Cells(Cell.Row, "G").Value 'ORDER NUMBER
.Range("orderDate").Value = Cells(Cell.Row, "Y").Value 'ORDER DATE
.Range("C7").Value = Cells(Cell.Row, "AH").Value 'SHIP NAME
.Range("C8").Value = Cells(Cell.Row, "AJ").Value 'SHIP STREET
.Range("C9").Value = Cells(Cell.Row, "AK").Value 'SHIP CITY
.Range("C10").Value = Cells(Cell.Row, "AI").Value 'SHIP PHONE
.Range("C13").Value = Cells(Cell.Row, "H").Value 'SHIP CO
.Range("C14").Value = Cells(Cell.Row, "I").Value 'SHIP METHOD
.Range("E7").Value = Cells(Cell.Row, "B").Value 'PURCHASED BY
.Range("E8").Value = Cells(Cell.Row, "C").Value 'PURCHASE PHONE
.Range("B17").Value = Cells(Cell.Row, "N").Value 'ORDER QTY
.Range("C17").Value = Cells(Cell.Row, "M").Value 'ORDER SKU
.Range("D17").Value = Cells(Cell.Row, "W").Value 'ORDER DESC
.Range("E17").Value = Cells(Cell.Row, "Q").Value 'ORDER PRICE
.Range("F25").Value = Cells(Cell.Row, "S").Value 'ORDER SHIPPING

.PrintOut copies:=1
.Range("D2,C7,C8,C9,C10,C13,C14,E7,E8,B17,C17,D17,E17,F25,orderDate").ClearContents
End With
Next Cell
End Sub

mana
10-31-2016, 07:11 AM
LR = Range("G" & Rows.Count).End(xlUp).Row

With Sheets("Invoice")
For i = 2 To LR
.Range("D2").Value = Cells(i, "G").Value 'ORDER NUMBER
.Range("orderDate").Value = Cells(i, "Y").Value 'ORDER DATE



.PrintOut copies:=1
.Range("D2,C7,C8,C9,C10,C13,C14,E7,E8,B17,C17,D17,E17,F25,orderDate").Clear Contents
Next i
End With