Copy and Insert Using VBA
 

Hello,

I have a problem in VBA that i can't get my head around...

I have two rows of data.. One on row 17 and one on row 31 of the same
tab. I need VBA to copy the row (both 17 & 31) and insert a new copied
row underneath... The probem is that when i run it again, because of
the insert from row 17 it needs to copy row 32 but it re copys 31,
which eventually becomes a header...?

How do i get it to effectivly follow row 31 and grow as the data
does..

Thanks alot

Mike


--
Mikey2005
------------------------------------------------------------------------
Mikey2005's Profile: http://www.excelforum.com/member.php...o&userid=31316
View this thread: http://www.excelforum.com/showthread...hreadid=510664

Copy and Insert Using VBA
 

You can use named ranges and refer to the named range in your code, instead
of the hard coded row numbers. If you want help with that you will need to
post your code...
--
HTH...

Jim Thomlinson


"Mikey2005" wrote:


Hello,

I have a problem in VBA that i can't get my head around...

I have two rows of data.. One on row 17 and one on row 31 of the same
tab. I need VBA to copy the row (both 17 & 31) and insert a new copied
row underneath... The probem is that when i run it again, because of
the insert from row 17 it needs to copy row 32 but it re copys 31,
which eventually becomes a header...?

How do i get it to effectivly follow row 31 and grow as the data
does..

Thanks alot

Mike


--
Mikey2005
------------------------------------------------------------------------
Mikey2005's Profile: http://www.excelforum.com/member.php...o&userid=31316
View this thread: http://www.excelforum.com/showthread...hreadid=510664

Copy and Insert Using VBA
 

My script currently reads:

Sub Copyandpaste()
'
' Copyandpaste Macro
' Macro recorded 09/02/2006 by michael.hebblewhite
'

'
Rows("17:17").Select
Selection.Copy
Rows("18:18").Select
Selection.Insert Shift:=xlDown
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=12
Rows("32:32").Select
Application.CutCopyMode = False
Selection.Copy
Rows("33:33").Select
Selection.Insert Shift:=xlDown
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-30
Range("A1").Select
ActiveWindow.SmallScroll Down:=0
End Sub

Its the second copy and paste thats the issue.....


--
Mikey2005
------------------------------------------------------------------------
Mikey2005's Profile: http://www.excelforum.com/member.php...o&userid=31316
View this thread: http://www.excelforum.com/showthread...hreadid=510664

Copy and Insert Using VBA
 

Still cant get my head around it....

Cheers....


--
Mikey2005
------------------------------------------------------------------------
Mikey2005's Profile: http://www.excelforum.com/member.php...o&userid=31316
View this thread: http://www.excelforum.com/showthread...hreadid=510664