Autofill Month

[alsharif]

Hello Every one,


please help me with the proper VBA code for this:

My work sheet has two Columns A and B

I need to Auto fill Dates monthly starting from the last date in column B till the end of the last filled Cell in Column A.

the last cell on both columns needs to be dynamic since the data is increasing every day in the sheet .


Thank you In Advance

 

[ER_Neha]

Hello Please try this. If works for you or not, please provide the feedback.

I have assumed one row in column b will have one date according to which other rows will be auto filled

Sub date_month()

Dim lastrow_a As Byte
Dim lastrow_b As Integer
Dim b_day As Byte
Dim b_month As Byte
Dim b_year As Integer
Dim i As Integer

lastrow_a = Range("a1").End(xlDown).Row
lastrow_b = Range("b1").End(xlDown).Row

b_day = VBA.Day(Range("b" & lastrow_b))
b_month = VBA.Month(Range("b" & lastrow_b))
b_year = VBA.Year(Range("b" & lastrow_b))


For i = (lastrow_b + 1) To lastrow_a
    If b_month < 12 Then
            Range("b" & i).Value = VBA.Conversion.CDate(b_day & "-" & b_month + 1 & "-" & b_year)
            b_month = b_month + 1
    Else
            b_month = 1
            b_year = b_year + 1
            Range("b" & i).Value = VBA.Conversion.CDate(b_day & "-" & b_month & "-" & b_year)
    End If
    Next i



End Sub

 

[alsharif]

Hello Please try this. If works for you or not, please provide the feedback.

I have assumed one row in column b will have one date according to which other rows will be auto filled

Sub date_month()

Dim lastrow_a As Byte
Dim lastrow_b As Integer
Dim b_day As Byte
Dim b_month As Byte
Dim b_year As Integer
Dim i As Integer

lastrow_a = Range("a1").End(xlDown).Row
lastrow_b = Range("b1").End(xlDown).Row

b_day = VBA.Day(Range("b" & lastrow_b))
b_month = VBA.Month(Range("b" & lastrow_b))
b_year = VBA.Year(Range("b" & lastrow_b))


For i = (lastrow_b + 1) To lastrow_a
    If b_month < 12 Then
            Range("b" & i).Value = VBA.Conversion.CDate(b_day & "-" & b_month + 1 & "-" & b_year)
            b_month = b_month + 1
    Else
            b_month = 1
            b_year = b_year + 1
            Range("b" & i).Value = VBA.Conversion.CDate(b_day & "-" & b_month & "-" & b_year)
    End If
    Next i



End Sub

thank you sir for your reply

the result i got form your code is so close but it fill the dates daily not monthly check the pictures

 

[ER_Neha]

thank you sir for your reply

the result i got form your code is so close but it fill the dates daily not monthly check the pictures

The result you are getting from my code is actually your answer but there is some formatting problem, because in you are getting the dates as mm/dd/yyyy

 

[ER_Neha]

Hello please check once

Sub date_month()

Dim lastrow_a As Byte
Dim lastrow_b As Integer
Dim b_day As Byte
Dim b_month As Byte
Dim b_year As Integer
Dim i As Integer

lastrow_a = Range("a1").End(xlDown).Row
lastrow_b = Range("b1").End(xlUp).Row

Range("b1:b" & lastrow_b) = VBA.Format(Range("b1:b" & lastrow_b), ddmmyyyy)
b_day = VBA.Day(Range("b" & lastrow_b))
b_month = VBA.Month(Range("b" & lastrow_b))
b_year = VBA.Year(Range("b" & lastrow_b))


For i = (lastrow_b + 1) To lastrow_a
    If b_month < 12 Then
            Range("b" & i).Value = VBA.Conversion.CDate(b_day & "-" & b_month + 1 & "-" & b_year)
            b_month = b_month + 1
    Else
            b_month = 1
            b_year = b_year + 1
            Range("b" & i).Value = VBA.Conversion.CDate(b_day & "-" & b_month & "-" & b_year)
    End If
    Next i



End Sub

 

[alsharif]

Wooow it really works fantastically.

thank you very much for your time and your effort.

you are the best

 

[ER_Neha]

Wooow it really works fantastically.

thank you very much for your time and your effort.

you are the best

Happy to help. Thank you for your feedback. Please mark the code which works for you as the answer. So that it help others too.

 

[alsharif]

Brother now while im working on the sheet using the code i found that the code affects the enitre B column from the start till the end, but i really need the code to ignore the date before the last date and do not change them , i mean that i need just to Autofill the the column only considering the last value and ignoring the values before because some times the sheet contains old dates before the last date and i do not want to change them, hope you get my point
thank you

 

[ER_Neha]

Brother now while im working on the sheet using the code i found that the code affects the enitre B column from the start till the end, but i really need the code to ignore the date before the last date and do not change them , i mean that i need just to Autofill the the column only considering the last value and ignoring the values before because some times the sheet contains old dates before the last date and i do not want to change them, hope you get my point
thank you

Is the solution working for you or you need the modification.

But actually as you require months to be increased. So in b column all the cells are behaving the same way right ?