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

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Andyg666]

Hi,
I have managed to attach a couple of made up images of basically what I need to do.
I need a macro to copy certain data out of the data sheet into the test certificate file for each line on the data sheet and then a pop up to ask where these certificates are saved.
These will then be emailed out.

I have a data sheet for each job (site, company or department)

 

[mumps]

Are the job data sheets and Test Certificate sheets all in one file and are they the only sheets in that file? It is hard to work with pictures. Please follow the instructions in Post #2 to add screen shots or to upload your file.

 

[Andyg666]

Are the job data sheets and Test Certificate sheets all in one file and are they the only sheets in that file? It is hard to work with pictures. Please follow the instructions in Post #2 to add screen shots or to upload your file.

Hi, these are two separate sheets and I need the test certificate saved for each item separately with the item name as the file name.

 

[mumps]

I think you may be confusing "two separate sheets" with "two separate workbooks". Are you saying that all the job data sheets are in one workbook and the Test Certificate is in a different workbook? If so, they don't need to be in separate workbooks. It would be easier if the Test Certificate were in the same workbook as the job data sheets. Would that work for you?

 

[Andyg666]

Yes sorry they are two separate workbooks.
I need them to be separate as the data sheet will have more data (that no-one but me will see) than will be show on the certificate which will be emailed out individually to the site.
So the site will get the certificate only.

 

[mumps]

My thinking was to have a blank Certificate in the same workbook as the data sheets. A macro can then go through all the data sheets, fill in the blank Certificate, create a new file for each sheet and each new file can be saved individually. It can work with the Certificate in a separate file. It just makes it easier if a blank certificate were in the same file as the data sheets.

 

[Andyg666]

My thinking was to have a blank Certificate in the same workbook as the data sheets. A macro can then go through all the data sheets, fill in the blank Certificate, create a new file for each sheet and each new file can be saved individually. It can work with the Certificate in a separate file. It just makes it easier if a blank certificate were in the same file as the data sheets.

Sounds like it could work. What's the best way for me to tackle this?

Link to the basic example workbooks - Equipment list.xlsx (Expires 7th May 2024)

 

[mumps]

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?