Row insert and Formula copy problem 
Author Message
 Row insert and Formula copy problem

I am using some VBA code to duplicate an existing row of data. The
code looks like it is working great but in reality the formulas are
lost in the process.

The original code came off of the message board and I have modified it
to include all of my required data elements.

Is there a better way to insert a row, copy the active row of data and
maintain formulas and any conditional formatting?

Here is the code I am using at this point:

Sub insertrow()
    Dim TempRange As Range
    Set TempRange = Selection.EntireRow
    Selection.EntireRow.Insert Shift:=xlDown
    '
    Intersect(Selection.EntireRow, Columns("A")) _
        = Intersect(TempRange, Columns("A"))
    Intersect(Selection.EntireRow, Columns("B")) _
        = Intersect(TempRange, Columns("B"))
    Intersect(Selection.EntireRow, Columns("C")) _
        = Intersect(TempRange, Columns("C"))
    Intersect(Selection.EntireRow, Columns("D")) _
        = Intersect(TempRange, Columns("D"))
    Intersect(Selection.EntireRow, Columns("E")) _
        = Intersect(TempRange, Columns("E"))
    Intersect(Selection.EntireRow, Columns("F")) _
        = Intersect(TempRange, Columns("F"))
    Intersect(Selection.EntireRow, Columns("G")) _
        = Intersect(TempRange, Columns("G"))
    Intersect(Selection.EntireRow, Columns("H")) _
        = Intersect(TempRange, Columns("H"))
    Intersect(Selection.EntireRow, Columns("I")) _
        = Intersect(TempRange, Columns("I"))
End Sub

Any help is much appreciated.

Al



Sat, 27 Aug 2005 16:59:57 GMT  
 Row insert and Formula copy problem
Try the following macro.

1. Indicate the row you wish to copy by clicking on any
cell in the row.
2. Run the macro.

An exact duplicate (including format and formulars) of the
row is inserted above the original row.

--------------------------------
Option Explicit

Sub test()

    'remembeb the source row
    Dim cellActive As Range
    Set cellActive = ActiveCell

    Dim rgNewRow As Range
    ActiveCell.EntireRow.Insert Shift:=xlDown
    Set rgNewRow = Rows(cellActive.Row - 1)

    cellActive.EntireRow.Copy

    rgNewRow.PasteSpecial (xlPasteAll)
End Sub

Quote:
>-----Original Message-----
>I am using some VBA code to duplicate an existing row of
data. The
>code looks like it is working great but in reality the
formulas are
>lost in the process.

>The original code came off of the message board and I
have modified it
>to include all of my required data elements.

>Is there a better way to insert a row, copy the active
row of data and
>maintain formulas and any conditional formatting?

>Here is the code I am using at this point:

>Sub insertrow()
>    Dim TempRange As Range
>    Set TempRange = Selection.EntireRow
>    Selection.EntireRow.Insert Shift:=xlDown
>    '
>    Intersect(Selection.EntireRow, Columns("A")) _
>        = Intersect(TempRange, Columns("A"))
>    Intersect(Selection.EntireRow, Columns("B")) _
>        = Intersect(TempRange, Columns("B"))
>    Intersect(Selection.EntireRow, Columns("C")) _
>        = Intersect(TempRange, Columns("C"))
>    Intersect(Selection.EntireRow, Columns("D")) _
>        = Intersect(TempRange, Columns("D"))
>    Intersect(Selection.EntireRow, Columns("E")) _
>        = Intersect(TempRange, Columns("E"))
>    Intersect(Selection.EntireRow, Columns("F")) _
>        = Intersect(TempRange, Columns("F"))
>    Intersect(Selection.EntireRow, Columns("G")) _
>        = Intersect(TempRange, Columns("G"))
>    Intersect(Selection.EntireRow, Columns("H")) _
>        = Intersect(TempRange, Columns("H"))
>    Intersect(Selection.EntireRow, Columns("I")) _
>        = Intersect(TempRange, Columns("I"))
>End Sub

>Any help is much appreciated.

>Al
>.



Sat, 27 Aug 2005 17:33:22 GMT  
 Row insert and Formula copy problem
Meihua,

That was too easy. Many thanks for the help.

Al

Quote:

> Try the following macro.

> 1. Indicate the row you wish to copy by clicking on any
> cell in the row.
> 2. Run the macro.

> An exact duplicate (including format and formulars) of the
> row is inserted above the original row.

> --------------------------------
> Option Explicit

> Sub test()

>     'remembeb the source row
>     Dim cellActive As Range
>     Set cellActive = ActiveCell

>     Dim rgNewRow As Range
>     ActiveCell.EntireRow.Insert Shift:=xlDown
>     Set rgNewRow = Rows(cellActive.Row - 1)

>     cellActive.EntireRow.Copy

>     rgNewRow.PasteSpecial (xlPasteAll)
> End Sub

> >-----Original Message-----
> >I am using some VBA code to duplicate an existing row of
>  data. The
> >code looks like it is working great but in reality the
>  formulas are
> >lost in the process.

> >The original code came off of the message board and I
>  have modified it
> >to include all of my required data elements.

> >Is there a better way to insert a row, copy the active
>  row of data and
> >maintain formulas and any conditional formatting?

> >Here is the code I am using at this point:

> >Sub insertrow()
> >    Dim TempRange As Range
> >    Set TempRange = Selection.EntireRow
> >    Selection.EntireRow.Insert Shift:=xlDown
> >    '
> >    Intersect(Selection.EntireRow, Columns("A")) _
> >        = Intersect(TempRange, Columns("A"))
> >    Intersect(Selection.EntireRow, Columns("B")) _
> >        = Intersect(TempRange, Columns("B"))
> >    Intersect(Selection.EntireRow, Columns("C")) _
> >        = Intersect(TempRange, Columns("C"))
> >    Intersect(Selection.EntireRow, Columns("D")) _
> >        = Intersect(TempRange, Columns("D"))
> >    Intersect(Selection.EntireRow, Columns("E")) _
> >        = Intersect(TempRange, Columns("E"))
> >    Intersect(Selection.EntireRow, Columns("F")) _
> >        = Intersect(TempRange, Columns("F"))
> >    Intersect(Selection.EntireRow, Columns("G")) _
> >        = Intersect(TempRange, Columns("G"))
> >    Intersect(Selection.EntireRow, Columns("H")) _
> >        = Intersect(TempRange, Columns("H"))
> >    Intersect(Selection.EntireRow, Columns("I")) _
> >        = Intersect(TempRange, Columns("I"))
> >End Sub

> >Any help is much appreciated.

> >Al
> >.



Sun, 28 Aug 2005 01:56:50 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Insert Row/Copy Cell Formulas

2. Inserting new rows with mixed formulas and data w/o copying data

3. Insert rows - copy formulas automatically

4. Insert Row, Can the formulas copy too?

5. insert row with formulas copied

6. Formula copied into Auto-inserted row???

7. Insert copied rows into blank rows that are between rows of data

8. Insert row, preserve formulas from row above/below?

9. copying a formula down x rows one time then y rows the next time

10. Insert Rows and Copy range to the new rows


 
Powered by phpBB® Forum Software © phpBB Group