Fill down to last row of another column, do same thing next month but from previous last row +1 to new last row

[mpaulic9]

Hi All,

Hope you can help out, searching has not yielded me any results(could be lack of vba knowledge blinding me).

Each month I receive a report which has X amount of rows in it, one month more or less then the other. I then paste all these reports into one spreadsheet, month by month and build pivot tables of it to evaluate the costs compared to the other months.

The data does not have dates or anything to work with, so I manually would enter the month (e.g. JUL) and drag it down, but I'm looking for a quicker way.

So I aim to click a button and an input box shows up and then I can punch in the month (e.g. JUL) and it then will fill the empty rows in the column(M) with JUL up until the last row of data in Column A. Next month I will paste the new data into Columns (A - J) below the previous data, and I would like to click the button, type in AUG, and it then will find the new last row in column M and fill it down to the new last row in column (A).

So far I have the following code (which is obviously not complete)

Sub testinput()

Dim LastRow As Object

Set LastRow = ActiveSheet.Range("M65536").End(xlUp).Offset(1, 0)

LastMonth = InputBox("What month is this data from?. Please Use format MMM")


LastRow.Value = LastMonth
End Sub

My Logic is now telling me to Fill down LastMonth into those blank columns, but using a line such as this(below) works, however come next month I don't want it to start from "M2", I want it to start from the new last row in M and end at the new last row of A

Copydown = Range("A" & Rows.Count).End(xlUp).Row

Range(LastRow).AutoFill Destination:=Range("M2:M" & Copydown), Type:=xlFillDefault

I have tried many things, but no luck.

Any guidance would be greatly appreciated.

Note:
(excel 2003 on Win XP)



Matt

 

[Comfy]

Try this:

Sub FillCol()
Dim LastRowA As Long, LastRowM As Long
Dim LastMonth As String


LastRowA = Range("A65536").End(xlUp).Row
LastRowM = Range("M65536").End(xlUp).Row


LastMonth = InputBox("What month is this data from?. Please Use format MMM")


Range(Cells(LastRowM + 1, 13), Cells(LastRowA, 13)).Value = LastMonth


End Sub

 

[mpaulic9]

Perfect, it worked.
many thanks comfy, I can now apply this to so many other things I do.

Regards