Inserting additional rows and only copying certain columns
 

Created a macro that inserts rows based on the number value in a certain
column. So if the column (EL2) shows 3, then the macro will insert 3 rows
beneath that row. However, the macro also copies all of the data from that
row. Would like to find a way to only copy certain columns to the row below.
So instead of the entire row being copied, say insert rows and only copy
columns A through R below.
Here's the macro. Iknow the probelm is in the [cell.EntireRow.Copy
Destination:=cell.Offset(1, 0).EntireRow] section, just can't figure out how
to change it
Thanks for any input


Sub addrows()

Dim cell, cell1 As Range
Set cell = Range("B2")

Do Until cell = ""

Set cell1 = cell.Offset(1, 0)

For i = 1 To cell.Value
cell.Offset(1, 0).EntireRow.Insert
cell.EntireRow.Copy Destination:=cell.Offset(1, 0).EntireRow
Next i

Set cell = cell1

Loop

End Sub

Inserting additional rows and only copying certain columns
 

I find that when I'm inserting or deleting rows, it's much easier to work from
the bottom row toward the top.

I used column B to find the last cell used. And column C to hold the number of
rows to insert after that row.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowManyRows As Long

Set wks = Worksheets("Sheet1")

With wks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
HowManyRows = .Cells(iRow, "C").Value
'some kind of sanity check
If HowManyRows 0 _
And HowManyRows < 100 Then
.Rows(iRow + 1).Resize(HowManyRows).EntireRow.Insert
.Rows(iRow).Copy _
Destination:=.Rows(iRow + 1).Resize(HowManyRows)
End If
Next iRow
End With
End Sub

Kennedy wrote:

Created a macro that inserts rows based on the number value in a certain
column. So if the column (EL2) shows 3, then the macro will insert 3 rows
beneath that row. However, the macro also copies all of the data from that
row. Would like to find a way to only copy certain columns to the row below.
So instead of the entire row being copied, say insert rows and only copy
columns A through R below.
Here's the macro. Iknow the probelm is in the [cell.EntireRow.Copy
Destination:=cell.Offset(1, 0).EntireRow] section, just can't figure out how
to change it
Thanks for any input

Sub addrows()

Dim cell, cell1 As Range
Set cell = Range("B2")

Do Until cell = ""

Set cell1 = cell.Offset(1, 0)

For i = 1 To cell.Value
cell.Offset(1, 0).EntireRow.Insert
cell.EntireRow.Copy Destination:=cell.Offset(1, 0).EntireRow
Next i

Set cell = cell1

Loop

End Sub

--

Dave Peterson