Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Sep 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Red face Problem with Excel 2007 macro

    Hi all,

    I'm very new to the VBA world, although I can handle myself with functions in excell. here's the dilema I'm having.

    I'll explain everything and then I'll ask the question.

    I have a directory, with aprox 22 excel files, one per each work day of the month. Every file has 3 sheets (1st sheet with data and the others blank). All files have the same headers. (attached an example). I have to do this every month.

    The first thing I would need to do is consolidate the first sheet of every file into a master file. And have the sheets renamed as the file it came from.

    As a second step, I would need from that master file to copy into a new book, the same row from each sheet. (attached exmaple).

    If anyone could help me I would appreciated it.

    Cheers



    02-09-2014.xlsxSeptember -final file-.xlsm

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    A couple of questions to answer:

    1. Is there naming convention for your 22 sheets?
    2. Are they located in the same folder and are they the only files in the folder or will there be other months and/or files there as well?
    3. what is the row(s) from each sheet you want copied into the new book and do you want them in consecutive rows?
    4. What is the name of the new book to be called?
    5. Will the sheets from the previous month be present in the master workbook?
    6. What is the name of the master workbook?

  3. #3
    New Lounger
    Join Date
    Sep 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Maudibe, thanks for the quick reply.

    1) The naming will be a date, as the example attached (02-09-2014.xlsx). It's going to be the date of each working day of the month.
    2)They are, and will be located in the same folder. I'll create a folder for eah month, so there will only be file for that month.
    3)I want to copy row 2 of each sheet in a new file sheet1, then row 3 on sheet2, row 4 in sheet5, etc In total there are 12 rows to be copied. (I hope that I could have explained myself as I think it in my head)
    4) The name would be the month, but I can change it manually, so it's not that important.
    5) No, I'll create a master file for each month separately.
    6) the name would be "Masterfile".


    Just to clarify, as you can see in the "September -final file-" apart from the copied rows, there's a column with the date of the copied row.

    Cheers

  4. #4
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Depending on your volume, you may?? be creating a lot of extra and unnecessary and undesirable work. You have 22 or so working days in each month. How many entries for each day_______? What goes on the current sheet 2,3, etc.
    It seems that this could be greatly condensed if we saw the desired "masterfile" and interim files with a complete explanation of each step. Actually, this is best left to a professional.

  5. #5
    New Lounger
    Join Date
    Sep 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The attached file " September -final file-" is how I would like it to be. The other attached file, is what I have today (a file for each day on the month).

    It is a mess of a macro, what I'm asking for!!! That's why I posted it, to see if anyone is up for the challenge and helps me at the same time.

    Cheers

  6. #6
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    To repeat what I said, if this is all the data you anticipate for each then simply use ONE sheet & add a column to identify and then use AUTOFILTER. MUCH BETTER. Same with your files feeding it. Possibly you could do ALL with one sheet.

Posting Permissions

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