Opening Last Working Day's File And Copying And Pasting Into Different File

[Bendrews]

Hi all,

I am looking for a Macro to open then copy data from an old file and paste into my present file.

Ideally the Macro can search for the old file but doesnt have to (old files are named using CCY and date i.e. AUD 01082010) (or just the previous day, weekends will not be included)

Data i need to copy is from A2 through to K2 going down to the last value in the columns.
This then needs to Paste into my present sheet in M3 .

Any help is much appreciated.

[Excel Fox]

So essentially, there will be multiple files and you want to consolidate all of that in to one single file? And the format of the file names is CCY (which I assume is currency) and date in the format AUD DDMMYYYY. So basically, the pattern will be ??? ######## where a question mark represents a non-numeric character and # a numeric character.

[Bendrews]

I do not want to consildate, i just need to pick up yesterdays file and bring the data into my new file.
yes you assume right CCY is currency

[Excel Fox]

Is this what you are looking for?


Sub DataFromPreviousDay()

Dim wbk As Workbook, wbkThis As Workbook
Const strPath As String = "C:\Folder To Look For File\"
Set wbkThis = ActiveWorkbook
Set wbk = Workbooks.Open(strPath & "AUD" & Format(Application.WorkDay(Date, -1), " DDMMYYYY"))
With wbk.Sheets(1)
.Range("A2:K" & .Cells(.Rows.Count, 1).End(xlUp).Row).Copy wbkThis.ActiveSheet.Range("M3")
End With

End Sub

[Bendrews]

Hi it works :)

is it possible to search and open various Currencies instead of just one. I have others in the same folder with the same names except for the currency shorcode i.e. AUD/NZD. i have looked at using "LEN" but unsure..

[Excel Fox]

Looked at using LEN? Not sure I understand that. You say you have a list of currencies... what are they? Do you have a fixed list? If they are random, do you have an exhaustive list of probable currencies? If the answer is yes to either one of these, we can make a more fungible piece of code. Let me know.

[Bendrews]

i mean using LEN as the reference finder. LEN function returns the length of the specified string. I think Len would be fruitless.

The list i have is NZD, AUD, GBP, EUR

this list could grow in time but atm i only have a need for the four above.

cheers

[bakerman]

A possible solution is a Combobox on your worksheet filled with all available currencies in which you first select the desired CCY and then refer to that selected value in your macro instead of hard-coding your CCy in your code.