Automatically creates sheets from a list, name them and copy particular information into each sheet

Chiwidan

New Member
Joined
Apr 19, 2020
Messages
15
Office Version
  1. 2010
Platform
  1. Windows
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…

1626559661770.png
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: To automatically create a variable number of worksheets, name them and the enter the
And To automatically create a variable number of worksheets, name them and put particular info on them - OzGrid Free Excel/VBA Help Forum
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 

Chiwidan

New Member
Joined
Apr 19, 2020
Messages
15
Office Version
  1. 2010
Platform
  1. Windows
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: To automatically create a variable number of worksheets, name them and the enter the
And To automatically create a variable number of worksheets, name them and put particular info on them - OzGrid Free Excel/VBA Help Forum
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
Perfect... which forum should I use?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
You can use all/any of them. All we ask is that if you cross post you let people know.
 

Forum statistics

Threads
1,141,060
Messages
5,704,042
Members
421,324
Latest member
Devo182

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top