Good morning,
I need help with adding a row to a table and pasting a copied selection into the newly added row. Right now I am trying to do it with a Range selection, but I know there must be a way to do it with ListObjects and ListRows.
The line where I Insert and then PasteSpecial located between the equal-sign dividers is clearly wrong. Can someone please help me fix it? Here is the code:
Thank you.
I need help with adding a row to a table and pasting a copied selection into the newly added row. Right now I am trying to do it with a Range selection, but I know there must be a way to do it with ListObjects and ListRows.
The line where I Insert and then PasteSpecial located between the equal-sign dividers is clearly wrong. Can someone please help me fix it? Here is the code:
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:K5").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, "J" & row).Copy
wsdata.Sheets("data").Range("A5:K5").Insert(shift:=xlDown).PasteSpecial (Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False)
wsdata.Sheets("data").Range("J5").Value = MyYear
wsdata.Sheets("data").Range("K5").Value = MyMonth
End If
Next row
'==================================================================================================================
WB.Close
fileName = Dir
Loop
End Sub
Thank you.