macro to insert row and copy previous row + excel

I have a named range First_Table (A5:AB30 ) and a named range End_Table (A30).
I would like to insert a row above the named range End_Table (A30).
Then copy the contents and formula from cells F29:AB29 into the new row.

However every time I run the macro it seems to insert the new row in a
different place which is messing up my data.

Can anyone help me please?
--
Laura

Always post your code. HTH Otto

"Biffo" wrote in message
...
I have a named range First_Table (A5:AB30 ) and a named range End_Table
(A30).
I would like to insert a row above the named range End_Table (A30).
Then copy the contents and formula from cells F29:AB29 into the new row.

However every time I run the macro it seems to insert the new row in a
different place which is messing up my data.

Can anyone help me please?
--
Laura

This doesn't work on columns A:AB. It works on the entire row.

When I do this, I usually don't have anything to the right of the table and I
want the entire row inserted and copied.

If that's not what you want, post back.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim FirstTableRng As Range
Dim LastCellInTableCol1 As Range

With Worksheets("Sheet1") 'change to what you need
Set FirstTableRng = .Range("First_Table")
End With

With FirstTableRng.Columns(1)
Set LastCellInTableCol1 = .Cells(.Cells.Count)
End With

With LastCellInTableCol1
.EntireRow.Insert
.Offset(-2, 0).Copy _
Destination:=.Offset(-1, 0)
End With

End Sub

Biffo wrote:

I have a named range First_Table (A5:AB30 ) and a named range End_Table (A30).
I would like to insert a row above the named range End_Table (A30).
Then copy the contents and formula from cells F29:AB29 into the new row.

However every time I run the macro it seems to insert the new row in a
different place which is messing up my data.

Can anyone help me please?
--
Laura

--

Dave Peterson

Sorry about that Otto.

Sub Add_Student()
'
' Add_Student Macro
' Macro recorded 21/12/2009 by Phil
'

'
Application.Goto Reference:="First_Table"
Range("A26").Select
Selection.EntireRow.Insert
Range("F25:Y25").Select
Selection.AutoFill Destination:=Range("F25:Y26"), Type:=xlFillDefault
Range("F25:Y26").Select
Range("A27").Select

End Sub
--
Laura


"Otto Moehrbach" wrote:

Always post your code. HTH Otto

"Biffo" wrote in message
...
I have a named range First_Table (A5:AB30 ) and a named range End_Table
(A30).
I would like to insert a row above the named range End_Table (A30).
Then copy the contents and formula from cells F29:AB29 into the new row.

However every time I run the macro it seems to insert the new row in a
different place which is messing up my data.

Can anyone help me please?
--
Laura

.

Sorry again first reply did not work. If this one does not work I will start
a new thread.

Sub Add_Student()
'
' Add_Student Macro
' Macro recorded 21/12/2009 by Phil
'

'
Application.Goto Reference:="First_Table"
Range("A26").Select
Selection.EntireRow.Insert
Range("F25:Y25").Select
Selection.AutoFill Destination:=Range("F25:Y26"), Type:=xlFillDefault
Range("F25:Y26").Select
Range("A27").Select

End Sub
--
Laura


"Otto Moehrbach" wrote:

Always post your code. HTH Otto

"Biffo" wrote in message
...
I have a named range First_Table (A5:AB30 ) and a named range End_Table
(A30).
I would like to insert a row above the named range End_Table (A30).
Then copy the contents and formula from cells F29:AB29 into the new row.

However every time I run the macro it seems to insert the new row in a
different place which is messing up my data.

Can anyone help me please?
--
Laura

.

Hi Dave It's inserting the row in the right place Yeah!, but not copying the
formulas etc down, sorry to be a pain:-)
--
Laura


"Dave Peterson" wrote:

This doesn't work on columns A:AB. It works on the entire row.

When I do this, I usually don't have anything to the right of the table and I
want the entire row inserted and copied.

If that's not what you want, post back.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim FirstTableRng As Range
Dim LastCellInTableCol1 As Range

With Worksheets("Sheet1") 'change to what you need
Set FirstTableRng = .Range("First_Table")
End With

With FirstTableRng.Columns(1)
Set LastCellInTableCol1 = .Cells(.Cells.Count)
End With

With LastCellInTableCol1
.EntireRow.Insert
.Offset(-2, 0).Copy _
Destination:=.Offset(-1, 0)
End With

End Sub

Biffo wrote:

I have a named range First_Table (A5:AB30 ) and a named range End_Table (A30).
I would like to insert a row above the named range End_Table (A30).
Then copy the contents and formula from cells F29:AB29 into the new row.

However every time I run the macro it seems to insert the new row in a
different place which is messing up my data.

Can anyone help me please?
--
Laura

--

Dave Peterson
.

I was going to change it to work with just A:AB, but then decided not to. But I
didn't correct my partial changes...

With LastCellInTableCol1
.EntireRow.Insert
'added the .entirerow on the next line
.Offset(-2, 0).EntireRow.Copy _
Destination:=.Offset(-1, 0)
End With

Biffo wrote:

Hi Dave It's inserting the row in the right place Yeah!, but not copying the
formulas etc down, sorry to be a pain:-)
--
Laura

"Dave Peterson" wrote:

This doesn't work on columns A:AB. It works on the entire row.

When I do this, I usually don't have anything to the right of the table and I
want the entire row inserted and copied.

If that's not what you want, post back.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim FirstTableRng As Range
Dim LastCellInTableCol1 As Range

With Worksheets("Sheet1") 'change to what you need
Set FirstTableRng = .Range("First_Table")
End With

With FirstTableRng.Columns(1)
Set LastCellInTableCol1 = .Cells(.Cells.Count)
End With

With LastCellInTableCol1
.EntireRow.Insert
.Offset(-2, 0).Copy _
Destination:=.Offset(-1, 0)
End With

End Sub

Biffo wrote:

I have a named range First_Table (A5:AB30 ) and a named range End_Table (A30).
I would like to insert a row above the named range End_Table (A30).
Then copy the contents and formula from cells F29:AB29 into the new row.

However every time I run the macro it seems to insert the new row in a
different place which is messing up my data.

Can anyone help me please?
--
Laura

--

Dave Peterson
.


--

Dave Peterson

In fact, if that table ever moved from column A, then this would work better:

With LastCellInTableCol1
.EntireRow.Insert
.Offset(-2, 0).EntireRow.Copy _
Destination:=.Offset(-1, 0).EntireRow.Cells(1)
End With

(And it still works ok if the table starts in column A. I'd use this version.)



Dave Peterson wrote:

I was going to change it to work with just A:AB, but then decided not to. But I
didn't correct my partial changes...

With LastCellInTableCol1
.EntireRow.Insert
'added the .entirerow on the next line
.Offset(-2, 0).EntireRow.Copy _
Destination:=.Offset(-1, 0)
End With

Biffo wrote:

Hi Dave It's inserting the row in the right place Yeah!, but not copying the
formulas etc down, sorry to be a pain:-)
--
Laura

"Dave Peterson" wrote:

This doesn't work on columns A:AB. It works on the entire row.

When I do this, I usually don't have anything to the right of the table and I
want the entire row inserted and copied.

If that's not what you want, post back.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim FirstTableRng As Range
Dim LastCellInTableCol1 As Range

With Worksheets("Sheet1") 'change to what you need
Set FirstTableRng = .Range("First_Table")
End With

With FirstTableRng.Columns(1)
Set LastCellInTableCol1 = .Cells(.Cells.Count)
End With

With LastCellInTableCol1
.EntireRow.Insert
.Offset(-2, 0).Copy _
Destination:=.Offset(-1, 0)
End With

End Sub

Biffo wrote:

I have a named range First_Table (A5:AB30 ) and a named range End_Table (A30).
I would like to insert a row above the named range End_Table (A30).
Then copy the contents and formula from cells F29:AB29 into the new row.

However every time I run the macro it seems to insert the new row in a
different place which is messing up my data.

Can anyone help me please?
--
Laura

--

Dave Peterson
.


--

Dave Peterson

--

Dave Peterson

Hi Dave you are a star! 'Hi five monitor'.
This is working brilliantly. Yeah 'punching the air'
Sorry, but I still get over-excited when code works
--
Laura


"Dave Peterson" wrote:

In fact, if that table ever moved from column A, then this would work better:

With LastCellInTableCol1
.EntireRow.Insert
.Offset(-2, 0).EntireRow.Copy _
Destination:=.Offset(-1, 0).EntireRow.Cells(1)
End With

(And it still works ok if the table starts in column A. I'd use this version.)



Dave Peterson wrote:

I was going to change it to work with just A:AB, but then decided not to. But I
didn't correct my partial changes...

With LastCellInTableCol1
.EntireRow.Insert
'added the .entirerow on the next line
.Offset(-2, 0).EntireRow.Copy _
Destination:=.Offset(-1, 0)
End With

Biffo wrote:

Hi Dave It's inserting the row in the right place Yeah!, but not copying the
formulas etc down, sorry to be a pain:-)
--
Laura

"Dave Peterson" wrote:

This doesn't work on columns A:AB. It works on the entire row.

When I do this, I usually don't have anything to the right of the table and I
want the entire row inserted and copied.

If that's not what you want, post back.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim FirstTableRng As Range
Dim LastCellInTableCol1 As Range

With Worksheets("Sheet1") 'change to what you need
Set FirstTableRng = .Range("First_Table")
End With

With FirstTableRng.Columns(1)
Set LastCellInTableCol1 = .Cells(.Cells.Count)
End With

With LastCellInTableCol1
.EntireRow.Insert
.Offset(-2, 0).Copy _
Destination:=.Offset(-1, 0)
End With

End Sub

Biffo wrote:

I have a named range First_Table (A5:AB30 ) and a named range End_Table (A30).
I would like to insert a row above the named range End_Table (A30).
Then copy the contents and formula from cells F29:AB29 into the new row.

However every time I run the macro it seems to insert the new row in a
different place which is messing up my data.

Can anyone help me please?
--
Laura

--

Dave Peterson
.


--

Dave Peterson

--

Dave Peterson
.

Dave you are a STAR! (hi 5 monitor)
This is working brilliantly - YEAH (punching the air)
Sorry, but I still get over-excited when code works
--
Laura


"Dave Peterson" wrote:

In fact, if that table ever moved from column A, then this would work better:

With LastCellInTableCol1
.EntireRow.Insert
.Offset(-2, 0).EntireRow.Copy _
Destination:=.Offset(-1, 0).EntireRow.Cells(1)
End With

(And it still works ok if the table starts in column A. I'd use this version.)



Dave Peterson wrote:

I was going to change it to work with just A:AB, but then decided not to. But I
didn't correct my partial changes...

With LastCellInTableCol1
.EntireRow.Insert
'added the .entirerow on the next line
.Offset(-2, 0).EntireRow.Copy _
Destination:=.Offset(-1, 0)
End With

Biffo wrote:

Hi Dave It's inserting the row in the right place Yeah!, but not copying the
formulas etc down, sorry to be a pain:-)
--
Laura

"Dave Peterson" wrote:

This doesn't work on columns A:AB. It works on the entire row.

When I do this, I usually don't have anything to the right of the table and I
want the entire row inserted and copied.

If that's not what you want, post back.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim FirstTableRng As Range
Dim LastCellInTableCol1 As Range

With Worksheets("Sheet1") 'change to what you need
Set FirstTableRng = .Range("First_Table")
End With

With FirstTableRng.Columns(1)
Set LastCellInTableCol1 = .Cells(.Cells.Count)
End With

With LastCellInTableCol1
.EntireRow.Insert
.Offset(-2, 0).Copy _
Destination:=.Offset(-1, 0)
End With

End Sub

Biffo wrote:

I have a named range First_Table (A5:AB30 ) and a named range End_Table (A30).
I would like to insert a row above the named range End_Table (A30).
Then copy the contents and formula from cells F29:AB29 into the new row.

However every time I run the macro it seems to insert the new row in a
different place which is messing up my data.

Can anyone help me please?
--
Laura

--

Dave Peterson
.


--

Dave Peterson

--

Dave Peterson
.