* IN SHEET NAME 1

[andrew299]

Thanks to Geoff I can now add a wildcard search into my macro the trouble is it now has to create a sheet with the search name in it.
Is there anyway of changing a variable to remove the * only
i.e

sheetcode = AAA*

sheets.add
activesheet.name= sheetcode

This is how I do it at present

What I want is a sheet just called AAA not AAA*

Does that make sense? Obviously I have no idea of what the codes will be in a given sheet

 

[andrew299]

tHIS IS WHAT i HAVE COM,E UP WITH AS A PROPOSED SOLUTION

AAA*

cut activecell
paste to text file
open text file
delimited with *
xx=activecell.value


Sub wildcut()

Activecell.Select
Selection.Cut


'

But how do I paste to a text file here??

'

Workbooks.OpenText Filename:= _
"C:\wildcut.txt", Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, textQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=True, OtherChar:="*", FieldInfo:=Array(Array(1, 1 _
), Array(2, 1))


End Sub

 

[andrew299]

Open "C:\wildcut.txt" For Output As #1
Write #1, f$,


Close #1

EASY!!

 

[Nikita6003]

Hiya,

This code'll strip out your wilcard character wherever it is in your search name & however many u have

Sub TEST()
    Dim l_sWildCard As String
    Dim l_sNoWildCard As String
    
    l_sWildCard = "AAA*"
    l_sNoWildCard = StripWildCard(l_sWildCard)
    MsgBox "This'll be the SheetName: " & l_sNoWildCard
    
    l_sWildCard = "*BBB*"
    l_sNoWildCard = StripWildCard(l_sWildCard)
    MsgBox "This'll be the SheetName: " & l_sNoWildCard
    
    l_sWildCard = "C*CC"
    l_sNoWildCard = StripWildCard(l_sWildCard)
    MsgBox "This'll be the SheetName: " & l_sNoWildCard
    
End Sub

Function StripWildCard(p_sWildCard As String)
    Dim l_iStripper As Integer
    
    StripWildCard = ""
    
    For l_iStripper = 1 To Len(p_sWildCard)
        If (Mid(p_sWildCard, l_iStripper, 1)) <> &quot;*&quot; Then StripWildCard = StripWildCard & Mid(p_sWildCard, l_iStripper, 1)
    Next l_iStripper
    
End Function

HTH

Cheers
Nikki

 

[andrew299]

Thanks Nikki

have a star

 

[Nikita6003]

My pleasure

& thanks for the star

Cheers
Nikki

 

[TonyJollans]

Hi All,

Am I missing something?

Why can't you just use ...

Sheetcode = Application.WorksheetFunction.Substitute(Sheetcode, "*", "&quot

Enjoy,

Tony