+ Reply to Thread
Results 1 to 11 of 11

Run one macro to multiple files

  1. #1
    Registered User
    Join Date
    10-17-2014
    Location
    MN
    MS-Off Ver
    2010, 2013
    Posts
    29

    Run one macro to multiple files

    Hi All!
    So we have about 40 Excel files that we use for employee time sheets. Instead of opening each file and clearing specific cells, is it possible to run one macro across each file?
    I'd like to clear contents of cell "In" and "Out" columns and change the date cell L2 to the beginning of the next pay period.
    If at all possible, I'd like to NOT have to move the files into a separate folder.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    808

    Re: Run one macro to multiple files

    Maybe following code is working for you:
    Please Login or Register  to view this content.
    Just select the files that you want to have cleaned,
    as many as you like, in any map
    The date in L2 is changed in 14 days ahead
    In the macro you have to change the name of the worksheet that has to be cleaned.
    The files will be saved in the same place.
    Watch out, the original files will be overwritten!

    Cheers
    Erwin
    Attached Files Attached Files
    Last edited by Eastw00d; 05-01-2020 at 01:37 AM. Reason: additional information
    I started learning VBA because I was lazy ...
    Still developing.... being more lazy...

  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,105

    Re: Run one macro to multiple files

    The code shall do the described job, so let me just add a comment, that this loop:
    Please Login or Register  to view this content.
    (if I understand it well)
    can be replaced with just:
    Please Login or Register  to view this content.
    As the loop was the only place variable j was used, it's declaration can be also deleted.
    Best Regards,

    Kaper

  4. #4
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    808

    Re: Run one macro to multiple files

    @Kaper,
    you are right,
    thank you for looking into that, it was a piece of code that I used in the past, but it didn't harm the outcome.

    Cheers
    Erwin

  5. #5
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    2,994

    Re: Run one macro to multiple files

    Your other choice, is that if all the files are named consistently, you can open with a mask rather than selections. for example if it is every file that starts with timesheet.
    timesheetdw.xlsx
    timesheetba.xlsx

    but you have not provided this info along the lines of

    https://www.thesmallman.com/opening-files

  6. #6
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    808

    Re: Run one macro to multiple files

    @davsth,
    Thank you for pointing that out!
    But that was indeed a choice of me, as the OP didn't elaborate on that.
    For the same reason I put the warning in red in my post.

    Cheers
    Erwin

  7. #7
    Registered User
    Join Date
    10-17-2014
    Location
    MN
    MS-Off Ver
    2010, 2013
    Posts
    29

    Re: Run one macro to multiple files

    Thanks Guys!
    - Eastw00d: your code ran great! I had to debug and change the worksheet name from Original to Sheet2.
    - daveth: The files names are all different (employee names)
    - Kaper: I see what you mean, just replace Eastw00ds section of the code with your loop

    I really appreciate the comments. I'm not too smart when it comes to Macros, I have to "Record" all mine, but that's very limited when it comes to doing advanced things.

  8. #8
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,105

    Re: Run one macro to multiple files

    Thanks for nice comments.

    If the above answers take care of your original question (I think so), please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, as a relatively not-so-active member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  9. #9
    Registered User
    Join Date
    10-17-2014
    Location
    MN
    MS-Off Ver
    2010, 2013
    Posts
    29

    Re: Run one macro to multiple files

    Hi Eastw00d,
    We had to change Dates in our Timesheets. I ran the Macro, but it didn't update the Dates to the new pay period. Wondering if you would know how I could fix that?
    Instead of starting on a Thursday, we are now starting on Wednesday. So this next pay period will be 6/22 - 7/5.
    Thank you soooo much in advance!
    Attached Files Attached Files

  10. #10
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    808

    Re: Run one macro to multiple files

    Hi 007van,

    the macro is still working, but the content of the sheet has changed.
    B4 is now a fixed value.
    If you change B4 of the "Timesheet - NewVersion.xlsx" in +L2
    things will work much better.

    Cheers
    Erwin
    Last edited by Eastw00d; 06-23-2022 at 12:15 AM. Reason: addition

  11. #11
    Registered User
    Join Date
    10-17-2014
    Location
    MN
    MS-Off Ver
    2010, 2013
    Posts
    29

    Re: Run one macro to multiple files

    Thank you Sooo much Erwin!! I really appreciate you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Macro for Creating one master data files from multiple files in the folder
    By NikhilDixit in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-13-2017, 03:10 PM
  2. Macro to open multiple files, remove header and save multiple files in a new format
    By twocircles in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-17-2014, 05:24 PM
  3. [SOLVED] Data shift with the letter "F" in csv files; macro that uploads multiple csv files
    By schmidt1962 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-19-2013, 01:22 PM
  4. [SOLVED] Macro to Import Multiple TXT Files into workbook - User to select files/directory
    By saber007 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-15-2013, 08:43 PM
  5. [SOLVED] how to convert multiple .txt files to .xlsx files using a macro?
    By Aaron_Tram in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-21-2013, 06:30 PM
  6. extract certain files from multiple zip files macro
    By rdiddy54 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-13-2012, 11:07 PM

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