New Row

Hi. This is the macro that I currently have. As you can see it starts w/ rows(21:21), however, if row 21 is enter and you run the macro again, it starts again at row 21 hence copying down whatever is currently in row 21 to 22. I do not want this macro to do that. i want it to start at the next empty row if previous rows are occupied. how do i do that? Thanks.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

This selects the first empty cell after the last entry in column A

Change the column letter and column number to suite your requirements

Hello Ruckuz,

This version of the macro will copy the values from the previous row to the next empty row.

Please Login or Register  to view this content.

Hello ruckuz,

Please Login or Register  to view this content.

Adding the Macro

1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
   
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

You didn't attach a sample workbook, so I don't know what you're up to.

You seem to be saying that you want the macro to copy certain data from row 20 to the next available row on the sheet every time the macro is run.

davesexcel's line:

Please Login or Register  to view this content.

loads the number of the last used row into Rws; the next available blank row is just Rws +1.
Then you need to copy the data values from row 20 to that row:

Please Login or Register  to view this content.

So that the macro becomes:

Please Login or Register  to view this content.

Though I'm curious why, if data in row 20 is changing, it just can't be put into the next row to begin with?

Hello ruckuz,

Now that I can see the layout, it is clear what you want to do.

Hello ruckuz,

I don't have Excel 2007 but I have a converter to read the 2007 format. Sometimes I can change the files and save them and the changes work. In this case, it isn't working.

Hello Ruckuz,

I discovered you cross posted this question. Please read the forum rules regarding cross posts. It is allowed provided you list the links to other sites. This is keeps everybody on the same page. Few things p*** people off more than working hard to help you and find out your question has already been answered.

Cross Posted Here: http://www.mrexcel.com/forum/showthread.php?t=517901

Hello ruckuz,

Not a problem. You're new here. I don't expect you to have read all the rues about posting yet. I am still working on the code and just about have it working. I copied the data over to a 2003 workbook so I could tell the difference between my code problems and those raised by converting the workbook.

Hello Ruckuz,

Here is the working macro. Copy this into Module2. You can then add a button to the worksheet (somewhere) to run the macro or do it manually.

EDIT: I attached the workbook with the macro and the button.

Please Login or Register  to view this content.

Hello Ruckuz,

Happy New Year !

Hello Ruckuz,

Thanks for pointing that out. I will work on it.

Hello Ruckuz,

I replaced the Subtotal function with a Sum since that is what the Subtotal was doing. The ranges are now automatically sized and summed. The Subtotal functions in row 23 are still there. Here is the formula I used and a copy of the workbook.

Row 21:
=SUM(OFFSET(C$3,0,0,ROW()-3, 1))