Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to match Cell with table of contents | Excel Discussion (Misc queries) | |||
If Cell Contents Don't Match, Move All Data Down One Row | Excel Programming | |||
Automating conform of cell contents so they MATCH | Excel Programming | |||
How do I modify an existing worksheet to remove columns & contents | Excel Discussion (Misc queries) | |||
Copying cell contents to add to existing contents in another cell | Excel Programming |