Do contents of a cell match existing worksheet name?

[cooper.caroline]

Hi All,

I think I'm attempting some coding that's a bit out of my league w/
Excel/VBA. Any advice would be greatly appreciated - even if it's just
"you can't do that, stupid!".

What I'm attempting:

I have a data dump which I'm trying to parse through by looping through
all rows and copying certain rows to certain worksheets depending on
the contents of one particular column/cell within the row. I've already
covered the looping through rows, and the copying/pasting to an
existing worksheet. (Not yet the "create new worksheet" but I don't
expect that to be hard).

What I need help with is the following: I'd like excel to select all
rows in which a particular column/cell has the same value (an
employee's name - I've already sorted the data on name so all
like-employee rows are together), then copy those rows to the
appropriate worksheet. I have no idea how to say "does cell contents
match the name of an existing workbook" in VBA.

As mentioned previously, any tips would be greatly appreciated!

Thanks!

Caroline

 

[Guest]

This code will tell you if a sheet already exists...

sub test
msgbox sheetexists("sheet1")
end sub

Public Function SheetExists(SName As String, _
Optional ByVal Wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If Wb Is Nothing Then Set Wb = ThisWorkbook
SheetExists = CBool(Len(Wb.Sheets(SName).Name))
End Function

 

[Dave Peterson]

You wrote "worksheet name" in the subject, but use "workbook" in the text of
your post.

I'm betting you meant worksheet name.

This is from Chip Pearson:

Function WorksheetExists(SheetName As Variant, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0)
End Function

'and you can use it like:
....
if worksheetexists("myname",activeworkbook) then

======
You may want to look at the way Ron de Bruin and Debra Dalgleish approached it:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

 

[cooper.caroline]

Awesome, thanks so much for the prompt response!! (Yep, I meant
worksheet - typing fast!) Checking out chip's as well as the other
functions. I'm sure one will work. Thanks again!!