Test this macro on copies of the source and destination workbooks first. The
macro is designed to be run from the destination wb and to select and open
the source wb using a dialog called by the macro. Minimal testing.

Whe
1. wb1 = destination workbook.
2. wb2 = source workbook.
3. wks1 = destination worksheet.
4. wks2 = source worksheet.
5. Destination worksheet name = "Current Sales Data".
6. Source worksheet name = "Sales Data".
7. First destination cell is cell A2 (A1 assumed to be a header).
8. First source cell is cell A2 also.
9. There is assumed to be no gaps in the source "Sales Person" data else the
macro will stop at the gap.

Sub TransferSalesData()
Dim wb1 As Workbook, wb2 As Workbook
Dim wks1 As Worksheet, wks2 As Worksheet
Dim r As Range, c As Range, cc As Range
Dim FNm As Variant

Set wb1 = ActiveWorkbook
Set wks1 = wb1.Sheets("Current Sales Data")
Set c = wks1.Range("A2")
FNm = Application.GetOpenFilename("Excel files(*.xls), *.xls", _
Title:="Transfer Sales Data")
If VarType(FNm) = vbBoolean Then Exit Sub
Application.ScreenUpdating = False
Set wb2 = Workbooks.Open(FNm)
Set wks2 = wb2.Sheets("Sales Data")
wb1.Activate
Set r = wks2.Range(wks2.Range("A2"), wks2.Range("A2").End(xlDown))
For Each cc In r.Cells
If Trim(UCase(cc(1, 4))) = "X" Then
c.Resize(1, 3).Value = cc.Resize(1, 3).Value
Set c = c(2)
End If
Next
Application.ScreenUpdating = True
End Sub

Regards,
Greg


"jacob" wrote:

I made a spreadsheet a couple of years ago and it took forever. I need
something similar and I'm determined to find an easier way!

I have rows of data in Book1. I would like to pull certain columns of
data from these rows and add them up in a neat TOTALS grid in Book2,
with a condition.

For instance, this is my general set up:
Book1
ColumnA: Name (salesperson)
ColumnB: number (units)
ColumnC: number (volume)
ColumnD: (either blank or an "x")

Each row has different names and associated data in the appropriate
columns. I'd like to pull each salesperson's units and volume into
Book2, depending on whether there is an X in Column D (No X = don't
pull over any data from that row; X = pull data over)

So I can write a million IF statements or there is an easier way I'm
hoping someone can point out for me. (And perhaps instead of text "x's"
i could use a button or pivot table or something??)

I hope I've explained what I'm trying to do well enough...if not please
let me know. Thank-you, in advance, for any help you can provide!

Jacob