I am getting a run-time error 1004 (This operation is attempting to shift cells in a table on your worksheet) when I try to insert a row, shift information down, and paste the copied selection in the inserted row. I don't know why this is an error if this is exactly what I want to do.
Here is the code for reference. The error occurs in the If statement located between the equal-sign dividers.
Thank you in advance.
Here is the code for reference. The error occurs in the If statement located between the equal-sign dividers.
Code:
Option Explicit
Sub Actualizar_informe()
Dim folderPath As String
Dim fileName As String
Dim WB As Workbook
Dim thisWB As Workbook
Dim wsdata As Object
Dim MyYear As Integer
Dim MyMonth As Byte
Set thisWB = ThisWorkbook
Set wsdata = thisWB.Worksheets("data")
Range("A5:J5").End(xlDown).ClearContents
folderPath = "Z:\Incentivos\Electromuebles\Consolidados\Meses Anteriores\Desde 2008"
If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
fileName = Dir(folderPath & "*.xls")
Do While fileName <> ""
MyYear = CInt(Left(fileName, 4))
MyMonth = CByte(Mid(fileName, 5, 2))
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Set WB = Workbooks.Open(folderPath & fileName, UpdateLinks:=False, ReadOnly:=True)
'==================================================================================================================
Dim row As Integer
Dim wsRes As Object
Set wsRes = WB.Worksheets("Resumen")
For row = 1 To 25
If Left(wsRes.Cells(row, 1).Value, 2) = "MC" Or Left(wsRes.Cells(row, 1).Value, 2) = "LS" Then
wsRes.Cells.Range("A" & row, "M" & row).Copy
Rows("5:5").Insert shift:=xlDown
wsdata.Rows("5:5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False
wsdata.Range("N5").Value = MyYear
wsdata.Range("O5").Value = MyMonth
End If
Next row
'==================================================================================================================
WB.Close
fileName = Dir
Loop
wsdata.Columns("E:E").Delete shift:=xlToLeft
End Sub
Thank you in advance.