I am trying to automate some reporting work and am trying to create a vba code that copies certain date from file 2 to file 1. However the code stops and i am getting an error of Application-defined or object-defined error in VBA code. Below is the code
Sub CopyDataFromFiles()
Dim filePath1 As String
Dim filePath2 As String
Dim file1 As Workbook
Dim file2 As Workbook
Dim summarySheet As Worksheet
Dim dataSheet As Worksheet
Dim lastRow As Long
Dim copyRange As Range
Dim pasteRange As Range
' Set the file paths for File 1 and File 2
filePath1 = "C:\Users\User\Documents\Test\Test_Report_Master.xlsx"
filePath2 = GetLastModifiedFile("C:\Users\User\Documents\Test\Daily Reports")
' Open File 1 and File 2
Set file1 = Workbooks.Open(filePath1)
Set file2 = Workbooks.Open(filePath2)
' Set the summary sheet in File 2
Set summarySheet = file2.Sheets("Summary")
' Set the data sheet in File 1
Set dataSheet = file1.Sheets("Data")
' Find the last row in the data sheet of File 1
lastRow = dataSheet.Cells(dataSheet.Rows.Count, "C").End(xlUp).Row
' Copy values from File 2 to File 1
' Copy values from column A and B in the Summary sheet of File 2
Set copyRange = summarySheet.Range("A5:B" & summarySheet.Cells(summarySheet.Rows.Count, "A").End(xlUp).Row)
' Insert a new row in File 1's data sheet
dataSheet.Cells(lastRow + 1, "C").EntireRow.Insert xlShiftDown
' Paste the copied values as values in File 1
Set pasteRange = dataSheet.Range("C" & lastRow + 1)
pasteRange.Resize(copyRange.Rows.Count, copyRange.Columns.Count).Value = copyRange.Value
' Copy the value from cell B4 in the Summary sheet of File 2
Dim lastRowB As Long
lastRowB = dataSheet.Cells(dataSheet.Rows.Count, "B").End(xlUp).Row
Dim copyCell As Range
Set copyCell = summarySheet.Range("B4")
' Find the first blank cell and all blank cells below in column B of File 1's data sheet **(This is where the error happens)**
Dim blankCell As Range
Set blankCell = dataSheet.Range("B" & lastRowB + 1).End(xlDown).Offset(1)
' Paste the copied value in the blank cells of column B
blankCell.Resize(dataSheet.Rows.Count - blankCell.Row + 1).Value = copyCell.Value
' Save and close the workbooks
file1.Close SaveChanges:=True
file2.Close SaveChanges:=False
End Sub
Function GetLastModifiedFile(folderPath As String) As String
Dim lastModifiedFile As String
Dim lastModifiedDate As Date
Dim fileName As String
Dim fileDate As Date
lastModifiedDate = DateSerial(1900, 1, 1)
fileName = Dir(folderPath & "\*.xlsx")
Do While fileName <> ""
fileDate = FileDateTime(folderPath & "\" & fileName)
If fileDate > lastModifiedDate Then
lastModifiedDate = fileDate
lastModifiedFile = folderPath & "\" & fileName
End If
fileName = Dir
Loop
GetLastModifiedFile = lastModifiedFile
End Function
Can anyone help me with this?
Using the vba code I tried to open C:\Users\User\Documents\Test Reports/Test_Report_Master.xlsx (You can assume this as File 1)
and also open the last modified file from folder C:\Users\User\Documents\Test Reports\Daily Reports (You can assume this as file 2).
Then I needed to copy all value from cell A5 and B5 and below in sheet named Summary of file 2 and paste them as value by inserting new row in cell C and D of Table 1 in sheet named Data of file 1. Again. copy the cell B4 in Summary sheet of file 2 and paste it in the first blank cell and all blank cells below of column B of Table 1 in Data sheet of file 1.
Update: As suggested in the comment, I have changed
Set blankCell = dataSheet.Range("B" & lastRowB + 1).End(xlDown).Offset(1)
to
Set blankCell = dataSheet.Range("B" & lastRowB + 1)
However, since I have already copied the data in by inserting a blank row at the end of Table 1 in column C and D of Data Sheet. The Set blankCell = dataSheet.Range("B" & lastRowB + 1) seems to ignore the blank cell in column B up until the recently pasted data in column C and D. It detects the blank cell only after the pasted data and copies it up until cell B1048526. You can see the image below to understand it better.
