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

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

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"Rich" wrote:

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

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

---
Regards,
Norman


"Rich" wrote in message
...
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

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

"chijanzen" wrote:

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

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"Rich" wrote:

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:
just curious what you sig is. it only shows up as boxes and takes longer to
download than other posts?

--


Gary


"chijanzen" wrote in message
...
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

--
???,???????
???,???????

http://www.vba.com.tw/plog/


"Rich" wrote:

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

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

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"Rich" wrote:

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

"chijanzen" wrote:

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

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"Rich" wrote:

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