To all,

I have a workbook that contains many worksheets named after a date for
example 2007911(1), 2007911(2) etc. (Format - yyyyddmm)

I want to be able to run a macro that will strip all the () (brakets)
out of the dates and insert an _, i.e. turn 2007911(1) into 2007911_1
and 2007911_2 etc.

I then want to be able to copy all the sheets in this format to a
workbook called PAT.xls. and then run or call a macro named 'test'
which is in a module in PAT.xls. I have a worksheet called control
which I do not wish to copy over to PAT.xls.

Is there an easy way to do this?

Thanks


Thanks in advance for your help,

Regards

Joseph Crabtree

Sub fixsheetname()

For Each sht In ActiveWorkbook.Sheets
If InStr(sht.Name, "(") > 0 Then
bracketpos = InStr(sht.Name, "(")
prefixname = Trim(Left(sht.Name, bracketpos - 1))
shtnumber = Trim(Mid(sht.Name, bracketpos + 1))
shtnumber = Trim(Left(shtnumber, InStr(shtnumber, ")") - 1))
sht.Name = prefixname & "_" & shtnumber
End If
Next sht
End Sub

"joecrabtree" wrote:

> To all,
>
> I have a workbook that contains many worksheets named after a date for
> example 2007911(1), 2007911(2) etc. (Format - yyyyddmm)
>
> I want to be able to run a macro that will strip all the () (brakets)
> out of the dates and insert an _, i.e. turn 2007911(1) into 2007911_1
> and 2007911_2 etc.
>
> I then want to be able to copy all the sheets in this format to a
> workbook called PAT.xls. and then run or call a macro named 'test'
> which is in a module in PAT.xls. I have a worksheet called control
> which I do not wish to copy over to PAT.xls.
>
> Is there an easy way to do this?
>
> Thanks
>
>
> Thanks in advance for your help,
>
> Regards
>
> Joseph Crabtree
>

I added to the code the COPY request. the code assumes the workbook PAT.XLS
is opend

Sub fixsheetname()

For Each sht In ActiveWorkbook.Sheets
If InStr(sht.Name, "(") > 0 Then
bracketpos = InStr(sht.Name, "(")
prefixname = Trim(Left(sht.Name, bracketpos - 1))
shtnumber = Trim(Mid(sht.Name, bracketpos + 1))
shtnumber = Trim(Left(shtnumber, InStr(shtnumber, ")") - 1))
sht.Name = prefixname & "_" & shtnumber

With Workbooks("Pat.xls")
sht.Copy after:=.Sheets(.Sheets.Count)
End With
End If
Next sht
End Sub

"Joel" wrote:

>
> Sub fixsheetname()
>
> For Each sht In ActiveWorkbook.Sheets
> If InStr(sht.Name, "(") > 0 Then
> bracketpos = InStr(sht.Name, "(")
> prefixname = Trim(Left(sht.Name, bracketpos - 1))
> shtnumber = Trim(Mid(sht.Name, bracketpos + 1))
> shtnumber = Trim(Left(shtnumber, InStr(shtnumber, ")") - 1))
> sht.Name = prefixname & "_" & shtnumber
> End If
> Next sht
> End Sub
>
> "joecrabtree" wrote:
>
> > To all,
> >
> > I have a workbook that contains many worksheets named after a date for
> > example 2007911(1), 2007911(2) etc. (Format - yyyyddmm)
> >
> > I want to be able to run a macro that will strip all the () (brakets)
> > out of the dates and insert an _, i.e. turn 2007911(1) into 2007911_1
> > and 2007911_2 etc.
> >
> > I then want to be able to copy all the sheets in this format to a
> > workbook called PAT.xls. and then run or call a macro named 'test'
> > which is in a module in PAT.xls. I have a worksheet called control
> > which I do not wish to copy over to PAT.xls.
> >
> > Is there an easy way to do this?
> >
> > Thanks
> >
> >
> > Thanks in advance for your help,
> >
> > Regards
> >
> > Joseph Crabtree
> >