Results 1 to 8 of 8

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

  1. #1
    Junior Member
    Join Date
    Aug 2013
    Posts
    4
    Rep Power
    0

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

    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.

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    Aug 2013
    Posts
    4
    Rep Power
    0
    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

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Is this what you are looking for?

    Code:
    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
    Last edited by Excel Fox; 08-21-2013 at 12:35 AM. Reason: Remvoed the dtmDate variable. Not used in the code
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Junior Member
    Join Date
    Aug 2013
    Posts
    4
    Rep Power
    0
    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..

  6. #6
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  7. #7
    Junior Member
    Join Date
    Aug 2013
    Posts
    4
    Rep Power
    0
    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

  8. #8
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    13
    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.

Similar Threads

  1. #Value Error in Working File
    By Suhail in forum Excel Help
    Replies: 2
    Last Post: 11-19-2012, 11:39 PM
  2. Nth Working Day Including Saturday
    By Excel Fox in forum Download Center
    Replies: 0
    Last Post: 10-10-2012, 02:41 AM
  3. Macro for Opening files and copy the contents of the File
    By ravichandavar in forum Excel Help
    Replies: 16
    Last Post: 08-15-2012, 09:17 PM
  4. Replies: 1
    Last Post: 06-26-2012, 08:17 PM
  5. Opening a Pdf file using VBA
    By littleiitin in forum Excel Help
    Replies: 2
    Last Post: 11-30-2011, 03:05 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •