Clipboard error while paste data

[negi]

Hi All,
I m using below code for merge sheet but its asking for clipboard while paste every file (there is a large amount of information on the clipboard,Do you want to be able to paste this information in to onother program later?.
I have 20K to 40K rows in every sheets.Please help

Sub CopyRange() Application.ScreenUpdating = False
Dim wkbDest As Workbook
Dim wkbSource As Workbook
Set wkbDest = ThisWorkbook
Dim LastRow As Long
Dim irow As Long
Const strPath As String = "D:\New\2015\IMFL\Apr"
ChDir strPath
strExtension = Dir("*.xlsx*")
Do While strExtension <> ""
Set wkbSource = Workbooks.Open(strPath & strExtension)
With wkbSource
irow = Cells(Rows.Count, 34).End(xlUp).Row
'************************************************************************
Range("AL13").Select
With Selection
.MergeCells = True
End With
Selection.UnMerge
Range("U11").Copy Range("AL13:AL" & irow)

Range("AM13").Select

ActiveCell.Formula = "=CONCATENATE(G10,U10)"

Range("AM13").Copy
Range("AM13:AM" & irow).PasteSpecial Paste:=xlPasteValues


'ActiveCell.FormulaR1C1 = "=RC[-3]&RC[-2]"
'Range("F8").Select

' LastRow = .Sheets("Report").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
.Sheets("Report").Range("A13:AM" & irow).Copy wkbDest.Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
.Close savechanges:=False
End With
strExtension = Dir
Loop
Application.ScreenUpdating = True
End Sub

 

[MARK858]

Maybe (change Sheets(1) to suit... probably "Report")...

Sub CopyRange()

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With

    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Dim LastRow As Long
    Dim irow As Long

    Const strPath As String = "D:\New\2015\IMFL\Apr"
    ChDir strPath
    strExtension = Dir("*.xlsx*")

    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wkbSource
            With .Sheets(1)
                irow = .Cells(Rows.Count, 34).End(xlUp).Row
                .Range("U11").Copy .Range("AL13:AL" & irow)

                With .Range("AM13:AM" & irow)
                    .Formula = "=CONCATENATE(G10,U10)"
                    .PasteSpecial Paste:=xlPasteValues
                End With

            End With
            Sheets("Report").Range("A13:AM" & irow).Copy wkbDest.Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop

    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With

End Sub

 

[negi]

thanks sir.
But i have merge cell in AL13 and want to paste the data from U11.

 

[MARK858]

How can you merge a single cell? that you also unmerge directly after merging before the copy. From your code...

Range("AL13").Select
 With Selection
 .MergeCells = True
 End With
 Selection.UnMerge

and the data from U11 is being copy/pasted in the line below . Did you try the code?

.Range("U11").Copy .Range("AL13:AL" & irow)

 

[Norie]

Try clearing the clipboard before closing the files with this.

Application.CutCopyMode  = False