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
0
Utf
12/21/2009 1:26:01 PM
excel.misc 78881 articles. 5 followers. Follow

12 Replies
752 Views

Similar Articles

[PageSpeed] 57

Always post your code.  HTH  Otto

"Biffo" <Biffo@discussions.microsoft.com> wrote in message 
news:3B60286F-5DAD-478A-B98A-417F5EA61A7D@microsoft.com...
> 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 

0
Otto
12/21/2009 1:30:29 PM
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
0
Dave
12/21/2009 1:40:48 PM
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" <Biffo@discussions.microsoft.com> wrote in message 
> news:3B60286F-5DAD-478A-B98A-417F5EA61A7D@microsoft.com...
> > 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 
> 
> .
> 
0
Utf
12/21/2009 2:16:01 PM
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" <Biffo@discussions.microsoft.com> wrote in message 
> news:3B60286F-5DAD-478A-B98A-417F5EA61A7D@microsoft.com...
> > 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 
> 
> .
> 
0
Utf
12/21/2009 2:21:01 PM
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
> .
> 
0
Utf
12/21/2009 2:30:02 PM
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
0
Dave
12/21/2009 2:48:59 PM
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
0
Dave
12/21/2009 2:56:16 PM
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
> .
> 
0
Utf
12/21/2009 5:22:01 PM
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
> .
> 
0
Utf
12/21/2009 5:25:01 PM
Glad it worked for you.

Biffo wrote:
> 
> 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
> > .
> >

-- 

Dave Peterson
0
Dave
12/21/2009 6:46:53 PM
Hi again
If you use ActiveSheet instead of a named sheet you can then copy the whole 
sheet for a different set of data and the code still works :-)

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

-- 
Laura


"Dave Peterson" wrote:

> Glad it worked for you.
> 
> Biffo wrote:
> > 
> > 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 ActiveSheet '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 Peterson
> .
> 
0
Utf
12/21/2009 8:11:01 PM
Be careful.  If the activesheet doesn't have a range with that name, the code
won't work.

Option Explicit
Sub testme()

    Dim wks As Worksheet
    Dim FirstTableRng As Range
    Dim LastCellInTableCol1 As Range
    
    With Activesheet
        set firsttablerng = nothing
        on error resume next
        Set FirstTableRng = .Range("First_Table")
        on error goto 0
    End With

    if firsttablerng is nothing then 
       msgbox "activesheet doesn't have a range named First_table" & vblf _
              & "Please change sheets or create that named range."
       exit sub
    end if 
        
    With FirstTableRng.Columns(1)
        Set LastCellInTableCol1 = .Cells(.Cells.Count)
    End With
    
    With LastCellInTableCol1
        .EntireRow.Insert
        .Offset(-2, 0).EntireRow.Copy _
            Destination:=.Offset(-1, 0).EntireRow.Cells(1)
    End With
    
End Sub


Biffo wrote:
> 
> Hi again
> If you use ActiveSheet instead of a named sheet you can then copy the whole
> sheet for a different set of data and the code still works :-)
> 
>     With ActiveSheet 'change to what you need
>          Set FirstTableRng = .Range("First_Table")
>     End With
> 
> --
> Laura
> 
> "Dave Peterson" wrote:
> 
> > Glad it worked for you.
> >
> > Biffo wrote:
> > >
> > > 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 ActiveSheet '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 Peterson
> > .
> >

-- 

Dave Peterson
0
Dave
12/21/2009 9:21:56 PM
Reply:

Similar Artilces: