Help to duplicate a range on one tab to another multiple times based on a cell value

[SkoobiDrew]

Hello everyone, I'm very new here so please forgive any format mistakes I may have made in my posting. I've looked through the other threads but have not found a direct solution for this situation, hopefully I am not asking something that has previously been answered.

The objective of this is to setup a data file from which I will print labels.
I need to basically copy a range of cells from a row on one tab, then paste (values, number formats) these onto another tab multiple times based on the value of a cell at the end of the range.


Here is an example of what I have, and what I need it to look like after the macro has run:




Here is the data for the macro:


Date
Zone
Project ID
Req_Name
Requestor
SEED1
SEED2
SEED3
SEED4
SEED5
SEED6
Number of Labels


25-Apr-2014
G-05
11224
Germination Study
Sandy
ABCDE





2


25-Apr-2014
G-05
11220
Bioassay
Jeff
EDCBA





1


25-Apr-2014
G-06
11235
Friday plantings
Heather
FGHJK
DFRGY
SXDCF
REWQA


3


25-Apr-2014
E-01
11219
Pollen donors
Jamie
POLKM
TREWQ




2












































This is what the result should look like on another tab:



Date
Zone
Project ID
Req_Name
Requestor
SEED1
SEED2
SEED3
SEED4
SEED5
SEED6



25-Apr-2014
G-05
11224
Germination Study
Sandy
ABCDE








25-Apr-2014
G-05
11224
Germination Study
Sandy
ABCDE








25-Apr-2014
G-05
11220
Bioassay
Jeff
EDCBA








25-Apr-2014
G-06
11235
Friday plantings
Heather
FGHJK
DFRGY
SXDCF
REWQA





25-Apr-2014
G-06
11235
Friday plantings
Heather
FGHJK
DFRGY
SXDCF
REWQA





25-Apr-2014
G-06
11235
Friday plantings
Heather
FGHJK
DFRGY
SXDCF
REWQA





25-Apr-2014
E-01
11219
Pollen donors
Jamie
POLKM
TREWQ







25-Apr-2014
E-01
11219
Pollen donors
Jamie
POLKM
TREWQ









Thanks, in advance, for any help/advice you can provide!

[p45cal]

The following macro creates a new sheet and duplicates the appropriate number of labels. See also attached which has a button at the top of the sheet which runs this macro. If your data is not in the same columns as mine then the results will probably be wrong.
Sub blah()
Set SourceSht = ActiveSheet
With Sheets.Add(After:=Sheets(Sheets.Count))
SourceSht.Range("A1").CurrentRegion.Copy .Range("A1")
.UsedRange.EntireColumn.AutoFit
For rw = .Cells(.Rows.Count, "L").End(xlUp).Row To 2 Step -1
If .Cells(rw, "L").Value > 1 Then
.Rows(rw).Copy
.Rows(rw).Offset(1).Resize(.Cells(rw, "L").Value - 1).Insert
End If
Next rw
.Columns("L:L").Delete
Application.CutCopyMode = False
End With
End Sub

[SkoobiDrew]

Thanks so very much p45cal, that one does accomplish the objective! The macro works great on the sample data I provided.

However, as I implement it in my data, I've run into a couple of issues.
1. The data being copied is the result of a formula, so I'm wondering if that if messing with the duplication.
2. I set up another macro that runs prior to yours which moves the data (copy-paste values) to a new sheet (your macro is on this sheet), but that still does not seem to have solved the issue.

Error code:
Run-time error '13':
Type mismatch

If you've got any advice to correct this, I would greatly appreciate it.


Here's a screen grab of the debug line:

[p45cal]

So it's copying the formulae and the formulae aren't giving the same results? It depends on (among other things) the absolute/relative references in the formulae. What are the formulae?
Do you want just the values to be on the new sheet? If so, change the single line (and let my macro create the new sheet and do the copying still):

SourceSht.Range("A1").CurrentRegion.Copy .Range("A1")
to the 2 lines:

SourceSht.Range("A1").CurrentRegion.Copy
.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Make sure you retain the dot at the beginning of the second line. All untested.
If not, come back.

ps column L contains numbers right?

[SkoobiDrew]

SOLVED!!! Thank you very much for the assist, p45cal, your guidance was essential!

Sorry for the delay in reply. There were a few days spent tweaking on my data fields so that the macro worked. THEN, I got jury duty for a week.