Automate a list?

Hi, I need some advice.

I have a list of 100 names in one column and to the right of that I
have several additional columns of data linked to those names.

I produce a one page report every week that contains selected names
from the list along with the other data linked to those names to the
right. I usually put the names inside a border box to dress up the
appearance. The size of the list varies from about 5 names to 20 at
most.

Is there a way I can automate this process say with a bunch of drop
down or combo boxes linked to the list in another spreasheet? I'm
getting tired of copying and pasting the names into the report every
week.

Thanks for any ideas you can offer.

What determines which names are selected?

--
Don Guillett
SalesAid Software
[email protected]
<[email protected]> wrote in message
news:[email protected]...
> Hi, I need some advice.
>
> I have a list of 100 names in one column and to the right of that I
> have several additional columns of data linked to those names.
>
> I produce a one page report every week that contains selected names
> from the list along with the other data linked to those names to the
> right. I usually put the names inside a border box to dress up the
> appearance. The size of the list varies from about 5 names to 20 at
> most.
>
> Is there a way I can automate this process say with a bunch of drop
> down or combo boxes linked to the list in another spreasheet? I'm
> getting tired of copying and pasting the names into the report every
> week.
>
> Thanks for any ideas you can offer.
>

Bob,

Here is a little macro for you. It is driven from a list of items on another
worksheet, and puts it on a third.

Sub Test()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim sh3 As Worksheet
Dim iLastRow As Long
Dim iLastCol As Long
Dim iRow As Long
Dim iPos As Long
Dim i As Long

Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
Set sh3 = Worksheets("Sheet3")

sh3.Cells.Clear
With sh2
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
On Error Resume Next
iPos = Application.Match(.Cells(i, "A").Value, sh1.Range("A:A"),
0)
On Error GoTo 0
If iPos > 0 Then
iRow = iRow + 1
sh1.Rows(iPos).Copy sh3.Cells(iRow, "A")
End If
Next i
iLastCol = sh3.Cells(1, sh3.Columns.Count).End(xlToLeft).Column
sh3.Columns("A:B").Insert
sh3.Rows("1:2").Insert
sh3.Range("A1").Offset(1, 1).Resize(iRow + 2, iLastCol +
2).BorderAround _
ColorIndex:=3, Weight:=xlThick
ActiveWindow.DisplayGridlines = False
sh3.Activate
End With

End Sub

Sub bob()
Dim Range1 As Range
Dim Range2 As Range
Dim Range3 As Range
Dim RangeArray
Dim m As Long

Set Range1 = Worksheets(1).Range("A1:D5")
Set Range2 = Worksheets(1).Range("A11:D15")
Set Range3 = Worksheets(1).Range("A21:D25")
RangeArray = Array(Range1, Range2, Range3)
m = 1
MsgBox RangeArray(m).Address

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


<[email protected]> wrote in message
news:[email protected]...
> Hi, I need some advice.
>
> I have a list of 100 names in one column and to the right of that I
> have several additional columns of data linked to those names.
>
> I produce a one page report every week that contains selected names
> from the list along with the other data linked to those names to the
> right. I usually put the names inside a border box to dress up the
> appearance. The size of the list varies from about 5 names to 20 at
> most.
>
> Is there a way I can automate this process say with a bunch of drop
> down or combo boxes linked to the list in another spreasheet? I'm
> getting tired of copying and pasting the names into the report every
> week.
>
> Thanks for any ideas you can offer.
>

The list is a list of company names and the ones included each week are
determined on their suitability for that week's project.

OK, that explains it all.

--
Don Guillett
SalesAid Software
[email protected]
<[email protected]> wrote in message
news:[email protected]...
> The list is a list of company names and the ones included each week are
> determined on their suitability for that week's project.
>

Bob,

Thanks for the macro. I got it to work a few times. I wish my VBA
skills were better so I understood what you gave me.

The second macro is a mystery to me. What is that one supposed to do?

Thanks again!

It was an answer to someone else's question. Bob copied it into this answer
by mistake. You can disregard it.

--
Regards,
Tom Ogilvy

<[email protected]> wrote in message
news:[email protected]...
> Bob,
>
> Thanks for the macro. I got it to work a few times. I wish my VBA
> skills were better so I understood what you gave me.
>
> The second macro is a mystery to me. What is that one supposed to do?
>
> Thanks again!
>

Is there a way to modify the code so:

1. It only looks at one sheet to print the list? (now it seems to
require the same list on two sheets - 1 & 2)

2. So it closes a gap if there is a gap in the list? (now it repeats
the entry before a gap)

Thanks!

Bob,

The problem with request #1 is that at present the macro copies the complete
row. If we put the selection list somewhere on sheet1, it is likely that
would get copied also. Can we restrict the copy to a set number of columns
rather than the whole row?

I am not really clear as to what is meant by #2? Are you referring to the
blanks that I put in there in rows 1 & 2 and columns A & B (which I hoped
would make the report clearer :-))?

--

HTH

RP
(remove nothere from the email address if mailing direct)


<[email protected]> wrote in message
news:[email protected]...
> Is there a way to modify the code so:
>
> 1. It only looks at one sheet to print the list? (now it seems to
> require the same list on two sheets - 1 & 2)
>
> 2. So it closes a gap if there is a gap in the list? (now it repeats
> the entry before a gap)
>
> Thanks!
>

1. Yes, we can limit it to 5 columns for each row.

2. If my list has a blank row between entries, I would like it to close
that space in the report without repeating the entry in the row before
the blank row.

Thanks again for your help on this.

Not getting 2 at all. If you are only copying matching data, how does
blanks affect it? They don't get copied in my tests.

Here is the code for same sheet

Sub TestData()
Const WS_CHECKCOLUMN As String = "M"
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim iLastRow As Long
Dim iLastCol As Long
Dim iRow As Long
Dim iPos As Long
Dim i As Long

Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")

sh2.Cells.Clear
With sh1
iLastRow = .Cells(.Rows.Count, WS_CHECKCOLUMN).End(xlUp).Row
For i = 1 To iLastRow
On Error Resume Next
iPos = Application.Match(.Cells(i, "A").Value, _
sh1.Range("A:A"), 0)
On Error GoTo 0
If iPos > 0 Then
iRow = iRow + 1
sh1.Cells(i, "A").Resize(, 5).Copy sh2.Cells(iRow, "A")
End If
Next i
iLastCol = sh2.Cells(1, sh2.Columns.Count).End(xlToLeft).Column
sh2.Columns("A:B").Insert
sh2.Rows("1:2").Insert
sh2.Range("A1").Offset(1, 1).Resize(iRow + 2, 5 + 2).BorderAround _
ColorIndex:=3, Weight:=xlThick
ActiveWindow.DisplayGridlines = False
sh2.Activate
End With

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


<[email protected]> wrote in message
news:[email protected]...
> 1. Yes, we can limit it to 5 columns for each row.
>
> 2. If my list has a blank row between entries, I would like it to close
> that space in the report without repeating the entry in the row before
> the blank row.
>
> Thanks again for your help on this.
>

1. the matrix of data for the report would be up to 300 rows with 5
columns of data and each row representing one record

2. the earlier macro seemed to print a report that would duplicate the
last row if the next row was blank

I will have a list of up to 300 company names in the first column, then
associated information about each company would appear in the next 4
columns to the right of the names. I would select up to 30 companies
from the list of 300 and there would be some blank rows between the
selected companies. So my goal is to put in the box those 30 companies
from the matrix area.

Thanks for all your help so far and I don't want to burden you with any
more of this and I think I can work with what you have posted so far.
I will try to modify it to get to my end goal.

Thanks again!