Abel Atol
asked on
How to create a Macro that copies format in Sheet 1 to multiple sheets
Hello guys,
I need help with the creation of a Macro that is doing my head...
Let me try to resume so it is super clear for everyone :
SHEET 1 = A simple food plan with Name / client Code / Menu Type and Allergies (then food below). This sheet is a individual and set by client.
SHEET 2 = An order form with the list of clients (order form contains names / client code / Menu type and allergies). This sheet is a whole list of client.
WHAT I WANT = A Macro that would generate the following report
- The macro would open Sheet 3 - Copy the whole format from sheet 1 - paste it in sheet 3 - goes to sheet 2 and copy the data (into the form pasted in sheet 3 from sheet 1) with the following information -> SHEET 2 -> Copy C6 paste it in SHEET 3 cell D4. / SHEET 2 -> Copy F6 and paste it in SHEET cell A1 / SHEET 2 Copy B6 and paste in SHEET 3 cell D5 / SHEET 2 copy K6 and paste in SHEET 3 cell B1.
- When this is done it would rename SHEET 3 with the code present in SHEET 3 Cell B1.
NEXT :
- THe macro would finally repeat as many new sheet with the above process as there are clients listed in SHEET 2 (if there is 5 clients - 5 new sheets would be created, if 20 clients then 20 new sheets). Finally each new sheet would be renamed as per cell B1 in that new sheet.
Thanks a lot guys that would help A LOT.
I need help with the creation of a Macro that is doing my head...
Let me try to resume so it is super clear for everyone :
SHEET 1 = A simple food plan with Name / client Code / Menu Type and Allergies (then food below). This sheet is a individual and set by client.
SHEET 2 = An order form with the list of clients (order form contains names / client code / Menu type and allergies). This sheet is a whole list of client.
WHAT I WANT = A Macro that would generate the following report
- The macro would open Sheet 3 - Copy the whole format from sheet 1 - paste it in sheet 3 - goes to sheet 2 and copy the data (into the form pasted in sheet 3 from sheet 1) with the following information -> SHEET 2 -> Copy C6 paste it in SHEET 3 cell D4. / SHEET 2 -> Copy F6 and paste it in SHEET cell A1 / SHEET 2 Copy B6 and paste in SHEET 3 cell D5 / SHEET 2 copy K6 and paste in SHEET 3 cell B1.
- When this is done it would rename SHEET 3 with the code present in SHEET 3 Cell B1.
NEXT :
- THe macro would finally repeat as many new sheet with the above process as there are clients listed in SHEET 2 (if there is 5 clients - 5 new sheets would be created, if 20 clients then 20 new sheets). Finally each new sheet would be renamed as per cell B1 in that new sheet.
Thanks a lot guys that would help A LOT.
JP
Can you upload a sample to work from.
Is your first row of client data on row 6?
ASKER
sure Thanks Jp let me do that now (hope it ll be clear to you).
In Sheet 1 you will have the cell references in Red so it helps you spot them a bit more clearly.
ZUHOUR-SCORE-CARD.xlsm
In Sheet 1 you will have the cell references in Red so it helps you spot them a bit more clearly.
ZUHOUR-SCORE-CARD.xlsm
You have more going on here that you have listed. I thought I was done but I see you have formulas in Sheet ZG which reference sheet OF. This can be handled it will just take more work.
In you instructions you say to copy Sheet 2 "OF" K6 to Sheet3 B1 then keep doing this for all the clients. Column K is the special instructions. Do you want the sheets to use the customer code or customer name? Also the one formula on sheet1 A1 is pulling from OF! F5, is this correct?
Check out the attached file, I added a button on the OF sheet to run the Macro, If the solution was not as you wanted let me know. I made assumptions since I didn't see a response yet.
ZUHOUR-SCORE-CARD.xlsm
ZUHOUR-SCORE-CARD.xlsm
ASKER
Good point JP,
let me explain.
In Sheet OF you have the listing of all clients with their AC Code, name etc.. The referencing as it is made on Sheet 1 ZG is correct.
Now what i need is simply a macro that would create a SHeet 3 Name it AC068 (which is the code of Abel) and copy paste exactly what s present right on Sheet ZG that you have.
Then Sheet 4 would be called AC012 (the code below) and will be a copy paste of Sheet ZG BUT with the right info relative to it (name will be jason, code will be AC012, Special instructions will be NO, and A1 will be BU (keep them as BU i will change that reference if need be)).
I hope i am clear JP if not please do not hesitate.
let me explain.
In Sheet OF you have the listing of all clients with their AC Code, name etc.. The referencing as it is made on Sheet 1 ZG is correct.
Now what i need is simply a macro that would create a SHeet 3 Name it AC068 (which is the code of Abel) and copy paste exactly what s present right on Sheet ZG that you have.
Then Sheet 4 would be called AC012 (the code below) and will be a copy paste of Sheet ZG BUT with the right info relative to it (name will be jason, code will be AC012, Special instructions will be NO, and A1 will be BU (keep them as BU i will change that reference if need be)).
I hope i am clear JP if not please do not hesitate.
I think we are on the same page now, the attached copy replaces the name with the code for the sheet names,
ZUHOUR-SCORE-CARD.xlsm
ZUHOUR-SCORE-CARD.xlsm
ASKER
JP your macro is called this workbook copy client right?
yes
ASKER
The sheets won t show :( ... strange really.
Are you trying the workbook as I sent it or have you replaced something. It is working for me. Are you getting any error messages?
ASKER
It was the security of the laptop :)
it is working and thank you very much JP.
You are good my friend. Really happy i ve found you :)
it is working and thank you very much JP.
You are good my friend. Really happy i ve found you :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Very good nothing to say.
ASKER
I would love to speak to you again if any need arise in the future. I am not familiar with this platform it is my first time but you are super fast and that is amazing.
Is it possible?
Is it possible?