Pull data from several different sheets into a list

**alesiadick** · 08-15-2012, 11:41 AM

I was advance certified in Excel 97? 2000? (can't remember) but have used lots of excel - so I have knowledge of excel but not macro or programming. A friend is trying to pull data from several different sheets into a list. IE addresses from invoices into a customer list. I have searched the forum and attempted to find a way to do this without a new thread but cannot find what I am looking for. Is there a simple way to reference the text and create a list of data to use in a drop down in future invoices and as a mail merge later? Thank you very much for any help.

**mike7952** · 08-15-2012, 11:47 AM

Can you upload your workbook with some data in it?

**tigeravatar** · 08-15-2012, 11:54 AM

alesiadick,

Welcome to the forum!
Create a sheet named Lists in the workbook, then run this code.
Change the "A1" in ws.Range("A1").Text to be the cell containing the address on each worksheet.
Change the "A1" in the Sheets("Lists").Range("A1")... to be the starting cell for the address list.

Please Login or Register  to view this content.

**alesiadick** · 08-15-2012, 11:56 AM

It's not my workbook.... I was having a conversation around outlook with another person and discussing excel and someone else asked me how to do this. I have not seen the workbook. I think he created an invoice template he has been using for various clients in excel and now wants to consolidate the customer contact information into a single worksheet. I know how to manually reference the cells and can copy and paste and just change the worksheet reference, etc. but if he put all the invoices into a single workbook and named them worksheet 1 and 2 and 3, I was hoping to find a way to list data from a single cell or set of cells into a list without having to individually do so. - (Clear as mud? - I wanted to help him and was hoping it would be a simple fix)

---------- Post added at 10:56 AM ---------- Previous post was at 10:55 AM ----------

Oh thank you!! - I have never run code though, I always created formulas. could you tell me how to run code? (Thank you very much!!)

**tigeravatar** · 08-15-2012, 11:59 AM

How to use a macro:

Make a copy of the workbook the macro will be run on
- Always run new code on a workbook copy, just in case the code doesn't run smoothly
- This is especially true of any code that deletes anything
In the copied workbook, press ALT+F11 to open the Visual Basic Editor
Insert | Module
Copy the provided code and paste into the module
Close the Visual Basic Editor
In Excel, press ALT+F8 to bring up the list of available macros to run
Double-click the desired macro (I named this one tgr)

**alesiadick** · 08-15-2012, 12:01 PM

I will share this with him!! Thank you so much for helping me help someone!! Very nice to meet you all!

Alesia

**tigeravatar** · 08-15-2012, 12:04 PM

You're very welcome. You'll have to let us know if that worked for him

**alesiadick** · 08-15-2012, 12:10 PM

Oh I will let you know how it goes and will mark your thread solved (or ask for more help if necessary) I truly appreciate your willingness and prompt responses in helping me!!!
Alesia