Auto row insert as per required

harinsh

Active Member
Joined
Feb 7, 2012
Messages
262
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
A</SPAN>B</SPAN>C</SPAN>
DEFC</SPAN>1221</SPAN>Jacks</SPAN>
ERDE</SPAN>1232</SPAN>Lub</SPAN>
RIEK</SPAN>1214</SPAN>Cable</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>


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.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

harinsh

Active Member
Joined
Feb 7, 2012
Messages
262
This is output look like
ABC
DEFC1221Jacks
DEFC1221Jacks
DEFC1221Jacks
DEFC1221Jacks
DEFC1221Jacks
DEFC1221Jacks
DEFC1221Jacks
DEFC1221Jacks
DEFC1221Jacks
DEFC1221Jacks
DEFC1221Jacks
DEFC1221Jacks
ERDE1232Lub
ERDE1232Lub
ERDE1232Lub
ERDE1232Lub
ERDE1232Lub
ERDE1232Lub
ERDE1232Lub
ERDE1232Lub
ERDE1232Lub
ERDE1232Lub
ERDE1232Lub
ERDE1232Lub
RIEK1214Cable
RIEK1214Cable
RIEK1214Cable
RIEK1214Cable
RIEK1214Cable
RIEK1214Cable
RIEK1214Cable
RIEK1214Cable
RIEK1214Cable
RIEK1214Cable
RIEK1214Cable
RIEK1214Cable

<colgroup><col style="width:48pt" span="3" width="64"> </colgroup><tbody>
</tbody>
 

kevatarvind

Well-known Member
Joined
Mar 3, 2013
Messages
1,047
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
 

harinsh

Active Member
Joined
Feb 7, 2012
Messages
262

ADVERTISEMENT

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....
 

kevatarvind

Well-known Member
Joined
Mar 3, 2013
Messages
1,047
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,720
Members
414,401
Latest member
grenona2020

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
Top