Fill multiple rows with printing costs

Zest Print Prices


Sub Button1_Click()
'Get lastrow of the Template form
With Worksheets("Template")
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

With Worksheets("££ Print Prices 2016")
lastrowPP = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

'Now we know the last row, go down each row and get the Item code, Print Method and max colours
For r = 2 To lastrowPP

x = Worksheets("££ Print Prices 2016").Range("A" & r) 'Print Method

If x <> "extra cols" Then
Worksheets("££ Print Prices 2016").Range("B" & r & ":U" & r).Copy

printmethodcodeFIND = WorksheetFunction.Match(x, Worksheets("CodeSheet").Range("A:A"), 0) 'Found the row its on
printmethodCODE = Worksheets("Method").Range("A" & printmethodcodeFIND)
printmethodNAME = Worksheets("Method").Range("B" & printmethodcodeFIND)

'Paste the figures into each Method 1 colour zone
For MAINr = 5 To lastrow
If Worksheets("Template").Range("G" & MAINr) = "Screenprint" Then
Worksheets("Template").Range("BI" & MAINr).PasteSpecial
End If

If Worksheets("Template").Range("G" & MAINr) = "Engraving" Then
Worksheets("Template").Cells(MAINr, 145).PasteSpecial
End If

If Worksheets("Template").Range("G" & MAINr) = "Embossing" Then
Worksheets("Template").Cells(MAINr, 166).PasteSpecial
End If

If Worksheets("Template").Range("G" & MAINr) = "Foil Blocking" Then
Worksheets("Template").Cells(MAINr, 187).PasteSpecial
End If

If Worksheets("Template").Range("G" & MAINr) = "Full Colour Print (UV / Transfer)" Then
Worksheets("Template").Cells(MAINr, 208).PasteSpecial
End If

If Worksheets("Template").Range("G" & MAINr) = "Full Colour Print (Digital)" Then
Worksheets("Template").Cells(MAINr, 229).PasteSpecial
End If

If Worksheets("Template").Range("G" & MAINr) = "Full Colour Print (Sublimation)" Then
Worksheets("Template").Cells(MAINr, 250).PasteSpecial
End If

If Worksheets("Template").Range("G" & MAINr) = "Full Colour Label" Then
Worksheets("Template").Cells(MAINr, 271).PasteSpecial
End If

If Worksheets("Template").Range("G" & MAINr) = "Embroidery" Then
Worksheets("Template").Cells(MAINr, 292).PasteSpecial
End If

If Worksheets("Template").Range("G" & MAINr) = "Full Colour Doming" Then
Worksheets("Template").Cells(MAINr, 313).PasteSpecial
End If

If Worksheets("Template").Range("G" & MAINr) = "Transfer" Then
Worksheets("Template").Cells(MAINr, 355).PasteSpecial
End If

If Worksheets("Template").Range("G" & MAINr) = "Screenround" Then
Worksheets("Template").Cells(MAINr, 439).PasteSpecial
End If

If Worksheets("Template").Range("G" & MAINr) = "Padprint" Then
Worksheets("Template").Cells(MAINr, 523).PasteSpecial
End If

Next MAINr

End If
Next r

'x = Worksheets("Template").Range("G" & r) 'Print Method
'
''Find Print Method equivalent code
'printmethodcodeFIND = WorksheetFunction.Match(a, Worksheets("££ Print Prices 2016").Range("A:A"), 0) 'Found the row its on
'printmethodCODE = Worksheets("Method").Range("A" & printmethodcodeFIND)
'printmethodNAME = Worksheets("Method").Range("B" & printmethodcodeFIND)

'extracols = False
'
'If Worksheets("££ Print Prices 2016").Range("A" & printmethodcodeFIND) = "extra cols" Then
'extracols = True
'End If
'
'maxcols = Worksheets("Template").Range("H" & r)
'
''Make sure no more than 4 maximum colours
'If maxcols > 4 Then maxcols = 4

End Sub

Datanology