On Jun 27, 3:54 pm, Socratis
wrote:
" wrote:
On Jun 27, 2:24 pm, Socratis
wrote:
" wrote:
On Jun 27, 9:22 am, Socratis
wrote:
ok so far so good.....only now I can't seem to get all the rows of
data to show up or the other colums. Only the first of the 7 columns
is showing up now. I'm sure I did something wrong. But it isn't
showing the sales rep's name anymore and that's great. I went ahead
and entered a blank line after each sales rep's group of data. Was I
supposed to do it after every entry?

The code uses the current region, an area of cells surrounded by a blank row
and a blank column, so perhaps that's where the problem lies. I assumed that
each of the 7 columns has info in it and there are no blank columns in
between them. Is that correct? If not, then that's the problem.

As far as the blank row, after each sales group is what I meant to say.

Cheers,
Socratis

There was a blank column in the data, That was the problem. But that
brings me another obstacle. The company code column in the report file
is on column but it is a merged column in the active document. I had
inserted the extra column to get the rest of the data in the proper
columns. Have any ideas that could fix it. I tried to insert a column
in the code but I don't think that I knew what I was doing.

Could you give an example, with column headings, of the data you are
bringing in and the location where you want to put it, so I can see what the
actual problem is?

Thanks.
Socratis- Hide quoted text -

- Show quoted text -

sure......

here is the data coming in:
A B C D
E F G
SalesRep Company Loads Cost GrossSpread Margin File Avg
AUTR 1 94 $27,023.67 $9,114.65 0.06 $96.96
AUTR 2 41 $10,114.17 $4,875.64 0.28 $118.92
AUTR 3 21 $3,136.23 $1,254.64 0.37 $59.74
AUTR 4 1 $1,750.00 $416.00 0.24 $416.00
AUTR 5 1 $1,550.00 $219.81 0.14 $219.81
AUTR 6 17 $2,865.36 $346.82 0.09 $20.40

Where I need the data to go is
A B C D
E F G
Company Loads Cost GrossSpread Margin File Avg
1 94 $27,023.67 $9,114.65 0.06 $96.96
2 41 $10,114.17 $4,875.64 0.28 $118.92
3 21 $3,136.23 $1,254.64 0.37 $59.74
4 1 $1,750.00 $416.00 0.24 $416.00
5 1 $1,550.00 $219.81 0.14 $219.81
6 17 $2,865.36 $346.82 0.09 $20.40

The reason for the two columns is due to information that is before
and after this section of the report. (For it to flow and be
structured it needs to be this way) If I just copy the information as
is....the Loads column ends up in Column B and there is no information
in Column G because all the information is off. I hope this makes
sense.

OK, I think we finally got this. Here is the updated version of the macro.

Public Sub GetSalesRecord()
Dim salesRange As Range ' the sales data range, w/out the
header
Dim salespersonRange As Range ' the sales data for one salesperson
Dim companyRange As Range ' the company column

' create a reference to the entire sales data in the report file
Set salesRange = Workbooks("top
carriers.xls").Names("SalesData").RefersToRange

' find cell containing salesperson in question. Salesperson searched must
be included
' in a cell in the report. (Allow space to the right and down for the
actual record, once
' it has been returned.)
Set salespersonRange = salesRange.Find(what:=ActiveCell,
LookIn:=xlValues, lookat:=xlWhole)

' grab the 7xn range of data for this salesperson. Make sure you insert a
blank line after
' each set of sales data in your report
Set salespersonRange = salespersonRange.CurrentRegion

' skip the salesperson's name from the returned data
Set salespersonRange = salespersonRange.Offset(columnoffset:=1)

' extract the first column
Set companyRange = salespersonRange
Set companyRange = companyRange.Resize(columnsize:=1)

' copy company column to active cell in the project
companyRange.Copy Destination:=ActiveCell

' now copy the rest of the data, but first skip the company column
Set salespersonRange = salespersonRange.Offset(columnoffset:=1)
salespersonRange.Copy Destination:=ActiveCell.Offset(columnoffset:=2)
End Sub

Let me know how this turns out.

Cheers,
Socratis- Hide quoted text -

- Show quoted text -

You rock! it worked perfectly! Thank you for all your help! Have a
great week!