how to combine only specific files from the list

[Summer7sun]

I have two folders on my "desktop" : Folder 1 named : "Today" and Folder 2 named : "Destination"
In folder named "Destination" I have multiple files : A.xlsx, B.xlsx, C.xlsx with same headers.
In folder 1 have the latest sale data and I want to combine the latest data with the destination files that is A.xlsx to the A.xlsx from destination folder and b.xlsx to b.xlsx.
I have file named "Master" and have the list of file name from destination From A7:A50
I want to combine the files from the list only. Please help

 

[mumps]

For clarification: The files in both folders have the same name. Is this correct? Do you want to add the data from each file in "Today" to the corresponding file in"Destination"? Is the data to be added in one sheet or multiple sheets? What are the sheet names in the source files and destination files? Do the file names in the Master file (A7:A50) include the file extension (xlsx)? What is the name of the sheet containing these file names in the "Master"? Sorry for all these questions but they are necessary to get an exact understanding.

 

[Summer7sun]

Hi! Mumps,
1. Files have same name in both the folders.
2. Files in "Today" folder are latest , Eg- A.xlsx has the latest data and I want the latest data combined or copied and placed in Folder "Destination" file with the A.xlsx in a new line without the header as the header is already in A.xlsx in Destination folder, I want to keep adding latest every day. That is everyday i would have the older data combined with latest.
3. Sheet names are same as the file name .. eg apple.xlsx has the sheet name as apple.
4. I can add the extension if needed ... as of now its just the name eg. apple mango etc
5. Master.xlsx has 1 sheet and the sheet name is Master and the list starts from a7 ... it would be great to have the code to run till a blank cell...


I like your questions mumps ... nothing to be sorry about

 

[mumps]

Place this macro in a regular module in the Master workbook and save the workbook as a macro-enabled file. This will change its extension to "xlsm". Run the macro from there.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim fName As Range
    Dim Master As Worksheet
    Set Master = ThisWorkbook.Sheets("Master")
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    For Each fName In Master.Range("A7:A50")
        Set wkbSource = Workbooks.Open(CreateObject("WScript.Shell").specialfolders("Desktop") & "\Today\" & fName & ".xlsx")
        Sheets(fName.Value).Range("A2", Sheets(fName.Value).Cells.SpecialCells(xlCellTypeLastCell)).Copy
        wkbSource.Close False
        Set wkbDest = Workbooks.Open(CreateObject("WScript.Shell").specialfolders("Desktop") & "\Destination\" & fName & ".xlsx")
        Sheets(fName.Value).Cells(Sheets(fName.Value).Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
        wkbDest.Close True
    Next fName
    Application.ScreenUpdating = True
End Sub

 

[Summer7sun]

thanks for the code however i am getting error .... 1st error there is large file on clipboard and the next error on this line

Sheets(fName.Value).Cells(Sheets(fName.Value).Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial

 

[Summer7sun]

2nd error says pastespecial method of range class failed 1004 error code

 

[mumps]

I tried the macro on some dummy sheets and it worked properly. It is difficult to suggest a working macro when we can't use the actual files. If you received a message that the clipboard contains a large amount of data, that is not an error. What was the error you received on the line you mentioned?

 

[Summer7sun]

I am getting the clipboard popup ... i dont know what to say to that if not error .. LOL... and when i click no then i get an error saying pastespecial method of range class failed with an error code 1004.... I suppose as I have the same file name and excel cant open same file name at once ... so the code needs to copy close then open and paste... I dont know exactly I think may be for this m getting the error

 

[mumps]

When you got the message that the clipboard contains a large amount of data, did you click "Yes" or "No" when you were asked if you wanted to keep the data?

 

[Summer7sun]

tried with yes and also with no ... both gives the same error