Do contents of a cell match existing worksheet name?
 

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

Do contents of a cell match existing worksheet name?
 

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

--
HTH...

Jim Thomlinson


" wrote:

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

Do contents of a cell match existing worksheet name?
 

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

wrote:

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

--

Dave Peterson

Do contents of a cell match existing worksheet name?
 

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!!


Dave Peterson wrote:
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

wrote:

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

--

Dave Peterson