Copy from workbook-changing name (in specific folder) to another named.Workbook

[mlddmc]

I have one workbook of changing name but located in specific folder (Extract) where from 'sheet1' I need to copy range $A$1:$M? (don't now last row as it is greater each time) and paste in a workbook 'NAMED' sheet 'data'


If you can point me the right direction I would appreciate it! I think I can figure out the copy part but I am having trouble doing the point it towards a folder & not a specific file. (I am used to Access and have done it in Access, but the wording is different!)


I greatly appreciate any assistance!

 

[Luke M]

We can certainly write a script to search through a specific folder (check out the help on the Dir method), but you're going to need someone to figure out what the file name is...unless it's the only XL file in the directory.

 

[mlddmc]

It would be the only Excel in a folder named Extracts. Once I have completed all the forumas and Macros in the Excel when I give it to the person who will be in charge of it I plan on having his create a folder on his desktop for the extracts which has a different name each time. These extract have data that needs to be copied into a sheet in a workbook that provides formulas and gives an output.


As I said before though, I can figure out that coupying and I have other Macros I have completed that dragdown the formulas as far as the new data. Am completeing an add resource to the data after the new data is added. But this part eludes me.


Is there a way to do tell it a path such as; path = CurrentProject.path & "Extract"

 

[Luke M]

Here's how to find the file name for unknown file if the folder path is known.

[pre]

Sub FindIt()
Dim MyFile As String

MyFile = Dir(ThisWorkbook.Path & "Extract*.xls*")
MsgBox "The file is named: " & MyFile

End Sub

[/pre]

 

[mlddmc]

Thank you so much! I have wasted so much time on something that seems so simple and can't tell you how much I appreciate you assitance!

 

[Luke M]

No worries mlddmc. I'm just glad that it turned out to be a simple fix. =)