Noob Question

[neetusxm]

Hello everyone, i am a novice excel user and i need help with the following:-

I have a long data sheet and i need to make more rows of specific number for each rows that i have for eg.

<TABLE style="WIDTH: 221pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=294><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3730" width=102><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" width=51><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 77pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=20 width=102>Model Number</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 width=77>Item</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 width=51>Cost</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 width=64>Stock Pcs</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=20>BR-101</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Brown Sofa</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>$50.00 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=20>BL-102</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Black Sofa</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>$65.00 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=20>CR-103</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Cream Sofa</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>$35.65 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>8</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=20>BR-104</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Brown Sofa</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>$88.98 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>2</TD></TR></TBODY></TABLE>

So i need to automatically create 10 rows of same data of model BR-101 and so on....

Is this possible in excel?

Again many thanks for any help i get in this

 

[hiker95]

neetusxm,

Welcome to the MrExcel board.


Sample data before the macro:

Excel Workbook
	A	B	C	D
1	Model Number	Item	Cost	Stock Pcs
2	BR-101	Brown Sofa	$50.00	10
3	BL-102	Black Sofa	$65.00	5
4	CR-103	Cream Sofa	$35.65	8
5	BR-104	Brown Sofa	$88.98	2
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Sheet1

After the macro:

Excel Workbook
	A	B	C	D
1	Model Number	Item	Cost	Stock Pcs
2	BR-101	Brown Sofa	$50.00	10
3	BR-101	Brown Sofa	$50.00	10
4	BR-101	Brown Sofa	$50.00	10
5	BR-101	Brown Sofa	$50.00	10
6	BR-101	Brown Sofa	$50.00	10
7	BR-101	Brown Sofa	$50.00	10
8	BR-101	Brown Sofa	$50.00	10
9	BR-101	Brown Sofa	$50.00	10
10	BR-101	Brown Sofa	$50.00	10
11	BR-101	Brown Sofa	$50.00	10
12	BR-101	Brown Sofa	$50.00	10
13	BL-102	Black Sofa	$65.00	5
14	BL-102	Black Sofa	$65.00	5
15	BL-102	Black Sofa	$65.00	5
16	BL-102	Black Sofa	$65.00	5
17	BL-102	Black Sofa	$65.00	5
18	BL-102	Black Sofa	$65.00	5
19	CR-103	Cream Sofa	$35.65	8
20	CR-103	Cream Sofa	$35.65	8
21	CR-103	Cream Sofa	$35.65	8
22	CR-103	Cream Sofa	$35.65	8
23	CR-103	Cream Sofa	$35.65	8
24	CR-103	Cream Sofa	$35.65	8
25	CR-103	Cream Sofa	$35.65	8
26	CR-103	Cream Sofa	$35.65	8
27	CR-103	Cream Sofa	$35.65	8
28	BR-104	Brown Sofa	$88.98	2
29	BR-104	Brown Sofa	$88.98	2
30	BR-104	Brown Sofa	$88.98	2
31
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub CopyRows()
' hiker95, 20100106
Dim a As Long, b As Long, rng As Range
Application.ScreenUpdating = False
For a = Cells(Rows.Count, 4).End(xlUp).Row To 2 Step -1
  Set rng = Range("A" & a & ":D" & a)
  b = Range("D" & a).Value
  Range("A" & a & ":A" & a + b - 1).EntireRow.Insert
  rng.Copy Range("A" & a & ":D" & a + b - 1)
Next a
Application.ScreenUpdating = True
End Sub

Then run the "CopyRows" macro.