Results 1 to 5 of 5

Macro - Master file to import data from another open file with variable file name

Threaded View

  1. #1
    Registered User
    Join Date
    12-07-2012
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2007
    Posts
    10

    Macro - Master file to import data from another open file with variable file name

    Hi everyone

    Please bear with me, as I am new to macros.

    I have created a spreadsheet (currently just a test/example) that I will send out via email to staff members.
    They will input their information (eg. name, job title, phone number etc) and email it back (so there will be variable file names).
    When I receive the file back from them, I will open that and run a macro from the master spreadsheet that I will copy the information into.

    This is the macro I have so far... (this is just me testing, I haven't fully developed all the content, hence the simplicity)...

    Sub Copy_data()
    '
    ' Copy_data Macro
    ' copies data from any other open workbook into this workbook
    '
    
    '
        Windows("Jess.xls").Activate
        Range("B1").Select
        Selection.Copy
        Windows("Master.xls").Activate
        ActiveSheet.Paste
        Range("B2").Select
        Windows("Jess.xls").Activate
        Range("B2").Select
        Application.CutCopyMode = False
        Selection.Copy
        Windows("Master.xls").Activate
        ActiveSheet.Paste
        Range("C2").Select
        Windows("Jess.xls").Activate
        Range("B3").Select
        Application.CutCopyMode = False
        Selection.Copy
        Windows("Master.xls").Activate
        ActiveSheet.Paste
        Rows("2:2").Select
        Application.CutCopyMode = False
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("A2").Select
    End Sub
    The problem is, when I receive another file and open it eg. "John.xls" (instead of "Jess.xls"), I try to run the macro, it doesn't work because it is a different file name.

    Is there something I can replace in the line:
    Windows("Jess.xls").Activate
    that will allow me to use whatever the other file name is open (whether it be "Jess.xls", "John.xls", "Mary.xls", "Peter.xls" etc etc) and import the data from that file?
    The "master.xls" is the file that contains the macro that will house all the data.

    I have attached the example master file and two test files that people might send to me.

    Master.xlsJess.xlsJohn.xls

    Thanks in advance!

    Jess.
    Last edited by jdodz; 12-07-2012 at 07:00 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1