Macro Help to Copy to Last Cell

[Dave Y]

Hello,

I created a macro using the recorder that is used to copy
a formula down to the last cell in a worksheet. Below is
the code for this macro:
Sub CopyFormula()
'
' CopyFormula Macro
' Macro recorded 6/30/2004 by dy
'
Range("G2").Select
Selection.Copy
Range("E3:G3").Select
Range("G3").Activate
Range(Selection, Selection.End(xlDown)).Select
Range("G3:G215").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

The problem is in the code statement of Range("G3:G215");
this was created when I recorded the macro which worked
today because G215 is currently the last cell. Tomorrow
when I paste new data into the worksheet there will be
more data which will obviously create the need to use more
rows beyond G215. My macro, as it stands, will only copy
down to G215 unless I manually change the range in the
code. How can I change this recorded code to know to copy
down to the current last cell automatically (dynamically)
so that the formula will get copied to the the last needed
cell? Any help will be greatly appreciated. Thank you.
Dave Y

 

[George Stevenson]

Make sure the Sheet name is correct for your application when you run the
..AddName command

sub TestLastRow()
dim mylastrow as integer
Range("D1").Select
myLastRow = FindLastRow()
ActiveWorkbook.Names.Add Name:="YourName",
RefersToR1C1:="=Sheet1!R1C1:R" + Trim(Str(myLastRow)) + "C18"
end sub

Public Function FindLastRow()
Set currentcell = ActiveCell
Dim MyRow
MyRow = 0
' Look for 3 consecutive rows of empty cells
Do Until IsEmpty(currentcell) And IsEmpty(currentcell.Offset(1, 0)) And
IsEmpty(currentcell.Offset(2, 0))
Set currentcell = currentcell.Offset(1, 0)
MyRow = currentcell.Row
' CurrentCell.Activate
Loop
FindLastRow = MyRow - 1
End Function

 

[Dave Y]

Hi George,

Thanks for your quick reply. I will have to keep playing
with the code; I haven't been able to get it to run as of
yet. I made sure the worksheet name was entered in
the "AddName command" as you mentioned, and I changed the
cell references to point to the cells in my worksheet. I
will keep trying to get it to run as I'm sure the problem
is my rookie VBA status. Thank you very much for your help.

Dave Y

 

[George Stevenson]

To see what is going on, I use the debug features and single step through
the macro so I can see what its doing on each line. I also remove the ' as
the first character in the line with the command
' CurrentCell.Activate

That changes it from a comment to an active command.

Once I know its working correctly, then I would put the ' back in as the
first character the the row (changes it to a comment) to keep that line from
executing because it slows things down to have to highlight the cell.

 

[Bryan Kelly]

With some help from someone else in this group I would up with this maco
to add a column and paste a formula down to the last row. This does not use
a do loop so it will be faster.

Sub Add_Elapsed_Time_Column()
'
' Add_Elapsed_Time_Column Macro
' Macro recorded 6/12/2004 by bk
' and subsequently edited
'
Dim nLastRow As Long
' Find the last row of data in column C
nLastRow = Range("C" & Rows.Count).End(xlUp).Row
'
' build a range variable encompasing the C column in rows
' 4 through the end of the column.
nTheRange = "C4:C" & nLastRow
'
' Insert a column to the left of column C
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
'
' format as a number with one decimal digit
Selection.NumberFormat = "0.0"
'
' title the column
Range("C1").Select
ActiveCell.FormulaR1C1 = "Elapsed Time"
'
' Start the column at time 0.0
Range("C2").Select
ActiveCell.FormulaR1C1 = "0"
'
' In the next cell, increment by 0.1 seconds
Range("C3").Select
ActiveCell.FormulaR1C1 = "=R[-1]C + 0.1"
'
' Copy that cell
Range("C3").Select
Selection.copy
'
' select the range as defined above and past to the entire column
Range(nTheRange).Select
ActiveSheet.Paste
End Sub