VBA - copy from one workbook, paste to a different workbook - clipboard issues

[athaung]

I have a Final workbook that needs to have information from many other workbooks. I had originally written a loop that opened a Source workbook, copied information, pasted as values in the Final workbook, closed out of the Source workbook, and went to the next Source workbook.

When I run the code, a message box saying I have a lot of data stored on the clipboard and asking whether I want to save it pops up each time the loop runs. I want to avoid this an make as efficient as possible. Below is my code - the bracketed red code is something I found trolling Excel/VBA forums - I've tried running this piece instead of the copy/pastespecial portion of the code I had originally written, but get an error.

If you have suggestions to make my code more efficient - that'd be great!

Sub Playtime()
Dim ws As Worksheet
Dim MeasNum As String
Dim MeasFile As String
Dim MeasAddress As String
Dim Summary As String


Summary = ThisWorkbook.Name


For Each ws In ThisWorkbook.Worksheets
    If ws.Name = "Master" Or ws.Name = "Template" Then

    Else

        MeasNum = ws.Name
        MeasFile = "meas" & MeasNum & ".xlsx"
        MeasAddress = "S:\MIDS OASIS\Users\BTF\stata\output\profiles\" & MeasFile

        Workbooks.Open Filename:=MeasAddress
       [COLOR="#FF0000"] [Workbooks(MeasFile).Sheets("table1").Range("B3:E8") = Workbooks(Summary).Sheets(MeasNum).Range("H15:K20").Value][/COLOR]
        Sheets("table1").Range ("B3")
        Range(Selection, Selection.End(xlDown)).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Copy

        Windows("Process Measures.xlsm").Activate
        Sheets(MeasNum).Select
        Range("H15").Select
        Selection.PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False

        Windows(MeasFile).Activate
        Sheets("table2").Select
        Range("B3").Select
        Range(Selection, Selection.End(xlDown)).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Copy

        Windows("Process Measures.xlsm").Activate
        Sheets(MeasNum).Select
        Range("H24").Select
        Selection.PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False

        Windows(MeasFile).Activate
        Sheets("table3").Select
        Range("B4:E40").Select
        Selection.Copy

        Windows("Process Measures.xlsm").Activate
        Sheets(MeasNum).Select
        Range("H34").Select
        Selection.PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
'
        Windows(MeasFile).Close False
    End If
Next ws
End Sub

 

[Domenic]

What type of error are you getting? Can you post the error number and description? By the way, with regards to your original code, you can avoid getting that error by cancelling "Copy" mode. You can place the following line of code before closing your workbook...

Application.CutCopyMode = False

 

[athaung]

This did the trick! Thanks, Domenic