I have a workbook where I have a loop to copy cell ranges from different workbooks. But I get the "PasteSpecial method of the Range class failed" for a few workbooks. The source workbooks have everything correct.
Below is the procedure code. Can someone help what I am doing wrong here?
Below is the procedure code. Can someone help what I am doing wrong here?
VBA Code:
Private Sub getDataFromWorkbooks(strFilePath)
Dim Wkb As Workbook, wR As Range, sheetExists As Boolean
copyRange1 = CStr(Options.Range("B3").Value) 'copy range is mentioned in cell
copyRange2 = CStr(Options.Range("B4").Value) 'copy range is mentioned in cell
colOffset = CInt(Options.Range("B5").Value) 'offset is mentioned in cell
On Error GoTo FileError
Application.StatusBar = "Opening Workbook " & strFilePath
Set wR = Wks.Range("A1048576").End(xlUp).Offset(1, 0)
Set Wkb = Workbooks.Open(strFilePath, False, True)
sheetExists = IsSheetExist(Wkb, SheetName)
If sheetExists = True Then
Wkb.Sheets(SheetName).Range(copyRange1).Copy
wR.PasteSpecial Paste:=xlPasteValues, Transpose:=True
Wkb.Sheets(SheetName).Range(copyRange2).Copy
wR.Offset(0, colOffset).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
Else
'Log errors in a sheet
ThisWorkbook.Sheets("Log").Range("A1048576").End(xlUp).Offset(1, 0).Value = Now
ThisWorkbook.Sheets("Log").Range("A1048576").End(xlUp).Offset(0, 1).Value = "Information: " & Wkb.Name & " does not have sheet - Matrics. "
ThisWorkbook.Sheets("Log").Range("A1048576").End(xlUp).Offset(0, 2).Value = strFilePath
End If
Wkb.Close False
Set Wkb = Nothing
On Error GoTo 0
Exit Sub
FileError:
ThisWorkbook.Sheets("Log").Range("A1048576").End(xlUp).Offset(1, 0).Value = Now
ThisWorkbook.Sheets("Log").Range("A1048576").End(xlUp).Offset(0, 1).Value = "File Error: Workbook is corrupt or structure is invalid. " & Err.Description
ThisWorkbook.Sheets("Log").Range("A1048576").End(xlUp).Offset(0, 2).Value = strFilePath
Err.Clear
Wkb.Close False
Set Wkb = Nothing
End Sub