copy and paste from row below

[Rich]

am trying to get the following code to copy data from the row below the row
it inserts a new line, instead of the fixed (copy from 17 and paste into 16
as it is at the min

Thanks



For Each sh In Worksheets(Array("Jan", "Feb", "March", "april", "may",
"June", "July", "Aug", "Sep", "Oct", "Nov", "Dec", "Overview"))
sh.Rows(x).Insert
Range("B17:e17").Select
Selection.Copy
Range("B16").Select
ActiveSheet.Paste
Next sh

 

[chijanzen]

Rich:

try,

For Each sh In Worksheets(Array("Jan", "Feb", "March", _
"april", "may", "June", "July", "Aug", "Sep", "Oct", "Nov", "Dec",
"Overview"))
With sh
'sh.Rows(x).Insert
.Range("B17:e17").Copy .Range("B16")
End With
Next sh

 

[Norman Jones]

Hi Rich,

Try something like:

'=============>>
Public Sub Tester002()
Dim SH As Worksheet
Dim rng As Range
Const i As Long = 16

For Each SH In Worksheets(Array("Jan", "Feb", "March", "april", _
"may", "June", "July", "Aug", _
"Sep", "Oct", "Nov", "Dec", _
"Overview"))
With SH
.Rows(i).Insert
Set rng = .Range("B" & i + 1).Resize(1, 4)
rng.Copy Destination:=rng(0, 1)
End With
Next SH
End Sub
'<<=============

 

[Rich]

thanks but that only works by copying data from row 17 and inserting it into
16,, i am looking for a way to make the row variable . so if a row is
inserted at 29 it copys the data from 30.. or if a new row in 99 it copies
from 100 ect

is this possible

 

[Gary Keramidas]

chijanzen:
just curious what you sig is. it only shows up as boxes and takes longer to
download than other posts?

 

[chijanzen]

Rich:

try,

Const x = 17 'copy row
For Each sh In Worksheets(Array("Jan", "Feb"))
With sh
.Rows(x - 1).Insert
.Range("B" & x).Resize(, 4).Copy .Range("B" & x - 1)
End With
Next sh