Macro to copy-paste value from multiple sheets into one

Please try this code. It should do the job.

Sub TransferSummary()

    Dim Ws As Worksheet

    For Each Ws In ActiveWorkbook.Worksheets
        If InStr(Ws.Name, "Fcst") > 0 Then
            Ws.Range("FcstArea").Copy
            Sheets("Summary").Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        End If
    Next Ws
End Sub

I couldn't test the exact code I am posting above for obvious reasons but I did test the functionality it implements. It should be OK.

I don't recommend to use Summary as a variable or procedure name in your VBA code. Select the word and press F1. You will find that Excel uses Summary as its own name for an object. Since the its use is different - your procedure doesn't resemble a VBA object - VBA will be able to sort out your intention. However, that may not be the case every time you use a "taken" word. Therefore it is better to be aware of what you are doing. Use F1 frequently to check the names you give, especially if they are very obvious and short, and avoid using them unless you are sure VBA will be able to take care of any conflict (which, your being a novice, isn't likely to be very often :-)).