Several points
1. Can you not lose the "" round the text in the Source and
Destination tables? The single ' at the beginning doesn't matter.
2. As it stands Dest and Source strings in vba do not contain any
Sheet name data, only row/column data. With the quotation marks
removed as suggested in (1) the macro works well copying data on
whichever sheet is the active sheet.
3. Keeping the quote marks in the tables  the following works in the
active sheet:

Dim Dest As String, Source As String
Dest = Mid(Range("Sheet1!F1").Value, 2, _
Len(Range("Sheet1!F1").Value) - 2)
Source = Mid(Range("Sheet1!G1").Value, 2, _
Len(Range("Sheet1!G1").Value) - 2)
Range(Dest).Value = Range(Source).Value

To include Sheet data you can either adjust Dest and Source by adding
that data thus:
Dest = "YTD!" & Dest
Source = "June!" & Source

before the last line in your code,

or instead change the last line to:
Range("YTD!" & Dest).Value = Range("June!" & Source).Value

In the last case, if June is the active sheet when you run the macro
you could pick up the sheet name thus:

Range("YTD!" & Dest).Value = Range(ActiveSheet.Name & "!" &
Source).Value

(that's one line)

and in the case before that as follows:

Source = ActiveSheet.Name & "!" & Source

leaving your original last line as
Range(Dest).Value = Range(Source).Value

at the end.