Copying cells from one workbook to another and saving an individual file for each cell

[Andyg666]

Hi,
I am new to macros but hope you can help me.
I have a list of items and related information (model, serial number etc) and I need a macro so that I can press a button and export the item name and serial number of each item into another workbook for each item on the original list including a pop up option of where these new workbooks would be saved.

I hope this makes sense, please help

 

[Andyg666]

In your Equipment List file (Sheet1) you have only one item (HP Printer ). Will each data sheet always contain one item or can there be multiple items in column A? If so, that would mean that multiple files for each data sheet would be created. Is this correct?

Yes each piece of equipment tested will be listed for the site, then a certificate for each item created.

 

[mumps]

Just to clarify: Do you have all your pieces of equipment on one data sheet only listed in column A, or do you have multiple data sheets with multiple pieces of equipment on each sheet?

 

[Andyg666]

Just to clarify: Do you have all your pieces of equipment on one data sheet only listed in column A, or do you have multiple data sheets with multiple pieces of equipment on each sheet?

Okay, to clarify let me explain the process.

I go to an office or factory and test equipment in that company as requested. That information is stored on the equipment list.

Parts of the information then are copied into the certificate and the certificate is saved (file name is the name of the equipment).
This certificate is then emailed to the company for the records (they need it in excel) to show it was tested, when and certain other information applicable from the test equipment data

Each piece of equipment will have a new line and its information this can be a few lines of equipment or hundreds and is added too as time goes on. I keep the Equipment list for each company I visit and each company only receives the certificate.

I have a new equipment list for each company I visit. So the form just needs to be saved as a template and can fill it in for the company i am working for and I run the macro as and when needed.

 

[mumps]

I'm not sure if I'm missing something but I'm still a bit confused. So you have a separate data sheet for each company and each data sheet contains multiple pieces of equipment listed in column A. You want to create a new Certificate (file) for each piece of equipment listed in column A for each company. So if Acme Industry has 10 pieces of equipment listed in column A, there would be 10 certificates (files) created for Acme Industry. Then if in another data sheet for company XXX you have 12 pieces of equipment listed in column A, there would be 12 certificates (files) created for XXX. This would be repeated for each company (data sheet). That would mean that if you have let's say 10 companies (10 data sheets), you would create the total number of pieces of equipment for all 10 companies multiplied by 10. Is this correct? If so, would you want to create the certificates for all companies (all data sheets) all in one go or would you want to do only one company at a time?

 

[Andyg666]

I'm not sure if I'm missing something but I'm still a bit confused. So you have a separate data sheet for each company and each data sheet contains multiple pieces of equipment listed in column A. You want to create a new Certificate (file) for each piece of equipment listed in column A for each company. So if Acme Industry has 10 pieces of equipment listed in column A, there would be 10 certificates (files) created for Acme Industry. Then if in another data sheet for company XXX you have 12 pieces of equipment listed in column A, there would be 12 certificates (files) created for XXX. This would be repeated for each company (data sheet). That would mean that if you have let's say 10 companies (10 data sheets), you would create the total number of pieces of equipment for all 10 companies multiplied by 10. Is this correct? If so, would you want to create the certificates for all companies (all data sheets) all in one go or would you want to do only one company at a time?

That's correct but I only need to create certificates for the company equipment I'm working on. So one at a time.

 

[mumps]

In that case, I think it would be helpful if you were given a way to select which company you wanted to process. Can I assume that the sheet name for each company would be named after the company? The data sheet for Acme Industry would be named "Acme Industry"?

 

[Andyg666]

In that case, I think it would be helpful if you were given a way to select which company you wanted to process. Can I assume that the sheet name for each company would be named after the company? The data sheet for Acme Industry would be named "Acme Industry"?

Sorry yes you are correct but each company would be a separate data file kept by me with other files about that company.

 

[mumps]

OK. That would mean that you would have to put the macro in each company file. I should have something for you to try soon.
As an aside, are you clicking then "Post Reply" button when you respond?

 

[Andyg666]

OK. That would mean that you would have to put the macro in each company file. I should have something for you to try soon.
As an aside, are you clicking then "Post Reply" button when you respond?

Yes I will use it on a new file going forward.

Yes I am clicking post reply, why ?

 

[Andyg666]

OK. That would mean that you would have to put the macro in each company file. I should have something for you to try soon.
As an aside, are you clicking then "Post Reply" button when you respond?

Yes I will use it on a new file going forward.

Yes I am clicking post reply, why