Search : in
By :

Coping formulas to next row using a macro

Last answer on Apr 3, 2009 1:23:44 am BST jd438, on Apr 2, 2009 4:30:21 pm BST 
 Report this message to moderators

Hello,
I am trying to make a macro to copy a formula to the next row in a spreadsheet. Then paste the absolute values back into the original row. It should always go to the next row, but I can't get it to do it. Here's what I have: Any help?

Sub Macro7()
'
' Macro7 Macro
' Macro recorded 04/02/2009 by Farmland Foods, Inc.
'
' Keyboard Shortcut: Ctrl+q
'
Range("B1600:AC1600").Select
Selection.Copy

Range("B1601").Select
ActiveSheet.Paste
Range("B1600").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B1602").Select
Application.CutCopyMode = False
End Sub

Configuration: Windows XP
Internet Explorer 6.0

Best answers for « Coping formulas to next row using a macro » in :
Copy rows based on a condition ShowCopy rows based on a condition Issue Solution Note Issue How can I have excel copy the entire row of data from worksheet Employee Inventory to another worksheet called EEs if column Q contains TERM. I've tried almost everything but...
Excel – Comparing cell A1 to entire A column in Sheet 2 ShowExcel – Comparing cell A1 to entire A column in Sheet 2 Issue Solution Note Issue I have been trying to compare sheet1 A2 to sheet2 A2 through A500 and if it exists somewhere in sheet2's a col then copy that entire row to a new sheet....
Excel – Macro for copy & paste selected range ShowExcel – Macro for copy & paste selected range Issue Solution Note Issue I have a summary of data by month in one sheet and the raw data in another sheet. Instead of creating multiple worksheets for my raw data for each month, I want...
Spreadsheet - Formulas ShowIntroduction to Formulas The main use of a spreadsheet is to automate calculations, which means using cells to perform operations based on values in other cells. The spreadsheet recalculates all the values each time a change is made to the value of...
Ask your question Reply

1

WutUp WutUp, on Apr 2, 2009 11:24:58 pm BST

I stay away from the macro recorder whenever possible.

Try this instead:

Dim EndRow
EndRow = Range("B65536").End(xlUp).Row

Range("B" & EndRow + 1, "AC" & EndRow + 1).Value = Range("B" & EndRow, "AC" & EndRow).Value

   
Reply to WutUp WutUp

2

 WutUp WutUp, on Apr 3, 2009 1:23:44 am BST

I believe I misread your post the first time. This one will copy the formulas to the next row. Then, copy and
paste values from the copy range as you stated.


Sub PasteValues()

Dim EndRow
EndRow = Range("B65536").End(xlUp).Row

Range("B" & EndRow, "AC" & EndRow).Copy Range("B" & EndRow + 1, "AC" & EndRow + 1)
Range("B" & EndRow, "AC" & EndRow).Copy
Range("B" & EndRow, "AC" & EndRow).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("B" & EndRow).Select


End Sub

   
Reply to WutUp WutUp
Ask your question Reply
They need your help