ExcelBanter - Copy and Paste Macro

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Copy and Paste Macro (https://www.excelbanter.com/excel-programming/403025-copy-paste-macro.html)

Copy and Paste Macro

Hi

I have data in Cols B, C and D. The number of rows of data varies.

I'm trying to write a macro which puts a formula in E2 which sums D2:D4 then
selects cells E2:E4 (E2 has the formula and E3:E4 are blank) and copies them
down to the last row of data in Col D. This is the equivalent of typing the
formula in E2, highlighting E2:E4 then double-clicking in the bottom right
corner of E4 to autofill down.

I've got this far but it doesn't work as it puts the formula in every cell.

Please can I have some help

Thanks a lot

Kewa

----------------------------------

Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("B1").Select
ActiveCell.FormulaR1C1 = "'Name"
Range("C1").Select
ActiveCell.FormulaR1C1 = "'Roll No"
Range("D1").Select
ActiveCell.FormulaR1C1 = "'Amount"
Range("E1").Select
ActiveCell.FormulaR1C1 = "'Total"

Range("E2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-1]:R[2]C[-1])"

Range("E2:e4").Select
Selection.Copy
Set frng = Range("E2:E4" & Range("D65536").End(xlUp).Row)
frng.FillDown

Columns(5).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("E1").Select
Application.CutCopyMode = False

Columns("B:E").Select
Columns("B:E").EntireColumn.AutoFit

Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="<"
End Sub

Copy and Paste Macro

Usually it's better to not select things to work with them. Just plop
formulas/values directly into ranges (for example).

Option Explicit
Sub testme()

Dim LastRow As Long

With ActiveSheet

.Rows(1).Insert
.Range("B1").Value = "Name"
.Range("C1").Value = "Roll No"
.Range("D1").Value = "Amount"
.Range("E1").Value = "Total"

.Range("E2").Formula = "=sum(B2:b4)"

LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row

.Range("E2:E4").AutoFill _
Destination:=.Range("e2:e" & LastRow), Type:=xlFillDefault

With .Columns(5)
.Value = .Value
End With

With .Columns("B:E")
.AutoFilter Field:=4, Criteria1:="<"
'autofit after adding filter arrows??
.EntireColumn.AutoFit
End With
End With
End Sub

And I like this way to fill a bunch of cells with headers:

.Range("B1").Resize(1, 4) _
= Array("Name", "Roll No", "Amount", "Total")

nospaminlich wrote:

Hi

I have data in Cols B, C and D. The number of rows of data varies.

I'm trying to write a macro which puts a formula in E2 which sums D2:D4 then
selects cells E2:E4 (E2 has the formula and E3:E4 are blank) and copies them
down to the last row of data in Col D. This is the equivalent of typing the
formula in E2, highlighting E2:E4 then double-clicking in the bottom right
corner of E4 to autofill down.

I've got this far but it doesn't work as it puts the formula in every cell.

Please can I have some help

Thanks a lot

Kewa

----------------------------------

Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("B1").Select
ActiveCell.FormulaR1C1 = "'Name"
Range("C1").Select
ActiveCell.FormulaR1C1 = "'Roll No"
Range("D1").Select
ActiveCell.FormulaR1C1 = "'Amount"
Range("E1").Select
ActiveCell.FormulaR1C1 = "'Total"

Range("E2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-1]:R[2]C[-1])"

Range("E2:e4").Select
Selection.Copy
Set frng = Range("E2:E4" & Range("D65536").End(xlUp).Row)
frng.FillDown

Columns(5).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("E1").Select
Application.CutCopyMode = False

Columns("B:E").Select
Columns("B:E").EntireColumn.AutoFit

Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="<"
End Sub

--

Dave Peterson

Copy and Paste Macro

Give this a whirl... It uses your same general logic but is a little cleaner.

Sub test()

Rows(1).Insert
Range("B1").Value = "'Name"
Range("C1").Value = "'Roll No"
Range("D1").Value = "'Amount"
Range("E1").Value = "'Total"

Range("E2").FormulaR1C1 = "=SUM(RC[-1]:R[2]C[-1])"
Range("E2:E4").Copy Destination:= _
Range(Range("E2"), Cells(Rows.Count, "D").End(xlUp).Offset(0, 1))
Columns("E").Value = Columns("E").Value
With Columns("B:E")
.EntireColumn.AutoFit
.AutoFilter Field:=4, Criteria1:="<"
End With

End Sub
--
HTH...

Jim Thomlinson

"nospaminlich" wrote:

Hi

I have data in Cols B, C and D. The number of rows of data varies.

I'm trying to write a macro which puts a formula in E2 which sums D2:D4 then
selects cells E2:E4 (E2 has the formula and E3:E4 are blank) and copies them
down to the last row of data in Col D. This is the equivalent of typing the
formula in E2, highlighting E2:E4 then double-clicking in the bottom right
corner of E4 to autofill down.

I've got this far but it doesn't work as it puts the formula in every cell.

Please can I have some help

Thanks a lot

Kewa

----------------------------------

Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("B1").Select
ActiveCell.FormulaR1C1 = "'Name"
Range("C1").Select
ActiveCell.FormulaR1C1 = "'Roll No"
Range("D1").Select
ActiveCell.FormulaR1C1 = "'Amount"
Range("E1").Select
ActiveCell.FormulaR1C1 = "'Total"

Range("E2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-1]:R[2]C[-1])"

Range("E2:e4").Select
Selection.Copy
Set frng = Range("E2:E4" & Range("D65536").End(xlUp).Row)
frng.FillDown

Columns(5).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("E1").Select
Application.CutCopyMode = False

Columns("B:E").Select
Columns("B:E").EntireColumn.AutoFit

Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="<"
End Sub

Copy and Paste Macro

Just a guess but did you mean "=sum(D2:D4)"and not "=sum(B2:b4)" for your
formula?

PS. I like the array thing. Kinda purdy. Perhaps overkill for just a couple
of items but very fancy... ;-)
--
HTH...

Jim Thomlinson

"Dave Peterson" wrote:

Usually it's better to not select things to work with them. Just plop
formulas/values directly into ranges (for example).

Option Explicit
Sub testme()

Dim LastRow As Long

With ActiveSheet

.Rows(1).Insert
.Range("B1").Value = "Name"
.Range("C1").Value = "Roll No"
.Range("D1").Value = "Amount"
.Range("E1").Value = "Total"

.Range("E2").Formula = "=sum(B2:b4)"

LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row

.Range("E2:E4").AutoFill _
Destination:=.Range("e2:e" & LastRow), Type:=xlFillDefault

With .Columns(5)
.Value = .Value
End With

With .Columns("B:E")
.AutoFilter Field:=4, Criteria1:="<"
'autofit after adding filter arrows??
.EntireColumn.AutoFit
End With
End With
End Sub

And I like this way to fill a bunch of cells with headers:

.Range("B1").Resize(1, 4) _
= Array("Name", "Roll No", "Amount", "Total")

nospaminlich wrote:

Hi

I have data in Cols B, C and D. The number of rows of data varies.

I'm trying to write a macro which puts a formula in E2 which sums D2:D4 then
selects cells E2:E4 (E2 has the formula and E3:E4 are blank) and copies them
down to the last row of data in Col D. This is the equivalent of typing the
formula in E2, highlighting E2:E4 then double-clicking in the bottom right
corner of E4 to autofill down.

I've got this far but it doesn't work as it puts the formula in every cell.

Please can I have some help

Thanks a lot

Kewa

----------------------------------

Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("B1").Select
ActiveCell.FormulaR1C1 = "'Name"
Range("C1").Select
ActiveCell.FormulaR1C1 = "'Roll No"
Range("D1").Select
ActiveCell.FormulaR1C1 = "'Amount"
Range("E1").Select
ActiveCell.FormulaR1C1 = "'Total"

Range("E2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-1]:R[2]C[-1])"

Range("E2:e4").Select
Selection.Copy
Set frng = Range("E2:E4" & Range("D65536").End(xlUp).Row)
frng.FillDown

Columns(5).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("E1").Select
Application.CutCopyMode = False

Columns("B:E").Select
Columns("B:E").EntireColumn.AutoFit

Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="<"
End Sub

--

Dave Peterson

Copy and Paste Macro

Yep. Thanks for the correction.

Jim Thomlinson wrote:

Just a guess but did you mean "=sum(D2:D4)"and not "=sum(B2:b4)" for your
formula?

PS. I like the array thing. Kinda purdy. Perhaps overkill for just a couple
of items but very fancy... ;-)
--
HTH...

Jim Thomlinson

"Dave Peterson" wrote:

Usually it's better to not select things to work with them. Just plop
formulas/values directly into ranges (for example).

Option Explicit
Sub testme()

Dim LastRow As Long

With ActiveSheet

.Rows(1).Insert
.Range("B1").Value = "Name"
.Range("C1").Value = "Roll No"
.Range("D1").Value = "Amount"
.Range("E1").Value = "Total"

.Range("E2").Formula = "=sum(B2:b4)"

LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row

.Range("E2:E4").AutoFill _
Destination:=.Range("e2:e" & LastRow), Type:=xlFillDefault

With .Columns(5)
.Value = .Value
End With

With .Columns("B:E")
.AutoFilter Field:=4, Criteria1:="<"
'autofit after adding filter arrows??
.EntireColumn.AutoFit
End With
End With
End Sub

And I like this way to fill a bunch of cells with headers:

.Range("B1").Resize(1, 4) _
= Array("Name", "Roll No", "Amount", "Total")

nospaminlich wrote:

Hi

I have data in Cols B, C and D. The number of rows of data varies.

I'm trying to write a macro which puts a formula in E2 which sums D2:D4 then
selects cells E2:E4 (E2 has the formula and E3:E4 are blank) and copies them
down to the last row of data in Col D. This is the equivalent of typing the
formula in E2, highlighting E2:E4 then double-clicking in the bottom right
corner of E4 to autofill down.

I've got this far but it doesn't work as it puts the formula in every cell.

Please can I have some help

Thanks a lot

Kewa

----------------------------------

Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("B1").Select
ActiveCell.FormulaR1C1 = "'Name"
Range("C1").Select
ActiveCell.FormulaR1C1 = "'Roll No"
Range("D1").Select
ActiveCell.FormulaR1C1 = "'Amount"
Range("E1").Select
ActiveCell.FormulaR1C1 = "'Total"

Range("E2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-1]:R[2]C[-1])"

Range("E2:e4").Select
Selection.Copy
Set frng = Range("E2:E4" & Range("D65536").End(xlUp).Row)
frng.FillDown

Columns(5).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("E1").Select
Application.CutCopyMode = False

Columns("B:E").Select
Columns("B:E").EntireColumn.AutoFit

Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="<"
End Sub

--

Dave Peterson

--

Dave Peterson

Copy and Paste Macro

Many thanks Dave and Jim for your very helpful responses. I'm sorted now.

Thanks again

Kewa