[Solved] copy data form a list of files

[bigme]

good morning friends,
i have a task to copy data from a list of file.
i try some code but is not working
what i try to do is open the file and copy using used range (but without the header)
and paste it to sheet Monitoring then close the file and open next file in the list do the same thing
and paste it to last empty rows, i hope someone can correcting my code, thank you.

regards,
bigMe

 

[Yousuf Shovon]

good morning friends,
i have a task to copy data from a list of file.
i try some code but is not working
what i try to do is open the file and copy using used range (but without the header)
and paste it to sheet Monitoring then close the file and open next file in the list do the same thing
and paste it to last empty rows, i hope someone can correcting my code, thank you.

regards,
bigMeView attachment 275View attachment 276

Dear BIGME,
I understand you wish to open the file and copy using the used range without the header and paste it to the sheet Monitoring then close the file and open the next file in the list and do the same thing and paste it to the last empty rows. Fortunately, you can do this by re-calculating LR2 before each paste. Here is the modified code:

Private Sub add_data()
    Dim FilePathList As Variant
    Dim FilePath As String
    Dim LR1 As Long, LR2 As Long
    Dim i As Long, j As Long
    Dim SourceWorkbook As Workbook, TargetWorkbook As Workbook
    Dim SourceWorksheet As Worksheet, TargetWorksheet As Worksheet
    Dim SourceDataRange As Range, TargetDataRange As Range
    
    ' Define the list of file paths
    FilePathList = Array("File1.xlsx", "File2.xlsx", "File3.xlsx")
    
    ' Open the target workbook and worksheet
    Set TargetWorkbook = ThisWorkbook
    Set TargetWorksheet = TargetWorkbook.Worksheets("Monitoring")
    
    ' Loop through each file path in the list
    For j = 0 To UBound(FilePathList)
        ' Set the file path and open the source workbook
        FilePath = FilePathList(j)
        Set SourceWorkbook = Workbooks.Open(FilePath)
        
        ' Copy the data from the used range of the source worksheet
        Set SourceWorksheet = SourceWorkbook.Worksheets(1)
        LR1 = SourceWorksheet.Range("A" & SourceWorksheet.Rows.Count).End(xlUp).Row
        Set SourceDataRange = SourceWorksheet.Range("A2").Resize(LR1 - 1).UsedRange
        
        ' Determine the last empty row in the target worksheet
        LR2 = TargetWorksheet.Range("A" & TargetWorksheet.Rows.Count).End(xlUp).Row + 1
        
        ' Paste the data to the target worksheet
        Set TargetDataRange = TargetWorksheet.Range("A" & LR2)
        SourceDataRange.Copy
        TargetDataRange.PasteSpecial xlPasteValues
        
        ' Close the source workbook
        SourceWorkbook.Close False
    Next j
    
    ' Clear the clipboard and selection
    Application.CutCopyMode = False
    TargetWorksheet.Select
End Sub

In this modified code, I removed the LR2 variable definition from the beginning of the code and moved it inside the loop. This ensures that each file's data is pasted to the next empty row in the "Monitoring" worksheet.

Try this code & let me know if it works.

Regards,
Yousuf Shovon

 

[bigme]

Dear Yousuf,
once again....... your code help me a lot, thank you very much......

best regards,
bigMe

 

[Yousuf Shovon]

Dear Yousuf,
once again....... your code help me a lot, thank you very much......

best regards,
bigMe

Dear BIGME,

We always appreciate your concerns and responses. Follow our https://www.exceldemy.com/ website for Excel problem-solving articles.

Best Regards.