Macro to insert at end of data

[allmant12]

Hello - I am trying to format a file using a Macro - the file is exported from another source and the amount of records is variable. I want to copy and paste the first record onto the end of the file - I tried using copy, then cntl-end. However, the Macro records this as a static row so the next time the Macro runs, it will paste in the same cell regardless of the record length. Is their another command I should use other than cntl-end in the Macro or is thier an edit I can use in the Macro code to override the hardcoded cell? (In bold below it hard codes to A39 when I want that to dynamically always be the row after the last row with data).

Sub FifthThirdPositivePay()
'
' FifthThirdPositivePay Macro
'
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
'
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Columns("E:E").Select
Selection.NumberFormat = "yyyymmdd"
Columns("E:E").EntireColumn.AutoFit
Columns("F:F").Select
Selection.NumberFormat = "00000000000"
Columns("F:F").EntireColumn.AutoFit
Rows("1:1").Select
Selection.Copy
ActiveCell.SpecialCells(xlLastCell).Select
Range("A39").Select
ActiveSheet.Paste
Range("A40").Select
ActiveSheet.Paste
Range("A39").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "20"
Range("D39").Select
ActiveCell.FormulaR1C1 = "=COUNTA(R[-38]C:R[-1]C)"
Range("D39").Select
Selection.NumberFormat = "0000000000"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("F39").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-38]C:R[-1]C)"
Range("H39").Select
ActiveCell.FormulaR1C1 = " "
Range("A40").Select
ActiveCell.FormulaR1C1 = "30"
Range("C40").Select
ActiveCell.FormulaR1C1 = "9999999999"
Range("D40").Select
ActiveCell.FormulaR1C1 = "counta(d1:"
Range("D40").Select
ActiveCell.FormulaR1C1 = "=COUNTA(R[-39]C:R[-2]C)"
Range("D40").Select
Selection.NumberFormat = "0000000000"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("F40").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-39]C:R[-2]C)"
Range("G40").Select
ActiveCell.FormulaR1C1 = "'"
Range("H40").Select
ActiveCell.FormulaR1C1 = " "
Range("I40").Select
Columns("C:C").EntireColumn.AutoFit
Range("C40").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
End Sub

 

[ravishankar]

HI
try adding this line wherever you paste.

Range("A" & Range("A65536").end(xlup).row+1).pastespecial

ravi

 

[allmant12]

Thanks Ravi - big help -

But now, how do I change the code:

ActiveCell.FormulaR1C1 = "=COUNTA(R[-38]C:R[-1]C)"

so the COUNTA(R[-38]]C is equal to the first cell in this column?

Thanks!
Tom

 

[ravishankar]

Hi
The formula depends on which is your active cell. for ex: if D42 is active
D42 = "=COUNTA(R[-41]C:R[-1]C)" the range will be counta(D1:D41). R[-41] means 41 rows above 42(activecell). R[-1) is one row above 42 which is 41. Column has no numbers attached so it remains D. you can say C[+3] which will be col G. "=COUNTA(R[-41]C[6]:R[-1]C[6])" = counta(J1:J41).
ravi