Date reformat and export

[joecrabtree]

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

 

[Joel]

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

 

[Joel]

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