Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste Macro
Many thanks Dave and Jim for your very helpful responses. I'm sorted now.
Thanks again Kewa |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Paste Macro | Excel Programming | |||
copy multiple worksheets of a workbook, and paste onto a Word document ( either create new doc file or paste onto an existing file.) I need this done by VBA, Excel Macro | Excel Programming | |||
Copy and Paste macro needs to paste to a changing cell reference | Excel Programming | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
Macro to Copy/Paste then Paste to Next Line | Excel Programming |