Macro to copy-paste value from multiple sheets into one


I have a macro that's basically taking multiple sheets and consolidating them into a summary sheet.

Sub Summary()
    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)(2)
            End If
        Next ws


It works perfectly fine except it pastes the data "as it" as opposed to paste value. Normally I would use:

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks :=False, Transpose:=False

except there isn't actually a paste function within the code. I'm pretty novice and I found the basic structure of the code online so I'm not 100% sure how it works.

Is there anyway to do a paste special without changing the existing code too much? Thanks!!



Selected Answer

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
            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 :-)).



Thank you, that worked perfectly. And thanks for the tip.
Cathy (rep: 19) Nov 29, '18 at 9:19 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login