Kanban

[tuxy]

I have an excel file containing a list of record(500). Every record has a number that identifies the total qty of labels that should be printed Example:

A Data , Data , Data etc....3
B Data , Data , Data etc....4
C Data , Data , Data etc....2

I'm trying to create a macro that starting from the sheet above generates a new sheet with the following layout:

A Data , Data , Data etc....1/3
A Data , Data , Data etc....2/3
A Data , Data , Data etc....3/3
B Data , Data , Data etc....1/4
B Data , Data , Data etc....2/4
B Data , Data , Data etc....3/4
B Data , Data , Data etc....4/4
C Data , Data , Data etc....1/2
C Data , Data , Data etc....2/2

I cannot figure out how to performe this..........
Please someone can suggest me an example code?: pray2:
At present time I?m doing this job manually and with an avarage of 500 part number is a big deal...............:banghead:

[mdmackillop]

Can you post a small sample?

[tuxy]

I have enclosed a sample:

The first sheet (before) is what I get from my system .
Then I have to create (manually at the moment) the sheet after (in this sample we have only two records that turns in six in sheet after , but usually I handle 400-500 records that took 3 days to be completed in the after output). I wolud like to create a macro that do this for me. But I's really hard for me to traslate this in a working VBA code. :bug::bug:

[mdmackillop]

Should give you time for a :coffee:


Option Explicit
Sub Labelling()
Dim rng As Range
Dim cel As Range
Dim Tgt As Range
Dim NoL As Long
Dim i As Long
Dim ws As Worksheet
Application.ScreenUpdating = False

'Add sheet for results
Set ws = Sheets.Add(after:=Sheets(Sheets.Count))
'Get range of Part Numbers
With Sheets("Before")
.Range("A1:E1").Copy ws.Range("A1")
Set rng = Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
End With
'Loop through part Nos.
For Each cel In rng
'Get number
NoL = cel.Offset(, 4)
'Copy and paste required lines
Set Tgt = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1)
cel.Resize(, 4).Copy Tgt.Resize(NoL)
'Add numbers
For i = 1 To NoL
Tgt.Offset(i - 1, 4) = "'" & i & "/" & NoL
Next
Next
'Format Columns
ws.Columns("A:E").Columns.AutoFit
Application.ScreenUpdating = True
End Sub

[tuxy]

You are great!!!! It works perfectly , Thank you !!!:friends:
:clap::clap::clap::clap::clap::clap::clap::clap::clap::clap::clap::clap::cl ap::clap::clap::clap::clap::clap::clap::clap: