For Next Loop

  • Hi,


    I am trying to update a report with a list of cost centres and copy each report created to a new tab.


    The sheets in my workbook are called:



    • CC List
    • CC REP


    The steps I am trying to code into a for next loop are as follows:



    • update a specific cell (criteria key for a sum if) on CC REP sheet with a list of cost centre codes from another (one code at a time).
    • Copy the report that the updated cell drives into a new tab



    Code for the For Next Loop


    Code
    Dim Col As Integer
     
        For Row = 1 To 9
        
               Set i = Sheets("CCList").Cells(Row, 1)
               Sheets("CCREP").Range("A8").Value = i
           
               Call Copysheet
            
        Next Row


    Please note the sample cost centre codes are in sheet CCREP, cells A1 to A9



    Code for Copying to a New Tab



    This bit of code works just fine



    Overview


    The issue that I have is that both parts work until I call the copysheet module and then the vba crashes.


    I don't understant the error???


    What i was expecting from this code was the following:



    • CC codes passed to CCREP sheet in cell A8
    • The sheet copied to a new tab as paste special values, and tab renamed as the cost centre code
    • The next cycle of the loop to continue



    This is not the case and if anyone can solve this problem, explain why it will not work, or point me in the right direction I will be very greatful.


    Thank you in advance.


    Matt[Blocked Image: https://www.mrexcel.com/forum/images/smilies/icon_banghead.gif][Blocked Image: https://www.mrexcel.com/forum/images/smilies/icon_banghead.gif][Blocked Image: https://www.mrexcel.com/forum/images/smilies/icon_banghead.gif]

  • Re: For Next Loop


    Maybe use just a single procedure



    This might still fail becasue of the way you are naming to new sheets, since the code takes less than a second to run, the variable "MyDate" will not change for every iteration of the loop, if it does not change then the code will try to rename the new sheet with the same name as an existing sheet, that will cause an error.


    You can try adding a "wait" to the code to ensure that "MyDate" will always be a unique value.


    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: For Next Loop


    It might but the delay might not be enough since the code is so short and fast, imo it is safer to use Application.Wait.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: For Next Loop


    The best alternative would be to change the naming system of the new sheets doing away with the "MyDate" and just using a counter.


    This will name the sheets "<value of CCList cell A8>__1", "<value of CCList cell A8>__2", "<value of CCList cell A8>__3" etc.


    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: For Next Loop


    Hi, and thanks.


    The reason that i am using the Mydate varaible is that it will always generate a unique reference and prevent the vba from crashing. A counter if I understand this right would cause duplicates if the old sheets were not dleted out -they should be, but I want to make it unbreakable-.


    Anyhow I have put the application wait in set for one second and the code does not crash, but appears to run indefinitely with out every finishing., amd when I stop the vba it debugs at the following line:


    Sheets("CCREP").Copy After:=Sheets("CCREP")


    Any ideas?


    Thanks in advance.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!