Excel VBA Populate Column with value from Date Picker

Hi,

pls try

Private Sub DTPicker1_Change()
Sheets("Sheet2").Range("W:W").Value = DTPicker1.Value
End Sub

Select allOpen in new window

Regards

Instead of filling dates in whole Column, try below:
Assigned linked cell for Date Picker in Sheet 1, in my case I assigned F10, change accordingly and change the last row to be filled.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim SrcWs As Worksheet
Dim SrcLR As Long
Set SrcWs = Worksheets("Sheet2")
SrcLR = SrcWs.Range("A" & Rows.Count).End(xlUp).Row 'Change "A" to the last row to be filled

If Target.Cells.Count > 1 Then Exit Sub
    If Not Application.Intersect(Range("F10"), Target) Is Nothing Then
    SrcWs.Range("W2:W" & SrcLR).Value = ActiveSheet.Range("F10").Value
    End If

End Sub

Select allOpen in new window

Kindly note above code should be in Sheet1 module

HI,

I've tried to include it in another macro --

Sub ConvertStartDate()
LastRW = Range("A" & Rows.Count).End(xlUp).Row
Sheets("T+A EXTRACT").Range("W2:W45").Value = DTPicker1.Value
Range("V2:V" & LastRW).Formula = "=RIGHT(RC[-19],2) & ""/"" & MID(RC[-19],5,2) & ""/"" & LEFT(RC[-19],4)"
End Sub

Select allOpen in new window

As ultimately I want the user to click a button for a number of commands to run...

But I get a Object expected error

Sheet 1 is called 'INSTRUCTIONS'

Thanks

How do you assign a linked cell to the datepicker?

Thanks

Try below:

Private Sub DTPicker1_Change()
Dim SrcWs As Worksheet
Dim SrcLR As Long
Set SrcWs = Worksheets("T+A EXTRACT")
SrcLR = SrcWs.Range("A" & Rows.Count).End(xlUp).Row 'Change "A" to the last row to be filled
SrcWs.Range("W2:W45").Value = DTPicker1.Value
SrcWs.Range("V2:V" & SrcLR).Formula = "=RIGHT(RC[-19],2) & ""/"" & MID(RC[-19],5,2) & ""/"" & LEFT(RC[-19],4)"
End Sub

Select allOpen in new window

Could you send a dummy?

in any case the formula should be R1C1

SrcWs.Range("V2:V" & SrcLR).FormulaR1C1 = "=RIGHT(RC[-19],2) & ""/"" & MID(RC[-19],5,2) & ""/"" & LEFT(RC[-19],4)"

Select allOpen in new window

Hi Guys,

I've attached an example of what I'm trying to do... its not working so far :)

ThanksT-A-Macro--2-.xlsm

Shums, thats FANTASTIC thank you....

One more question, now I have column X in which I have written the formula =IF(V2<>W2,"Y","N")

The problem that I have is that it's not working, is this because the data in V is a formula?

Code looks like -

Private Sub DTPicker21_Change()
Dim SrcWs As Worksheet
Dim SrcLR As Long
Set SrcWs = Worksheets("T+A EXTRACT")
SrcLR = SrcWs.Range("A" & Rows.Count).End(xlUp).Row 'Change "A" to the last row to be filled
SrcWs.Range("W2:W" & SrcLR).Value = DTPicker21.Value
SrcWs.Range("V2:V" & SrcLR).Formula = "=RIGHT(RC[-19],2) & ""/"" & MID(RC[-19],5,2) & ""/"" & LEFT(RC[-19],4)"
SrcWs.Range("X2:X" & SrcLR).Formula = "=IF(RC[-2]<>RC[-1],""Y"",""N"")"
End Sub

Select allOpen in new window

Thanks mate

then try

Private Sub DTPicker21_Change()
Dim SrcWs As Worksheet
Dim SrcLR As Long
Set SrcWs = Worksheets("T+A EXTRACT")
SrcLR = SrcWs.Range("A" & Rows.Count).End(xlUp).Row 'Change "A" to the last row to be filled
SrcWs.Range("W2:W" & SrcLR).Value = DTPicker21.Value
SrcWs.Range("V2:V" & SrcLR).FormulaR1C1 = "=RIGHT(RC[-19],2) & ""/"" & MID(RC[-19],5,2) & ""/"" & LEFT(RC[-19],4)"
SrcWs.Range("X2:X" & SrcLR).FormulaR1C1 = "=IF(RC[-2]<>RC[-1],""Y"",""N"")"
End Sub

Select allOpen in new window

Sorry Rgonzo1971 - no joy.

Thanks

Changed;

Private Sub DTPicker21_Change()
Dim SrcWs As Worksheet
Dim SrcLR As Long
Set SrcWs = Worksheets("T+A EXTRACT")
SrcLR = SrcWs.Range("A" & Rows.Count).End(xlUp).Row 'Change "A" to the last row to be filled
SrcWs.Range("W2:W" & SrcLR).Value = DTPicker21.Value
SrcWs.Range("V2:V" & SrcLR).FormulaR1C1 = "=RIGHT(RC[-19],2) & ""/"" & MID(RC[-19],5,2) & ""/"" & LEFT(RC[-19],4)"
SrcWs.Range("X2:X" & SrcLR).FormulaR1C1 = "=IF(RC22<>RC23,""Y"",""N"")"
End Sub

Select allOpen in new window

T-A-Macro--V3.xlsm

FANTASTIC guys - thank you so much  :)

You're Welcome! Glad we're able to help you