Pull month name and year from main sheet to separate sheet

Question

So I have a main sheet that contains a date column (D). Date is as 01/01/2010. I want to be able to pull month name and year in a VBA subroutine to sheet1 based on other user input. Here is some of the code I'm trying to make work, this is in a sub in mainsheet:

Dim ws2 As Worksheet
Set ws2 = ThisWorkbook.Sheets("mainsheet")
    With ws2 
     '=YEAR(mainsheet!D2)
      ' mn = Text([Date], "mmmm")
       my= .Cells(Year(1, 3))
    End With

After I get the correct month and year (January 2010) I plan to put that in a variable that goes into E1 on sheet1.

I've moved things around, I've used quotes, I've tried range ... it's just not working.

Answer 1

Try this:

Dim ws2 As Worksheet
Set ws2 = ThisWorkbook.Sheets("mainsheet")
    With ws2
     Dim y As Integer, m As String
     y = Year(.Range("D2"))
     m = MonthName(Month(.Range("D2")))
    End With
Set ws2 = ThisWorkbook.Sheets("Sheet1")
    With ws2
     .Range("E1") = m & " " & y
    End With

Answer 2

Here is what I came up with to solve your issue. Given your date is in cell D2 on the mainsheet page this will put the month and year in the format "January 2010" into cell E1 on the same page.

Public Sub test()
    Dim ws2 As Worksheet
    Set ws2 = ThisWorkbook.Sheets("mainsheet")
    With ws2
        yr = Year(.Range("D2"))
        mn = Format(.Range("D2"), "MMMM")
        .Cells(1, 5) = mn & " " & yr
        .Cells(1, 5).NumberFormat = "MMMM YYYY"         
    End With
End Sub

Answer 3

To get the month use:

.cells(1,5) = MonthName(Month(.Cells(1,3).value)

to get year:

.cells(1,5) = Year(.Cells(1,3).value)

edit:

If all you want is the month and date from one cell into another then

 Sheets("TargetSheetName").Cells(1,5) = format(Sheets("DataSheetName").Cells(1,3), "mmmm yyyy")