Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Macro that requires extraordinary mind (2003)

    Hi,

    I've got stuck somewhere. Actually I have 30 workbooks(for 30 persons)...that contains their data, each workbook contains sheets according to date of a month. like 31 sheets in a workbook for month of january...and so on......I have all these workbooks in a folder ......

    Now, I have another Master workbook......that contains i have 31sheets ( as dates in jan).....in which i copy and paste data for each person. So, I total i have to open all 30 sheets and copy their data for a particular date and then paste it into that master sheet in a pasticular date sheet.

    I want a macro,, that will automatically picks up the data for each person say : from range A1 to L100 from his workbook for a particular date sheet and paste it into the master sheet in that particular date sheet.. like if there is any data in date 20th sheet of a parson's workbook then when i run the macro the data will be automatically pasted into the master sheet in 20th date sheet without opening the person's workbook.



    If anyone can help me in this then i'll be very very thankful to him/her.


    Thanks in advance......


    Ankit

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro that requires extraordinary mind (2003)

    I have a lot of questions:
    1. <LI>Do you want to paste the data below each other in the master sheets?
      <LI>Are there header rows that shouldn't be copied? If so, which ones?
      <LI>Is there a column that will always be filled if there are data in a row? If so, which one?
      <LI>Do the sheets have the same names in the person's workbooks and in the master workbook?
      <LI>Are there sheets that should not be copied?
    If you have other information that could be relevant, please include that in your reply.

  3. #3
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Macro that requires extraordinary mind (2003)

    Hi Hans,

    Please find ur answers below.
    # Do you want to paste the data below each other in the master sheets?
    Yes, I want that when a data from one person's sheet will be pasted then other sheet's data will be pasted exactly below....but the wokbook name should divide their data.....Like A's data will be pasted then B's workbook name will be copied and pasted and then B's data. So that it will be easier to identify the data.

    # Are there header rows that shouldn't be copied? If so, which ones?

    Header Rows are not required.

    # Is there a column that will always be filled if there are data in a row? If so, which one?
    No, as such there is no requirement. But I just want that , if there is any data in a person's sheet ranging from A1 to L100 that will be pasted into master sheet's A1 to L100. That's it ...

    # Do the sheets have the same names in the person's workbooks and in the master workbook?
    Yes, All person's contains sheets date wise like 31 sheets( names as 1,2,3,4,5,6,7,8......31) and the master sheet also contains same number of sheets, 31 sheets...

    Also, the requirement is date no. 1 data will be pasted into date no. 1 sheet of master workbook....and so on.....


    # Are there sheets that should not be copied?
    Yes there are other sheets also in a person's workbook but i want only number sheets data should be copied...1to 31...and nothing else...



    Please get back to me in case of any other queries..


    Thanks for your help.



    Regards,
    Ankit

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro that requires extraordinary mind (2003)

    The requirements

    > Header Rows are not required

    and

    > if there is any data in a person's sheet ranging from A1 to L100 that will be pasted into master sheet's A1 to L100

    are contradictory.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro that requires extraordinary mind (2003)

    I have attached a macro that may do what you want (more or less).
    You can copy the code into a standard module in the master workbook and run it from there.

    You must change the constant at the beginning of the macro to the path of the folder containing the individual workbooks. The path must end in a backslash .

    Create a copy of the master workbook before testing, so that you can easily go back if things go wrong.
    Attached Files Attached Files

  6. #6
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Macro that requires extraordinary mind (2003)

    fantastic!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

    So far, the job done ....is simply ......outstanding .....

    I really appreciate Hans.........In case of any modification......I'll surely get back to you......



    Thanks a lot for ur help.....


    Regards,
    Ankit

  7. #7
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Macro that requires extraordinary mind (2003)

    Hi Hans,


    Well, this is working perfectly fine...but there is a problem ....as i have the person's workbook starting from Sheet "Main"(named as "main") and then other sheets( 1 to 31) are there.



    One more thing, person's workbook contains sheets 1 to 31 as 01, 02, 03 , 04 , 05, 06, 07, 08, 09, 10 and so on......the macro is working only on sheets named as 1,2,3,4,.....31....it is not working for 01, 02......





    Pls. help me in these matter.


    Issue:

    # workbook starting from main Sheet and i want to exclude it..
    # sheets are named as 01,02, 03....

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro that requires extraordinary mind (2003)

    Will the sheets be named 01, 02 etc. in ALL workbooks or just in some of them?

  9. #9
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Macro that requires extraordinary mind (2003)

    in all the workbooks

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro that requires extraordinary mind (2003)

    Try the attached version, it uses Format(i, "00") for the worksheet name instead of CStr(i).

    The presence of worksheets with other names shouldn't matter, such sheets will be ignored.
    Attached Files Attached Files

Posting Permissions

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