Fill Macro

Hi

I am new to macros, and havent any past experience with programming. I am looking for a way of making a macro that looks for the last row in a table of three columns, and fills it down a row.

A rough idea of what i want can be found here Macro Question

Thanks in advance, ben

Give this a try

Please Login or Register  to view this content.

I'm not sure what you mean by "It would be good if it could auto put in another value from another reference"
Do you want one of the cells Ax, Bx or Cx to have a fixed value?
or depending on what row you are filling into it will pull a value from somewhere else on the same worksheet or another worksheet in the same workbook, etc.
What value do you want it to put in?

The macro I sent you will find the last used row on your sheet and then fill down one row from there in columns A, B, and C.
Since your column A has the Month-Year then it will increment that value.
It appears that your column B and C have numeric values so it will simply copy that same value into the next row.

Do you want the value that is currently in the Overview section of your spreadsheet to be filled into column B and C?

If that is the case try this.

Please Login or Register  to view this content.

I have modified the positions to reflect the positions of your two tables in the MacroHelp example you show.

HTH

That works exactly how i want it, there is one slight problem, that it starts filling from row 50, when the current row it should start from is 16, and because its starting all the way down there, it is not filling the date because it isnt in the cell above it. Any way to get it to start filling from row 16, where it needs to ?

If you dont understand: Screenshot and annotation

Thanks for all the help so far!

benb7760,
That is one drawback with the using the UsedRange command the way I have in this macro.
If you have ever done something in a row below the bottom of your table (lower than row 15 in this case) it will find that with UsedRange.Rows.Count.

A quick and easy work around is to highlight all rows (or at least several hundred) starting at the bottom of your table (row 16) then right clicking and select 'delete' (Don't just use the delete keyboard button).

This will clear any reminants of activitiy on the lower rows of your sheet then the macro will run correctly.

I have seen posts which suggest a more robust way to find the last populated row in the sheet and will check if I can find that information.

thank you, everything is working fine. If you do find a better way to find the last row, it'd be great if you could show me how to work it into the formula. The only problem left now is that the cells that you showed me how to do automatically:
Range("C" & NumRows + 1) = Range("F6")
Range("D" & NumRows + 1) = Range("F7")
Arent getting the cell formatting that i desire. Is there a way to do this?

Thanks for all your help!

If you are always going to be working on the same sheet then the easiest way to do it would be to preformat the cells with the desired format.

You can also do it programically by adding the following lines

Range("C" & NumRows + 1) = Range("F6")
Range("C" & NumRows + 1).number.format = "$#0.00"
Range("D" & NumRows + 1) = Range("F7")
Range("D" & NumRows + 1).number.format = "$#0.00"

If you want a different number format then I'd suggest recording a macro and manually changing the numberformat of a number of different cells and that will give you an idea of how the syntax will look for various types of numberformats.

To count the number of rows in a specific column you can replace the NumRows line with the following:

NumRows = Cells(Rows.Count, "B").End(xlUp).Row

This assumes your Dates are in Column B as in your example.

HTH