Adding unmerge of cells to existing Macro

[Posky]

Hi All,

I was wondering if as always can get your brilliant expertise in the below macro.

The F3 cell is a merged cell and has unrecognized characters like Period :March 16 and when i run the macro ,so the ":" is invalid. I was wondering if there a way to unmerge the cells and remove the ":" from the cell rather than going into each file to manually take this out...

Many Thanks

Sub CombineAll()
Const sheetName As String = "GB00 Total"
Const sheetName2 As String = "Total"
Const Folder As String = "C:\Returns 2015\Austria\"

Dim fileName As String
Dim wks      As Worksheet
Dim wkb1     As Workbook
Dim wkb2     As Workbook

fileName = Dir$(Folder & "*.xls*", vbNormal)

Set wkb1 = Workbooks.Add

Do Until fileName = ""
    Set wkb2 = Workbooks.Open(Folder & fileName, , True)
   
    On Error Resume Next
    Set wks = wkb2.Sheets(sheetName)
    Set wks = wkb2.Sheets(sheetName2)
    On Error GoTo 0
   
    If Not wks Is Nothing Then _
            wks.Copy , wkb1.Sheets(wkb1.Sheets.Count)
           
           
wkb1.Sheets(wkb1.Sheets.Count).Name = wks.Range("F3").Value
           
   
    wkb2.Close False
    fileName = Dir$
    Set wks = Nothing
Loop

End Sub

 

[Luke M]

I think you can change the one line of code to this:

wkb1.Sheets(wkb1.Sheets.Count).Name = Replace(wks.Range("F3").Value, ":", "")

which just tells the code to remove the colon before trying to set the sheet name.

 

[Posky]

Brilliant--- it works perfectly!!-Thank you very much