Auto row insert as per required

H

harinsh

Active Member
Joined
Feb 7, 2012
Messages
273
Hi Team,

I looking for below macro requirement and hope can help me with it.

I have two sheets, Sheet1 is containing number and sheet2 containing the data. In sheet 2 if you have three line items then macro should insert the rows and copy the same row data as per the number updated in sheet 1. This should repeat for all the number of line items.

Example.
In Sheet 1 - A1 cell value =12

In Sheet 2 - data below
[TABLE="width: 179"]
<TBODY>[TR]
[TD]A</SPAN>[/TD]
[TD]B</SPAN>[/TD]
[TD]C</SPAN>[/TD]
[/TR]
[TR]
[TD]DEFC</SPAN>[/TD]
[TD]1221</SPAN>[/TD]
[TD]Jacks</SPAN>[/TD]
[/TR]
[TR]
[TD]ERDE</SPAN>[/TD]
[TD]1232</SPAN>[/TD]
[TD]Lub</SPAN>[/TD]
[/TR]
[TR]
[TD]RIEK</SPAN>[/TD]
[TD]1214</SPAN>[/TD]
[TD]Cable</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]


Requirement - Macro should insert and copy the same A1 value in Sheet2 12 times and should repeat the same for rest of two line items. Please note that there should be additional empty row between each line item after inserting the rows.

Plz let me know if you need further clarificaiton on the above.

Thanks for your time.
 
Sort by date Sort by votes
This is output look like
[TABLE="width: 192"]
<colgroup><col style="width:48pt" span="3" width="64"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]A[/TD]
[TD="class: xl63, width: 64"]B[/TD]
[TD="class: xl63, width: 64"]C[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]DEFC[/TD]
[TD="class: xl63, width: 64, align: right"]1221[/TD]
[TD="class: xl63, width: 64"]Jacks[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]DEFC[/TD]
[TD="class: xl63, width: 64, align: right"]1221[/TD]
[TD="class: xl63, width: 64"]Jacks[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]DEFC[/TD]
[TD="class: xl63, width: 64, align: right"]1221[/TD]
[TD="class: xl63, width: 64"]Jacks[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]DEFC[/TD]
[TD="class: xl63, width: 64, align: right"]1221[/TD]
[TD="class: xl63, width: 64"]Jacks[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]DEFC[/TD]
[TD="class: xl63, width: 64, align: right"]1221[/TD]
[TD="class: xl63, width: 64"]Jacks[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]DEFC[/TD]
[TD="class: xl63, width: 64, align: right"]1221[/TD]
[TD="class: xl63, width: 64"]Jacks[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]DEFC[/TD]
[TD="class: xl63, width: 64, align: right"]1221[/TD]
[TD="class: xl63, width: 64"]Jacks[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]DEFC[/TD]
[TD="class: xl63, width: 64, align: right"]1221[/TD]
[TD="class: xl63, width: 64"]Jacks[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]DEFC[/TD]
[TD="class: xl63, width: 64, align: right"]1221[/TD]
[TD="class: xl63, width: 64"]Jacks[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]DEFC[/TD]
[TD="class: xl63, width: 64, align: right"]1221[/TD]
[TD="class: xl63, width: 64"]Jacks[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]DEFC[/TD]
[TD="class: xl63, width: 64, align: right"]1221[/TD]
[TD="class: xl63, width: 64"]Jacks[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]DEFC[/TD]
[TD="class: xl63, width: 64, align: right"]1221[/TD]
[TD="class: xl63, width: 64"]Jacks[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]ERDE[/TD]
[TD="class: xl63, width: 64, align: right"]1232[/TD]
[TD="class: xl63, width: 64"]Lub[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]ERDE[/TD]
[TD="class: xl63, width: 64, align: right"]1232[/TD]
[TD="class: xl63, width: 64"]Lub[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]ERDE[/TD]
[TD="class: xl63, width: 64, align: right"]1232[/TD]
[TD="class: xl63, width: 64"]Lub[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]ERDE[/TD]
[TD="class: xl63, width: 64, align: right"]1232[/TD]
[TD="class: xl63, width: 64"]Lub[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]ERDE[/TD]
[TD="class: xl63, width: 64, align: right"]1232[/TD]
[TD="class: xl63, width: 64"]Lub[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]ERDE[/TD]
[TD="class: xl63, width: 64, align: right"]1232[/TD]
[TD="class: xl63, width: 64"]Lub[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]ERDE[/TD]
[TD="class: xl63, width: 64, align: right"]1232[/TD]
[TD="class: xl63, width: 64"]Lub[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]ERDE[/TD]
[TD="class: xl63, width: 64, align: right"]1232[/TD]
[TD="class: xl63, width: 64"]Lub[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]ERDE[/TD]
[TD="class: xl63, width: 64, align: right"]1232[/TD]
[TD="class: xl63, width: 64"]Lub[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]ERDE[/TD]
[TD="class: xl63, width: 64, align: right"]1232[/TD]
[TD="class: xl63, width: 64"]Lub[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]ERDE[/TD]
[TD="class: xl63, width: 64, align: right"]1232[/TD]
[TD="class: xl63, width: 64"]Lub[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]ERDE[/TD]
[TD="class: xl63, width: 64, align: right"]1232[/TD]
[TD="class: xl63, width: 64"]Lub[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]RIEK[/TD]
[TD="class: xl63, width: 64, align: right"]1214[/TD]
[TD="class: xl63, width: 64"]Cable[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]RIEK[/TD]
[TD="class: xl63, width: 64, align: right"]1214[/TD]
[TD="class: xl63, width: 64"]Cable[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]RIEK[/TD]
[TD="class: xl63, width: 64, align: right"]1214[/TD]
[TD="class: xl63, width: 64"]Cable[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]RIEK[/TD]
[TD="class: xl63, width: 64, align: right"]1214[/TD]
[TD="class: xl63, width: 64"]Cable[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]RIEK[/TD]
[TD="class: xl63, width: 64, align: right"]1214[/TD]
[TD="class: xl63, width: 64"]Cable[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]RIEK[/TD]
[TD="class: xl63, width: 64, align: right"]1214[/TD]
[TD="class: xl63, width: 64"]Cable[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]RIEK[/TD]
[TD="class: xl63, width: 64, align: right"]1214[/TD]
[TD="class: xl63, width: 64"]Cable[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]RIEK[/TD]
[TD="class: xl63, width: 64, align: right"]1214[/TD]
[TD="class: xl63, width: 64"]Cable[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]RIEK[/TD]
[TD="class: xl63, width: 64, align: right"]1214[/TD]
[TD="class: xl63, width: 64"]Cable[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]RIEK[/TD]
[TD="class: xl63, width: 64, align: right"]1214[/TD]
[TD="class: xl63, width: 64"]Cable[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]RIEK[/TD]
[TD="class: xl63, width: 64, align: right"]1214[/TD]
[TD="class: xl63, width: 64"]Cable[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]RIEK[/TD]
[TD="class: xl63, width: 64, align: right"]1214[/TD]
[TD="class: xl63, width: 64"]Cable[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
try below code
Code: 
Option Explicit
Sub Test()
Dim inrng As Long
Dim i As Integer
Dim lr As Long
inrng = Sheets("Sheet1").Range("A1").Value
Sheets("Sheet2").Select
lr = Range("A" & Rows.Count).End(xlUp).Row
For i = lr To 1 Step -1
Cells(i + 1, 1).Resize(inrng - 1, 1).EntireRow.Insert
Cells(i, 1).Resize(inrng, 3).FillDown
Next i
End Sub
 
Upvote 0
Super...its working fine...but I need little customise when you count the number of rows in Sheet 2 it always should exclude begining for the two rows....that A1 & A2 rows and starts from A3.

One more thing I need to include one empty row in between each set of rows. Like once it's filldown 12 rows then there should be one empty row then again start the second one.

Thanks for your time and efforts....
 
Upvote 0
try below code
Code: 
Option Explicit
Sub Test()
Dim inrng As Long
Dim i As Integer
Dim lr As Long
inrng = Sheets("Sheet1").Range("A1").Value
Sheets("Sheet2").Select
lr = Range("A" & Rows.Count).End(xlUp).Row
For i = lr To 3 Step -1
Cells(i + 1, 1).Resize(inrng, 1).EntireRow.Insert
Cells(i, 1).Resize(inrng, 3).FillDown
Next i
Rows(3).EntireRow.Insert
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

Q
  • Question Question
Macro needed to insert 4 rows in table, copy data from above, copy data from another sheet and paste special (transpose values) to those new rows...re
Replies
1
Views
513
sijpie
sijpie
S
  • Solved
VBA for inserting multiple photos from a specified folder
2
Replies
12
Views
1K
SomeOrdinaryIndian
S
R
  • Question Question
Insert new rows based on user input
Replies
4
Views
861
bobaol
B
G
  • Question Question
Run macro after new email
Replies
1
Views
267
Peter_SSs
Peter_SSs
C
  • Question Question
Create a new row for every date in row and create multiple copies of data to new worksheet based on cell number value for each date in the row VBA
Replies
0
Views
203
Chris258
C

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top