Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help to Copy to Last Cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help to Copy to Last Cell
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help to Copy to Last Cell
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 -----Original Message----- 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" wrote in message ... 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help to Copy to Last Cell
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. "Dave Y" wrote in message ... 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 -----Original Message----- 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" wrote in message ... 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help to Copy to Last Cell
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 "George Stevenson" wrote in message ... 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. "Dave Y" wrote in message ... 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 -----Original Message----- 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" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I copy a macro into another cell? | Excel Discussion (Misc queries) | |||
Is there a macro i can use to copy a cell +1 | Excel Discussion (Misc queries) | |||
Want to use a Macro to copy and paste cell *value* only | Excel Discussion (Misc queries) | |||
Using macro to copy a part of a cell content to next cell | Excel Discussion (Misc queries) | |||
Macro help - copy a cell down | Excel Discussion (Misc queries) |