Prev: Maintaining Text Box Font Setting (Excel) using VB
Next: "000" reverts back to "0" even with a string
From: Kelly******** on 21 May 2010 23:30
I have a workbook with several sheets these sheets are all the same eccept
for the title. Colunms are Name, heat one, heat two, heat three, total. then
I have a sheet that the colunms are Name, total
How can I make the last sheet with the Name, and total more automatated. I
would like for the Name colunm to be a dropdown of some sort to where it
looks at all the other sheets for names when you see the name you want you
click it. it then fills in the field with that name and puts the total from
the sheet where the name came from in the total field.
I could provide the xls file if need be for a clearer picture of what Im
trying to explain. For that matter if someone would look at it and have a
better way of doing it I would be open to sugestions.
From: joel on 22 May 2010 07:43

If you have a large list of names then you need to create a
consolidated list of names some place in the workbook. You can place
the names in column IV or a hiden column on one of the worksheets.

I think the easiest way is to create a macro that combines the names
and creates a validation list in the last sheet. You would need to run
the macro every time a new name is added but it would be very simple to
adds rows to the last sheet.

What I usually do is to add all the names to one column. Then use
advance filter method to get a lists of unique names.

Try this macro below

Sub MakeValidationList()

Set Sumsht = Sheets("Summary")

For Each sht In Sheets
If UCase(sht.Name) <> "SUMMARY" Then
'copy data to column IU on summary sheet
With sht
'get range of names on sht in column A
'Assume header row so data starts in row 2
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
Set DataRange = .Range("A2:A" & LastRow)
End With

With Sumsht
'get last row of data in column IU
If .Range("IU1") = "" Then
'no names in summary sheet
'put data in header row
'so advance filter works properly
.Range("IU1") = "Names"
End If

LastRow = .Range("IU" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1

'paste names into column
DataRange.Copy _
Destination:=.Range("IU" & NewRow)

End With
End If

Next sht

With Sumsht

'get unique names
LastRow = .Range("IU" & Rows.Count).End(xlUp).Row
.Range("IU1:IU" & LastRow).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("IV1"), _
Unique:=True

'delete temprary data in column IU
.Columns("IU").Clear

LastRow = .Range("IV" & Rows.Count).End(xlUp).Row
Set ValidationNames = .Range("IV2:IV" & LastRow)

'create a validation list in column A in summary sheet
'make the validation range 1000 rows after last data
'so workbook doesn't grow vary large
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
LastRow = LastRow + 1000

'assume header row in column A
Set ValidationRange = .Range("A2:A" & LastRow)

With ValidationRange.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=" & ValidationNames.Address
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

End With

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=204450

http://www.thecodecage.com/forumz

 | 
Pages: 1
Prev: Maintaining Text Box Font Setting (Excel) using VB
Next: "000" reverts back to "0" even with a string