+ Reply to Thread
Results 1 to 10 of 10

Macro to copy values from specific columns from different workbooks to one sheet

  1. #1
    Registered User
    Join Date
    06-11-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    7

    Macro to copy values from specific columns from different workbooks to one sheet

    Need help real badly, i have to prepare summary report of simulator boxes utilization and prepare trend charts. For this need help on macro.

    Multiple input files(can be in one single directory c:/tmp/): File_date1.xls, File_date2.xls, File_date3.xls, File_date4.xls... 30 files

    Another catch is min, max and avg are formula in input sheet, however need to copy only value to master sheet.

    Sample File_date1.xls file, here every alternate column data is required. physical location between box1 and box2 data is fixed:
    A B C D E F G H I J K
    Box1 xx xx Box2 xx xx Box2 xx xx BoxN
    Min 1 xx xx 1 xx xx 1 xx xx 1
    Max 4 xx xx 4 xx xx 4 xx xx 4
    Avg 3 xx xx 3 xx xx 3 xx xx 3

    required master_file.xls in following format using above multiple files using macro:

    A B C D E F G H I J K
    Box1_Date1 Box1_Date2 Box1_Date3 Box1_Date4 .. ..
    Min 1 1 1 1
    Max 4 4 4 4
    Avg 3 3 3 3

    Box2_Date1 Box2_Date2 Box2_Date3 Box2_Date4 .. ..
    Min 1 1 1 1
    Max 4 4 4 4
    Avg 3 3 3 3

    Box3_Date1 Box3_Date2 Box3_Date3 Box3_Date4 .. ..
    Min 1 1 1 1
    Max 4 4 4 4
    Avg 3 3 3 3

    ..
    ..
    BoxN_Date1 BoxN_Date2 BoxN_Date3 BoxN_Date4 .. ..
    Min 1 1 1 1
    Max 4 4 4 4
    Avg 3 3 3 3
    Attached Files Attached Files

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Macro to copy values from specific columns from different workbooks to one sheet

    Can you explain how you arrive at the output?
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    06-11-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Macro to copy values from specific columns from different workbooks to one sheet

    Sample_file_v2.xlsxFor getting the output, need to copy fixed columns(C,F,I,K,..) i.e select column after 3rd columns from input files and paste in master sheet as rows box1(min,max,avg), box2(min,max,avg),... as daily trends for all 30 days.

    I can change the input files to start data values from C,F,I,K.. columns, which is in my control.

    code flow, what i could think of:

    select 30 xls files from a given directory.

    open masterfile.xls

    for i in 30 xls files
    open $i

    loop every 3rd column to boxN
    select 3rd column
    copy values -> paste to master sheet 2nd column(for next loops paste next column)
    end loop

    close $i
    repeat for next xls file

    Added a sample xls with mapping references.
    Last edited by sksajid7; 06-21-2013 at 03:29 AM.

  4. #4
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Macro to copy values from specific columns from different workbooks to one sheet

    Will the format always be the same? For e.g. the Box numbers are 2 columns away from each other?

    Where do i get the date from for Box1_Date1 ?

  5. #5
    Registered User
    Join Date
    06-11-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Macro to copy values from specific columns from different workbooks to one sheet

    Box numbers are fixed columns(example is 2), however in my actual case its 10 columns away from each other. Yes date is coming from Box1_Date1.

  6. #6
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Macro to copy values from specific columns from different workbooks to one sheet

    I would suggest you re-upload a file which matches your original file exactly. You may have a tough time replicating the code if the formats are different.

  7. #7
    Registered User
    Join Date
    06-11-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Macro to copy values from specific columns from different workbooks to one sheet

    Actual files attached.

    Input file: Perf_Summary_Date1.xls, here you can copy-paste the same file with date2, date3 for testing purpose.

    Master workbook: Perf_DailySummary_2013.xls

    Thanks for your time,
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    06-11-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Macro to copy values from specific columns from different workbooks to one sheet

    solution on this problem will be greatly appreciated.

  9. #9
    Registered User
    Join Date
    06-11-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    7

    need help in resolving this

    need help in resolving this

  10. #10
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Macro to copy values from specific columns from different workbooks to one sheet

    Can you explain your requirement with the help of your new files?

+ Reply to Thread

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