Pastspecial method of range class failed

[pidnani]

Hi, i am getting an error on the below coding, extracting 2 tabs from all workbooks and paste special as values with updating tab name from cell reference in each file, I get the runtime error of:- Pastspecial method of range class failed --- Any help would be appreciated-- thanks


Sub CombineAllpastespecial()
Const sheetName As String = "GB00 Total"
Const Sheetname2 As String = "Total"
Const Folder As String = "C:\Returns 2014\Latvia\"
Dim fileName As String
Dim wks As Worksheet
Dim wkb1 As Workbook
Dim wkb2 As Workbook
fileName = Dir$(Folder & "*.xls*", vbNormal)
Set wkb1 = Workbooks.Add
Do Until fileName = ""
Set wkb2 = Workbooks.Open(Folder & fileName, , True)

' -----------------------------------------------------------------------------------
On Error Resume Next
Set wks = wkb2.Sheets(sheetName)
On Error GoTo 0

If Not wks Is Nothing Then _
wks.UsedRange.Copy
wkb1.Sheets.Add().Range("A1").PasteSpecial xlPasteValuesAndNumberFormats

wkb1.Sheets(wkb1.Sheets.Count).Name = wks.Range("k3").Value

' -----------------------------------------------------------------------------------

' -----------------------------------------------------------------------------------
wkb2.Close False
fileName = Dir$
Set wks = Nothing
Loop
End Sub

[SujitSingh]

Hi,

Please check this code my be you get idea about error.

Please Login or Register  to view this content.