Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate a list?
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate a list?
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) wrote in message oups.com... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate a list?
The list is a list of company names and the ones included each week are
determined on their suitability for that week's project. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate a list?
OK, that explains it all.
-- Don Guillett SalesAid Software wrote in message oups.com... The list is a list of company names and the ones included each week are determined on their suitability for that week's project. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate a list?
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate a list?
It was an answer to someone else's question. Bob copied it into this answer
by mistake. You can disregard it. -- Regards, Tom Ogilvy wrote in message oups.com... 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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate a list?
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! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate a list?
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) wrote in message ups.com... 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! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate a list?
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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate a list?
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) wrote in message oups.com... 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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate a list?
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automate sheets list | Excel Discussion (Misc queries) | |||
automate formating a mailing list | Excel Discussion (Misc queries) | |||
I would like to know how to automate graph using drop down list | Charts and Charting in Excel | |||
Trying to automate | Excel Programming | |||
Automate Add-In | Excel Programming |