CODEWITHSUNDEEP
vbadateautofill
Binx
Binx

Reputation: 414

Autofill to a specific date

I am working with date data and want to autofill to a specific date. The specific date will be stored as value X. X is the last value in column A

I've looked at some other posts here on StackOverflow, but the issue I keep running into is setting my final "value". My final value will not be a number, but a date to stop at.

    With ThisWorkbook.Sheets("Form Date 2")
    'Select this workbook

    Dim X as Date 
    'I am not sure what I need to Dim this as

    Range("B2").Value = Range("A2").Value
    'B2 is the value I want to autofill down with

    X = Range("A" & Rows.Count).End(xlUp).Value
    'X will be the last value in column A

    Range("B2").Autofill Destination:=Range("B2:B" & X), Type:=xlFillSeries
    'Not sure what to set my Type as

    End With

So the final result would be, Column B would obtain all dates from A1 to last value in A (02/04/2018-05/06/2018). Format should be dd/mm/yyyy.

In Column A I have a whole bunch of dates, but some are missing, so I am grabbing all the missing ones as well.

With the picture below I have the Dates column. I want VBA to spit out the All Dates column. So The code will copy and paste A2 over to B2 and then autofill until the last value in column A(not row). So in this example All Dates should continue down until value/date 01/06/2018.

enter image description here

Upvotes: 1

Views: 1560

Answers (2)

Mikku
Mikku

Reputation: 6654

Perhaps this will work:

With ThisWorkbook.Sheets("Sheet2")
'Select this workbook

Dim X As Integer
'I am not sure what I need to Dim this as

.Range("B2").Value = .Range("A2").Value
'B2 is the value I want to autofill down with

X = .Range("A" & .Rows.Count).End(xlUp).Row
'X will be the last value in column A

.Range("B2").Select
Selection.AutoFill Destination:=.Range("B2:B" & X), Type:=xlFillDefault
'Not sure what to set my Type as

End With

Updated Code to fill till the date on the last cell of Column A:

With ThisWorkbook.Sheets("Sheet2")
'Select this workbook

Dim X As Integer
'I am not sure what I need to Dim this as

.Range("B2").Value = .Range("A2").Value
'B2 is the value I want to autofill down with

X = .Range("A" & .Range("A" & .Rows.Count).End(xlUp).Row).Value - .Range("B2").Value
'X will be the last value in column A


.Range("B2").Select
Selection.AutoFill Destination:=.Range("B2:B" & X + 2), Type:=xlFillDefault
'Not sure what to set my Type as

End With

Upvotes: 1

Hippolyte BRINGER
Hippolyte BRINGER

Reputation: 853

This code works fine :

Sub test()

Dim X As Integer

ThisWorkbook.Sheets("Sheet2").Range("B2").Value = Range("A2").Value
'B2 is the value I want to autofill down with

X = ThisWorkbook.Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
'X will be the last value in column A

ThisWorkbook.Sheets("Sheet2").Range("B2").AutoFill Destination:=ThisWorkbook.Sheets("Sheet2").Range("B2:B" & X), Type:=xlFillDefault

End Sub

You're welcome :)

Upvotes: 0

Related Questions