+ Reply to Thread
Results 1 to 4 of 4

Repeat or Loop Process for each value in a list for saving new filesin VBA

  1. #1
    Registered User
    Join Date
    06-03-2013
    Location
    London, England
    MS-Off Ver
    Excel 2016
    Posts
    67

    Repeat or Loop Process for each value in a list for saving new filesin VBA

    Hi,

    Thanks for help in advance.

    I have a piece of code that saves a new version of a file and updates links in the file. The macro bases the name of the file, the links and the directory it is saved in on a cell value on a sheet called Macro. Basically I have a formula that works out what the directory needs to be based on a name I choose in cell J3. The list of companies is in cells A2:A27. At the moment I open the file, choose the target company and then run the macro and do this 26 times. What I would like is to press go, and then for it to take the first company name in the list (A2), copy it, paste it into cell J3, calculate the sheet, run the macro to save a new file, then take the second company name (A3) paste it into cell J3, save as and update etc and repeat the process all the way to the end of the list. In the code below I would only need it to show the message box once at the end not each time. The first If statement below is just checking that I am not telling it to update links to the company it already is looking at in the directory (H8 works out the current company, J8 works out the new company based on the company name I put into J3)


    I think this is probably quite easy but not sure how to set up the loop.

    Please Login or Register  to view this content.


    Thanks again.

    Alex

  2. #2
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,702

    Re: Repeat or Loop Process for each value in a list for saving new filesin VBA

    You could try adding the following to your macro

    Please Login or Register  to view this content.

    Alf

  3. #3
    Registered User
    Join Date
    06-03-2013
    Location
    London, England
    MS-Off Ver
    Excel 2016
    Posts
    67

    Re: Repeat or Loop Process for each value in a list for saving new filesin VBA

    Thanks Alf,

    I have tried that but it seems to keep the original value in J3 and just runs once, it doesnt seem to update the J3 from the list.

    Any more tips would be much appreciated.

  4. #4
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,702

    Re: Repeat or Loop Process for each value in a list for saving new filesin VBA

    This is rather strange as the "For and Next" loop should work without problem.

    Is the values A2 to A27 on the same sheet as the J3 cell where you add the company name?

    Are the company names in A2 to A27 "real" names i.e. not formulas?

    You could try stepping through this code after pasting it in your maco module. Select "Step through" and press F8 to stepp through and check that J3 gets updated,

    Please Login or Register  to view this content.
    Aside from this I really have no idea. Is it possible to upload the file after removing sensitive information?

    Alf

+ 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. [SOLVED] Need to close opened workbook after copying sheet(3) and repeat process using loop.
    By Kammphoto in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-26-2013, 10:55 AM
  2. Need to input value then repeat a process until value exhausted
    By BigMcBen in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-10-2013, 02:02 PM
  3. Macro - Coding to repeat process on different row
    By x3emilygrace in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-29-2011, 12:09 PM
  4. Repeat process on all sheets?
    By Bgeophysics in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-28-2011, 11:39 AM
  5. Replies: 7
    Last Post: 08-31-2010, 12:30 PM

Tags for this Thread

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