How to return multiple cells from a data sheet in a seperate workbook

T

tennisash

I am working on a project right now that I need to automatically
update based on a report that I run every month. Here is what the
report looks like right now.

SalesRep CompanyCode Number Cost
Spread Margin File Avg
AUTR 1 94 $27,023.67 $9,114.65
34.9% $96.96
AUTR 2 41 $10,114.17 $4,875.64
34.9% $118.92
AUTR 3 21 $3,136.23
$1,254.64 34.9% $59.74
AUTR 4 1 $1,750.00 $416.00
34.9% $416.00
AUTR 5 1 $1,550.00
$219.81 34.9% $219.81
AUTR 6 17 $2,865.36
$346.82 34.9% $20.40
AUTR 175 $46,439.43 $16,227.56 34.9%
$92.73
AYLE 1 15 $22,400.00
$5,165.69 25.3% $344.38
AYLE 2 16 $11,575.00
$2,601.70 25.3% $162.61
AYLE 3 15 $9,942.50
$2,045.75 25.3% $136.38
AYLE 4 2 $3,900.00
$1,810.00 25.3% $905.00
AYLE 5 6 $4,880.00
$1,686.73 25.3% $281.12
AYLE 6 96
$104,862.50 $26,509.50 25.3% $276.14
AYLE 150 $157,560.00
$39,819.37 25.3% $265.46

There are 7 colums and 7 rows associated to each salesperson. The
final row is the total for that salesperson for the entire month. What
I would like is to be able to link the worksheets together and return
each corresponding line of information.

The formula I am using now is
=VLOOKUP("autr",'[top carriers.xls]Sheet1'!$A$1:$K$434,{2},0)

This works fine to get the first line of information, but my problem
is that I can't get the last 6 rows of information for each
salesperson. Anyone have any ideas that could help me? I really need
it to be able to automatically update everytime the top carriers
worksheet is updated. Thanks for any ideas you can come up with.
Anything is better than what I have now.
 
S

Socratis

Are u trying to get the entire record (all 7 columns) for each salesman as
one value, or do you want each column to be separate? Basically, how are you
using the information in the first worksheet?

From your post I am assuming you don't want to have a vlookup() function in
49 different cells.

Cheers,
socratis
 
T

tennisash

Are u trying to get the entire record (all 7 columns) for each salesman as
one value, or do you want each column to be separate? Basically, how are you
using the information in the first worksheet?

From your post I am assuming you don't want to have a vlookup() function in
49 different cells.

Cheers,
socratis
Click to expand...

I don't mind having a vlookup in all the different cells. My problem
is just that I can't get the other rows to be found. I want to keep
all the recods seperate. Basically I want it to look exactly like that
but just on a different worksheet without me having to cut and paste
every month for over 50 salespeople. Like I said before, I can use
vlookup and get the first row, but when I try it again for the second
row all I get is the same information that's in the first row. Any
ideas?
 
S

Socratis

See if this macro will work for u:

Public Sub GetSalesRecord()
Dim salesRange As Range
Dim salespersonRange As Range

Set salesRange = Workbooks("book2.xls").Names("SalesData").RefersToRange
Set salespersonRange = salesRange.Find(what:=ActiveCell,
LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlColumns)
Set salespersonRange = salespersonRange.Resize(rowsize:=7 columnsize:=7)
salespersonRange.Copy Destination:=ActiveCell
End Sub

Define this macro in workbook1. Now, in each cell that u would like the 7x7
sales data for a given salesperson to be copied from [top carriers.xls],
enter their name and run the macro. It will copy their 7 rows over. For
example:

1) in A1 enter AUTR
A
1 AUTR

2) run the macro
3) Worksheet now looks like

A B ... G
1 AUTR ......
2 AUTR ......
....
7 AUTR .....

Note: There is a named range, "SalesData" in top [carriers.xls] that
includes all the sales records, including a row header. So, if you have 50
salesmen, this range should be 351x7.

HTH.
Cheers,
socratis
 
T

tennisash

Ok here's the macro that I have. I probably have it all screwed up,
because it isn't working.

Public Sub GetSalesRecord()
Dim salesRange As Range
Dim salespersonRange As Range

Set salesRange = Workbooks("top
carriers.xls").Names("SalesRep").RefersToRange

Set salespersonRange = salesRange.Find(what:=ActiveCell,
LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlColumns)
Set salespersonRange = salespersonRange.Resize(rowsize:=7,
columnsize:=7)
salespersonRange.Copy Destination:=ActiveCell
End Sub

I'm not very good at Visual Basic so it's possible that I don't
understand what I was supposed to be doing. And where am I supposed to
put the range on where to find the data? I think that may be my
problem. But I think this would work if I can make this work. Thanks
for your help!
 
S

Socratis

[email protected] said:
Ok here's the macro that I have. I probably have it all screwed up,
because it isn't working.

Public Sub GetSalesRecord()
Dim salesRange As Range
Dim salespersonRange As Range

Set salesRange = Workbooks("top
carriers.xls").Names("SalesRep").RefersToRange

Set salespersonRange = salesRange.Find(what:=ActiveCell,
LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlColumns)
Set salespersonRange = salespersonRange.Resize(rowsize:=7,
columnsize:=7)
salespersonRange.Copy Destination:=ActiveCell
End Sub

I'm not very good at Visual Basic so it's possible that I don't
understand what I was supposed to be doing. And where am I supposed to
put the range on where to find the data? I think that may be my
problem. But I think this would work if I can make this work. Thanks
for your help!
Click to expand...
First create the macro:
- In the project worksheet, press alt+F11 to open the Visual Basic Editor.
- Select Insert | Module from the menu.
- Paste the code into the newly added module.
In the project worksheet:
- In the cell you want to pull the report data into, add the name of the
- salesperson, i.e. AUTR. Make sure this cell is selected before you
- run the macro (ActiveCell in the code). Also note, this will bring back
- a 7x7 area, so allow some space for that in the worksheet.
In the report worksheet:
- Create a named range ("SalesRep") to include your data. This includes the
- header row and should be 50 salespeople * 7 rows each + 1 header
- = 351 rows x 7 columns.
Run the macro:
- With a cell (containing the salesperson) selected in the project ws,
select
- Tools | Macro | Macro... from the menu and then select the macro name
- and click on run.

HTH

Cheers,
Socratis
 
T

tennisash

ok I have it all in there...but where do I put the 351 rows x 7
columns in the macro? Where does that go? I think that's the problem
that I am having.
 
T

tennisash

ok I have it all in there...but where do I put the 351 rows x 7
columns in the macro? Where does that go? I think that's the problem
that I am having.
Click to expand...

Whenever I debug the macro...it keeps telling me that this line is
wrong..........

Set salesRange = Workbooks("top
carriers.xls").Names("SalesRep").RefersToRange

any ideas?
 
S

Socratis

the 351 x 7 is the named range you must create in the report. Simply
highlight the range and select Insert | Name | Define... from the menu and
then name the range "SalesRep".

Second, make sure the top carriers.xls is open, since the code assumes that.

Cheers,
Socratis
 
T

tennisash

the 351 x 7 is the named range you must create in the report. Simply
highlight the range and select Insert | Name | Define... from the menu and
then name the range "SalesRep".

Second, make sure the top carriers.xls is open, since the code assumes that.

Cheers,
Socratis








- Show quoted text -
Click to expand...

you rock! Thank you sooo much! This worked perfectly.... now if I can
only stop the salesrep name from being returned this would work
awesomely. But so far so good. Is there some kind of code that I can
enter to suppress the first column? Even though that is what the query
is being based upon?

thank you for all your help!
 
T

tennisash

you rock! Thank you sooo much! This worked perfectly.... now if I can
only stop the salesrep name from being returned this would work
awesomely. But so far so good. Is there some kind of code that I can
enter to suppress the first column? Even though that is what the query
is being based upon?

thank you for all your help!- Hide quoted text -

- Show quoted text -
Click to expand...

Also, I have another question...this may not be able to be done
either...but I figured I'd ask. if there are less than 7 rows of data
for the salesrep is there a way to make it not bring in all 7 rows?
Some people may only have one or two lines of information while others
will have all 7.

Thank you for letting me pick your brain.
 
S

Socratis

[email protected] said:
Also, I have another question...this may not be able to be done
either...but I figured I'd ask. if there are less than 7 rows of data
for the salesrep is there a way to make it not bring in all 7 rows?
Some people may only have one or two lines of information while others
will have all 7.

Thank you for letting me pick your brain.
Click to expand...

You are welcome and here is the same macro modified to account for you two
additional requests.

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

' create a reference to the entire sales data in 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 nx7 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)

' copy them to the active cell in the project
salespersonRange.Copy Destination:=ActiveCell
End Sub

The changes I made to the code are:
1) added comments to help you follow the code at a later date
2) grabbed the nx7 sales data
3) skipped the salesperson's name

Now, please make these two changes to the report file.
1) modify the named range to exclude the header row. Follow same procedure
as before.
2) Insert at least one blank line between sales data.

Enjoy and let me know how it turns out.

Cheers,
Socratis
 
T

tennisash

You are welcome and here is the same macro modified to account for you two
additional requests.

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

' create a reference to the entire sales data in 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 nx7 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)

' copy them to the active cell in the project
salespersonRange.Copy Destination:=ActiveCell
End Sub

The changes I made to the code are:
1) added comments to help you follow the code at a later date
2) grabbed the nx7 sales data
3) skipped the salesperson's name

Now, please make these two changes to the report file.
1) modify the named range to exclude the header row. Follow same procedure
as before.
2) Insert at least one blank line between sales data.

Enjoy and let me know how it turns out.

Cheers,
Socratis- Hide quoted text -

- Show quoted text -
Click to expand...

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?
 
S

Socratis

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?
Click to expand...

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
 
T

tennisash

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
Click to expand...

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.
 
S

Socratis

[email protected] said:
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.
Click to expand...

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
 
T

tennisash

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 -
Click to expand...

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.
 
S

Socratis

[email protected] said:
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.
Click to expand...

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
 
T

tennisash

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 -
Click to expand...

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