Coping formulas to next row using a macro

Solved/Closed

jd438 - Apr 2, 2009 at 11:30 AM
CADU1947 Posts 3 Registration date Friday December 4, 2015 Status Member Last seen December 4, 2015 - Dec 4, 2015 at 10:30 AM

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

Coping formulas to next row using a macro
Saints row 2 cheats - Guide
How to delete a row in a table in word - Guide
Spreadsheet formulas - Guide
Little alchemy formulas - Guide
Spell number in excel without macro - Guide

1 response

Answer 1 / 1

WutUp WutUp
Apr 2, 2009 at 06:24 PM

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

WutUp WutUp
Apr 2, 2009 at 08:23 PM

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

CADU1947 Posts 3 Registration date Friday December 4, 2015 Status Member Last seen December 4, 2015 > WutUp WutUp
Dec 4, 2015 at 10:30 AM

Thank you, I'll try and see what happens.

Dulce
May 25, 2012 at 04:24 PM

How can I apply this macro with the same instructions but that instead of getting the last row, get the last column?

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 26, 2012 at 08:30 AM

you can use this function to get last/first column /row within a given range

Sample Calls :
Get last used row lastrow = getItemLocation("*", sheets("sheet1").cells)
get first used row firstrow = getItemLocation("*", sheets("sheet1").cells), true, false)
get last column firstrow = getItemLocation("*", sheets("sheet1").cells), true, true, false)
get first column firstrow = getItemLocation("*", sheets("sheet1").cells), true, false, false)

Public Function getItemLocation(sLookFor As String, _
                                rngSearch As Range, _
                                Optional bFullString As Boolean = True, _
                                Optional bLastOccurance As Boolean = True, _
                                Optional bFindRow As Boolean = True) As Long
                                   
   'find the first/last row/column  within a range for a specific string
      
   Dim Cell             As Range
   Dim iLookAt          As Integer
   Dim iSearchDir       As Integer
   Dim iSearchOdr       As Integer
         
   If (bFullString) _
   Then
      iLookAt = xlWhole
   Else
      iLookAt = xlPart
   End If
   If (bLastOccurance) _
   Then
      iSearchDir = xlPrevious
   Else
      iSearchDir = xlNext
   End If
   If Not (bFindRow) _
   Then
      iSearchOdr = xlByColumns
   Else
      iSearchOdr = xlByRows
   End If
         
   With rngSearch
      If (bLastOccurance) _
      Then
         Set Cell = .Find(sLookFor, .Cells(1, 1), xlValues, iLookAt, iSearchOdr, iSearchDir)
      Else
         Set Cell = .Find(sLookFor, .Cells(.Rows.Count, .Columns.Count), xlValues, iLookAt, iSearchOdr, iSearchDir)
      End If
   End With
         
   If Cell Is Nothing Then
      getItemLocation = 0
   ElseIf Not (bFindRow) _
   Then
      getItemLocation = Cell.Column
   Else
      getItemLocation = Cell.Row
   End If
   Set Cell = Nothing

End Function

CADU1947 Posts 3 Registration date Friday December 4, 2015 Status Member Last seen December 4, 2015
Dec 4, 2015 at 10:30 AM

Many thanks !!!

Similar discussions

Code for macro that conditionally inserts rows

Automatically Run Macro from a calculated cell value change

Similar discussions

Subscribe To Our Newsletter!