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

 

[Fluff]

If the file isn't open try

Set wkbDest = [COLOR=#ff0000]WorkBooks.Open[/COLOR]("C:\Users\Work\Desktop\Destination\" & fName & ".csv")

 

[Summer7sun]

thanks fluff .... pastespecial method of range class failed

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

on this line

 

[Fluff]

Could you post your current code, as that line isn't in the last code you posted.

 

[Summer7sun]

Fluff i messed up with the codes ... and previous person who was helping me out said to have a new thread coz he dint have expertise on it... any ways.... all these time was tryin out with the codes ....

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 & ".csv")
        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 & ".csv")
        Sheets(fName.Value).Cells(Sheets(fName.Value).Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
        wkbDest.Close True
        
        Next fName

I got the code to work however the code returns an error saying .csv not found , I suppose that the range is a7:a50 and m using only two files so as a9 to a50 are blank i believe for that it returns this error .... I want it to do until range = "" however cant get it to work.

 

[Fluff]

In that case try

For Each fname In Range("A7:A50").SpecialCells(xlConstants)

 

[Summer7sun]

Fluff Thanks a lot for the code... worked !! without the error

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Summer7sun]

Fluff ... Facing some issue
If i place a file which has huge data in the Today folder "by huge i mean data from a1 z6000 .. I get the clipboard error and after clicking no get this error run time error 1004 pastespecial method of range class failed

on this line

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

how ever if i run the code with less data it works

 

[Fluff]

Either click Yes rather than no, or try this

Application.DisplayAlerts = False
   For Each fname In Master.Range("A7:A50")
   
      Set wkbSource = Workbooks.Open(CreateObject("WScript.Shell").specialfolders("Desktop") & "\Today\" & fname & ".csv")
      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 & ".csv")
      Sheets(fname.Value).Cells(Sheets(fname.Value).Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
      wkbDest.Close True
   
   Next fname
Application.DisplayAlerts = True

 

[Summer7sun]

AWESOME!! Perfect this time. Happy !! Wish i could use the vba to copy paste brain data..... THanks