Macro to change value from list and make number of copies by cell value then print all pages

[Radzha]

Hello,

I am trying to optimize paperwork and I cant figure out how to compile two separate macros to work as I need.

On Excel worrk book I have two sheets.

Sheet1 contains order form (invoice, etc.)
Sheet2 contains list of product name and quantity.

I need to do a macro so it would take "Product name" from list(eg. Sheet2 A1 to Sheet1 N10) and change number interchangeable from 1 to "Quantity" (eg. Sheet2 B2 to Sheet1 R10) and print all generated copies through all the list

For example: Sheet2 contains product "TEST" and quantity is "3". So printout will be 3 pages "TEST - 1", "TEST - 2", 'TEST - 3" And then folow to the next value in the list.


Thank for tips.


I have found a way to print number of copies with interchangeable number using this code below.

Sub Technologiniu_korteliu_kiekis()

Dim CopiesCount As Long
Dim copynumber As Long


CopiesCount = Application.InputBox("How many copies do you want?", Type:=1)
'Now the program wants you to input how many pages you like to print.
'You can input 100 here.


For copynumber = 1 To CopiesCount
With ActiveSheet
   .Range("L7").Value = copynumber 'I assume your invoice number is in cell E1.
   .PrintOut 'Print the sheet
End With
Next copynumber
End Sub

Thread in tihs forum called "VBA macro for REPLACE cell value from a list THEN print! Important " has macro for replacing values. So I wonder how to merge these codes?

 

[Radzha]

Hello,

It seems I have managed to compile those codes togethers. So far it works as intended.

Sub Macro()

Dim i As Long, LastRow As Long


LastRow = Worksheets("Sheet2").Range("C65536").End(xlUp).Row


For i = 8 To LastRow


    Worksheets("Sheet1").Range("G6").Value = Worksheets("Sheet2").Range("C" & i).Value
    Worksheets("Sheet1").Range("J7").Value = Worksheets("Sheet2").Range("D" & i).Value


Dim CopiesCount As Long
Dim copynumber As Long


CopiesCount = Worksheets("Sheet2").Range("E" & i).Value


If Not Worksheets("Sheet2").Range("B" & i).Value = 1 Then
For copynumber = 1 To CopiesCount
With Worksheets("Sheet1")
   .Range("L7").Value = copynumber
   .PrintOut
End With
Next copynumber
    End If


Next i




End Sub