To automatically create a variable number of worksheets, name them and put particular info on them

  • [xpost]

    [/xpost]

    Hi folks,

    I wrote a couple of weeks about this but now I have some of the macros written…

    How can I attach the file with my work so far?

    Everything starts when the user manually enters the info in the cells in blue:

    - NHI (to identify the person)

    - CAT (the client code)

    - Units to recover

    - …

    - Balance in funding to use

    The first macro, Macro1 of Module 1:

    - Creates the sheet Calculations

    - Counts and lists how many different Invoices there would be (in column Macros!K:K).

    - Creates a list of those many invoices and places them in Macros!A26:A33

    - Takes the “Units to Recover” in Macros!C:C, and splits them into one number with a decimal and then bigger, multiple of 10 figures, such as 50, 100, 500, 1000. To do that, considers that the total does not exceed the available (in column Macros!M)

    The second and third macros, Prepare_Invoices1 and Prepare_Invoices2() in Module 2:

    - Creates the sheet “Data for Invoices”

    - Creates a table for the Info in each of the tables of Calculations to go into so it can filter the blanks/empty values

    - You can see I need more understanding of looping because you might be easily able to do that with a loop but, due to my lack of knowledge, I needed to write the code for each of the tables in Calculations.

    - The filtered info is placed in Data for Invoices in I2, N2, S2….

    The fourth macro, Invoice_Making:

    - Create the sheet “Invoice Template”

    - Populates columns and formulas required for each template.

    And this is when I struggle with:

    This is what I need to do:

    1nd

    Copy the value of the first Cell in Macros!A24:A33 to cell $C$2 in the Invoice Template sheet

    Copy the value of the respective cells in the Data for Invoices, in this case, those ones that are in the list starting from I3 to L3 into the respective cells in the Invoice Template

    2nd

    I need to create a new sheet and name it according the Invoice, as showing in the Macros!A24:A33

    I need this to loop through all the values in the Macros!A24:A33.

    I started doing some coding to create the invoice pages… not sure how to go from there…

    I notice there might be an issue:

    When preparing the information to copy in the invoices, this is copied to the “Data for Invoices sheets but starting from different cells, e.g I2, N2, S2… AH2.

    This is because I was incapable to do this through a loop due to lack of knowledge…

    Perhaps doing this in a different way can help with the following part, the loop to create the invoices.

    Please help!!!!

    This has taking me a lot of learning, headaches, trial and error but I guess this is how we all started…



    [Blocked Image: https://www.mrexcel.com/board/attachments/1626559661770-png.42996/]

  • Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the URL from the address bar in your browser) to the cross-post. We are here to help so help us help you!




    Read this to understand why we ask you to do this



    https://www.excelguru.ca/content.php?184

Participate now!

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