copying data from multiple worksheets

[KJohnson1312]

I creat purchase orders in excel and have a master PO workbook containing about 150 worksheets. How can I copy the vendor name, address, etc from each worksheet and create a mailing list using that information? The template for each PO is the same.

[Damon Ostrander]

Hi Karin,

It is quite easy to write a macro that will grab data from all your sheets and consolidate it on a single sheet, if that is what you mean by a mailing list. If you want to provide a bit more information I could provide the code. I just need to know the cells the name and address are in on all the sheets (I assume they are in the same place on all the sheets), whether the address is split into multiple cells for city, state, zip, etc., and how they should appear on the mailing list sheet.

Also, I think it might be possible to do this without a macro by using the PivotTable and PivotChart Report capability since I believe it has the ability to grab data from multiple sheets and consolidate it on another. Maybe someone with more experience than I with PivotTables and reports can help out here?

[Nimrod]

Hi KJ :
This Macro should do the trick for you . It will go through all 150 sheets of your workbook and copy information to summary sheet.

In this example the summary sheet is called "MailList"..though you can see that at top of macro you can change that.

To add more lines to macro just copy the line Sheets(ML).Cells(TargRow, 1) = SrcWs.Range("A1") and modify the number "1" and the "A1"

The "A1" should be changed to indicate which cell the information is coming from .
The Number "1" indicates which column you want it to be placed in the "Mailing List" sheet.

For example Let's say that the PO's has the person's name in Cell "C12" and you want these names placed in column 3 of the Mailing list then:
-- "A1" needs to be changed to "C12"
-- 1 needs to be changed to 3

If you have 10 pieces of information from each PO collected, just copy the line 10 times and make the appropriate "A1" and (1) changes.

HERE'S THE MACRO

Public Sub MakeMailingList()
ML = "MailList" ' This MUST match the SheetName of your Target MailList
TargRow = 0 ' Change Start Value to change Starting TargetRow, 0=row1
    For Each SrcWs In Worksheets
        If Not (SrcWs.Name = ML) Then
        TargRow = TargRow + 1
            
            '  Target columns here          SourceCells Here
            Sheets(ML).Cells(TargRow, 1) = SrcWs.Range("A1")
            Sheets(ML).Cells(TargRow, 2) = SrcWs.Range("C5")
        
        End If
    Next SrcWs
End Sub

This macro does not require any knowledge of sheet names other then the Target Mailing List sheet.
Don't have any other workbooks open when running macro.

PS Please let me know if this Macro met your needs.





[ This Message was edited by: Nimrod on 2002-05-18 04:13 ]

[Nimrod]

Though I prefer my first version (i.e. refer above) here's a macro that uses the "end" function to find the bottom empty row in column A and starts putting new data in that row. With the first version you set this manually by modifiing the starting row indicator "TargRow=0" .

Public Sub MakeMailingList2()
ML = "MailList" ' Place the Mail list sheet name here
RefCol = 1  ' Col to check for bottom Row i.e. 1 = colA
    For Each SrcWs In Worksheets
        If Not (SrcWs.Name = ML) Then
        TargRow = (Cells(65536, RefCol).End(xlUp).Row + 1)
            
            '  Target columns here          SourceCells Here
            Sheets(ML).Cells(TargRow, 1) = SrcWs.Range("A1")
            Sheets(ML).Cells(TargRow, 2) = SrcWs.Range("C5")
        
        End If
    Next SrcWs
End Sub