+ Reply to Thread
Results 1 to 10 of 10

data...idea/thoughts

  1. #1
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,224

    data...idea/thoughts

    hello all

    i have this massive task ahead of me and i was wondering if i can get some input/advice on how i should approach this task...

    i will be receiving 180 workbooks. there is a sheet called "weekly update" with approx. 100 rows of data. the worksheet name and # of rows is consistent in all workbook.

    i need to roll this from 180 seperate workbooks into one consolidated workbook "master"....

    what is the best method to do this task?
    -excel indirect.ext?
    -access?

    thoughts/suggestions? pls and thx u
    Last edited by jw01; 02-24-2012 at 01:18 PM.

  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: consolidate data...idea/thoughts

    You can do this in excel - for now excel 2003 will suffice, but as your data grows, you might need excel 2007.
    Are the files stored in a folder, from which you need to open one at a time and consolidate?

    Can you attach a sample of 1 or 2 files? Will the master file have the same format as the individual file?
    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
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,224

    Re: consolidate data...idea/thoughts

    hello arlu

    i have attached a sample file. and yes i am using excel 2007 for this (thx God). Yes, the master file will have the same format except it will store data from 180 files starting in row C10.

    Here is the criteria....

    the files will be stored in the folder below - path of the files:
    C:\Documents and Settings\hh\Desktop\Zoning

    each worksheet has 38 rows of data from C10:X47, with row 9 being the header.

    the worksheet name will all be the same "SE weekly pricing worksheet"...however the file names will vary (Travel Zone - Miami; Travel Zone - New York etc).

    can someone pls help me with a macro or so that will go through the workbooks in the folder mentioned above and and extract the data from C10:X47?

    pls help!!
    Attached Files Attached Files

  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: consolidate data...idea/thoughts

    Use this code
    Please Login or Register  to view this content.
    Open your excel file. Press Alt+F11. You will see Microsoft Excel Objects on the left hand side. Right click and select Insert-> Module. Copy the code over. Go back to the excel file and select View->Macros and run the macro.

    I saw blanks at the end of your data from row 42 to 47. So the code will check the last row of data and copy it over.

  5. #5
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,224

    Re: consolidate data...idea/thoughts

    hello arlu

    thx u so much for your time.

    im having some trouble.

    in my folder, i created a file called "Master". the master workbook contains the macro (i pasted the formula you provided into a module).

    then i simply ran the macro....it is not copying anything over. As a dummy test, i have only 2 files in the folder (1 master and 1 dummy test file).

    also, when i removed the "master" file from the folder and placed it on my desktop....it opens the other dummy test workbook from the "zoning" folder however shows an error "type mismatch on the following line

    Please Login or Register  to view this content.
    can u pls help!! im confused! thx u

  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: consolidate data...idea/thoughts

    Are your files Excel 2003 files or 2007? Also, the Master file shouldnt be in the same folder. If you do need to keep it in the same folder, then let me know and i will add a code line to ensure that the file is not included in the consolidation.

  7. #7
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,224

    Re: consolidate data...idea/thoughts

    hello arlu

    thx u for your prompt response, i really appreciate it.

    i used the template i uploaded and pasted the macro onto that file and it appears to work great!

    also, is it possible for the macro to only copy/paste values? i have drop down list so when i run the macro ...it is trying to overwrite the the drop down list names in the "defined name" section.

    also, one final thing....in my source file...row 50 contains some data....so the macro does not work...is it possible to limit the macro to search only in the range C10:X47 (check the last row +/- within that range)?

    thx u sir!
    Last edited by jw01; 02-24-2012 at 12:21 PM.

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

    Re: consolidate data...idea/thoughts

    Try this out (its the answer to both your questions ) -
    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,224

    Re: data...idea/thoughts

    thx u so much again...it is really appreciated!

  10. #10
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,224

    Re: data...idea/thoughts

    hello arlu

    one final request

    when i run the macro....when the function workbook close occurs...a popup opens each time the other workbook are open to "Save changes YES.NO".....i have to click no each time...is it possible for macro to bypass that and say NO to save changes?

    thx u for your great help!

+ 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