Inserting additional rows and only copying certain columns

K

Kennedy

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
 
D

Dave Peterson

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top