macro help with print lables

  • Thread starter Thread starter Damian
  • Start date Start date
D

Damian

I suggest you repost the message to the Programming newsgroup - I am still

learning VBA. Shorten the message to something along the lines of:

I have an Excel worksheet with a packing list on sheet1

ITEM NO. QTY. PART NO.

1 5 Oven Base

2 36 Default

3 29 Oven Internal Wall

etc

I need a macro with these specs

For each item

a) copy the Part No data (column C) to column B on Sheet2. This is to be

done N times where N = value in column B of sheet1

(e.g. 5 times for first item filling B1, B2, B3, B4 and B5 of Sheet2 with

Oven Base)

b) print Sheet2

c) delete data on Sheet2 leaving formatting

next item in list.

I have looked at using Words mail merge but this isn,'t what i'm really
after
--
----------------------------
Couray Sheetmetal
9 Apsley Plc.
Seaford
Melbourne.
Australia.
PH 03 97861609

---------------------------
 
Sub PrintSheet()
Dim i As Integer
Dim p As Integer
Dim Ws1 As Worksheet
Dim Ws2 As Worksheet


Set Ws1 = Sheets("sheet1")
Set Ws2 = Sheets("Sheet2")
For i = 1 To Ws1.Range("b" & Ws1.Range("b65536").End(xlUp).Row) Ste
1
For p = 1 To Ws1.Cells(i, "b").Value Step 1
Ws2.Cells(p, "b").Value = Ws1.Cells(i, "c").Value
Next p
Ws2.PrintOut Copies:=1, Collate:=True
Ws2.Cells.ClearContents
Next i
End Su
 
Back
Top