Copying large amount of cells is making code loop

[JOCoyle]

Hi,

I have some code which opens another workbook, copies two columns and pastes it into the current workbook. Now the code works perfectly, except that it seems to be repeating itself 4 or 5 times before it is done and subsequently the code following it is also repeating.

Wasn't sure if this was intentional or a strange error but please see the code below as is:

Sub PullTelStats()
    LoadStatus = "Data from Telephony report selected..."
    LoadText (LoadStatus)
    Dim sht As Worksheet
    Set sht = Worksheets("AAData")
    Dim wbPath As String
    Dim wbName As String
    Dim thisWB As String
    Const wsName As String = "AgentActivity"
    Dim i As Integer
    Dim x As Integer
    wbPath = StripFilename(FilePath)
    wbName = GetFilenameFromPath(FilePath)
    thisWB = ThisWorkbook.Name
    'Method1:
    'Application.ScreenUpdating = False
    'Application.Calculation = xlCalculationManual
    'With sht
    '    For x = 1 To 1017
    '        .Cells(x, 2).Formula = "='" & wbPath & "[" & wbName & "]" & wsName & "'!$G$" & x
    '        .Cells(x, 3).Formula = "='" & wbPath & "[" & wbName & "]" & wsName & "'!$AO$" & x
    '    Next x
    '    Application.Calculation = xlCalculationAutomatic
    '        .Columns(2).NumberFormat = "General"
    '        .Columns(3).NumberFormat = "General"
    'End With
    'Application.ScreenUpdating = True
    'Method2:
    Workbooks.Open (FilePath)
    Application.ScreenUpdating = False
    Workbooks(wbName).Worksheets(wsName).Range("AO:AO").Copy
    Workbooks(thisWB).Worksheets("AAData").Range("C:C").PasteSpecial Paste:=xlPasteValues
    Workbooks(wbName).Worksheets(wsName).Range("G:G").Copy
    Workbooks(thisWB).Worksheets("AAData").Range("B:B").PasteSpecial Paste:=xlPasteValues
    Application.ScreenUpdating = True
    Workbooks(thisWB).Activate
    Worksheets("Loading").Activate
    LoadStatus = "Finished Data Import, Data Clean..."
    LoadText (LoadStatus)
    LoadAmount = LoadAmount + 10
    AddLB Worksheets("Loading"), Worksheets("Loading").Shapes("LoadBar")
    DataClean
End Sub

As you can see the code I tried previously is to go to the sheet, grab each cell and bring it to the current sheet, however this makes my client lock up for a good 30 seconds before being complete, whereas the copy and paste method is so much quicker.

Thanks,
JC.

 

[MUKESHY12390]

add this after every pastespecial line then try...

application.CutCopyMode = false