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]

It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of 2 files from the "Today" folder and the corresponding 2 files from the "Destination" folder to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to each file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[Summer7sun]

its funny had the file deleted and created new files ... saved the files as excel workbook ... checked the extension it says .xlsx..
used the same code without any change.... does the job how ever gives an error sorry we could not find c:\user\work\Today\.xlsx.Is possoble moved or deleted..... but it does the job ...funny.... anyways may be I will b able to deal with that error... thanks for your valuable time and patience ...

 

[mumps]

You had said that your folders were on the desktop. The error gives a different folder path: c:\user\work\Today\.xlsx If you are using this new path, it is looking for a file named ".xlsx" which it won't find and so it generates an error because the name is missing. It should be something like: c:\user\work\Today\sample.xlsx

 

[Summer7sun]

my folder path is C:\Users\work\Desktop\Today\Apple.xlsx . Mumps why does'nt it work with .csv extention ? any idea ...I tried changing the .xlsx to .csv in the code

 

[Summer7sun]

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 ("C:\Users\Work\Desktop\Today\"& fName & ".csv")
Sheets(fName.Value).Range("A2", Sheets(fName.Value).Cells.SpecialCells(xlCellTypeLastCell)).Copy
wkbSource.Close False
Set wkbDest = ("C:\Users\Work\Desktop\Destination\"& fName & ".csv")
wkbDest.Close True
 Next fName
    Application.ScreenUpdating = True
End Sub

modified it to this says object required ...

 

[mumps]

Which line is highlighted?

 

[Summer7sun]

latest code...

Sub CopyData1()
    Application.ScreenUpdating = False
  Dim fName As Range
Set Rng = Sheets("Master").Range("A7")
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("C:\Users\Work\Desktop\Today\" & fName & ".csv")
Sheets(fName.Value).Range("A2", Sheets(fName.Value).Cells.SpecialCells(xlCellTypeLastCell)).Copy
wkbSource.Close False
Set wkbDest = ("C:\Users\Work\Desktop\Destination\" & fName & ".csv")
wkbDest.Close True
Do Until Rng = ""
 Set Rng = Rng.Offset(1, 0)
  Loop
    Application.ScreenUpdating = True
    Next
End Sub

Set wkbDest = ("C:\Users\Work\Desktop\Destination\" & fName & ".csv")

this line is highlighted ... says object required .... btw the file is save is CSV comma dilimited

 

[mumps]

To be honest, I haven't worked with cvs files so I'm not sure how Excel deals with them in terms of manipulating data such as copying and pasting. I know that the macro works as you originally requested. Could I suggest that you start a new thread describing your current problem. I'm sure that there are Forum members who will be able to help.

 

[Summer7sun]

Thank you Mumps

 

[mumps]

My pleasure.