Add Spaces Before Capitals - Multiple Worksheets

[Eccelerre]

Would someone please help me with this dilemma? I'm trying to add spaces before non-repeating capital letters in the first row of every sheet in my workbook. I continually deal with workbooks with hundreds of sheets, each with a first row containing column headers that import without spaces.

Example: change "SpacesWouldBeOK" to "Spaces Would Be OK"

I'm trying to understand and learn this stuff, but my mashups of code from various sites have not worked. The closest code I've worked with came from extendoffice.com:

<code class="vb keyword">

Sub</code> <code class="vb plain">AddSpacesRange()</code>

<code class="vb comments">'Update 20140723</code>
<code class="vb keyword">Dim</code> <code class="vb plain">Rng </code><code class="vb keyword">As</code> <code class="vb plain">Range</code>
<code class="vb keyword">Dim</code> <code class="vb plain">WorkRng </code><code class="vb keyword">As</code> <code class="vb plain">Range</code>
<code class="vb keyword">Dim</code> <code class="vb plain">xOut </code><code class="vb keyword">As</code> <code class="vb keyword">String</code>
<code class="vb keyword">Dim</code> <code class="vb plain">xValue </code><code class="vb keyword">As</code> <code class="vb keyword">String</code>
<code class="vb keyword">On</code> <code class="vb keyword">Error</code> <code class="vb keyword">Resume</code> <code class="vb keyword">Next</code>
<code class="vb plain">xTitleId = </code><code class="vb string">"KutoolsforExcel"</code>
<code class="vb keyword">Set</code> <code class="vb plain">WorkRng = Application.Selection</code>
<code class="vb keyword">Set</code> <code class="vb plain">WorkRng = Application.InputBox(</code><code class="vb string">"Range"</code><code class="vb plain">, xTitleId, WorkRng.Address, Type:=8)</code>
<code class="vb plain">Application.ScreenUpdating = </code><code class="vb keyword">False</code>
<code class="vb keyword">For</code> <code class="vb keyword">Each</code> <code class="vb plain">Rng </code><code class="vb keyword">In</code> <code class="vb plain">WorkRng</code>
<code class="vb spaces">    </code><code class="vb plain">xValue = Rng.Value</code>
<code class="vb spaces">    </code><code class="vb plain">xOut = VBA.Left(xValue, 1)</code>
<code class="vb spaces">    </code><code class="vb keyword">For</code> <code class="vb plain">i = 2 </code><code class="vb keyword">To</code> <code class="vb plain">VBA.Len(xValue)</code>
<code class="vb spaces">       </code><code class="vb plain">xAsc = VBA.Asc(VBA.Mid(xValue, i, 1))</code>
<code class="vb spaces">       </code><code class="vb keyword">If</code> <code class="vb plain">xAsc >= 65 </code><code class="vb keyword">And</code> <code class="vb plain">xAsc <= 90 </code><code class="vb keyword">Then</code>
<code class="vb spaces">          </code><code class="vb plain">xOut = xOut & </code><code class="vb string">" "</code> <code class="vb plain">& VBA.Mid(xValue, i, 1)</code>
<code class="vb spaces">       </code><code class="vb keyword">Else</code>
<code class="vb spaces">          </code><code class="vb plain">xOut = xOut & VBA.Mid(xValue, i, 1)</code>
<code class="vb spaces">       </code><code class="vb keyword">End</code> <code class="vb keyword">If</code>
<code class="vb spaces">    </code><code class="vb keyword">Next</code>
<code class="vb spaces">    </code><code class="vb plain">Rng.Value = xOut</code>
<code class="vb keyword">Next</code>
<code class="vb plain">Application.ScreenUpdating = </code><code class="vb keyword">True</code>
<code class="vb keyword">End</code> <code class="vb keyword">Sub<code class="vb keyword">

This works OK for a single sheet, but it doesn't ignore consecutive capitals (such as "OK"), and requires me to select the range, which is throwing me off with adapting it to the entire workbook with code such as </code></code>

Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets

 

[Eccelerre]

For reference, I found this alternate code from ozgrid.com that works for a single column (not row) for a single sheet:

Sub Ins_Space() 
    Dim r As Range, regex As Object, cell As Range 
    Set r = Range("A1:A" & Cells(Rows.Count,"A").End(xlUp).Row) 'amend to whichever column contains names eg change "A1:A" to "F1:F" if you want column F (and change the other instance of "A" to "F"
    Set regex = CreateObject("VBScript.Regexp") 
    With regex 
        .Global = True 
        .Pattern = "([A-Z])" 
        For Each cell In r 
            If .Test(cell.Value) Then cell.Value = Trim(.Replace(cell.Value," $1")) 
        Next cell 
    End With 
End Sub

and another that only works for selected text within a sheet:

Sub AAA()
Dim R As Range
Dim N As Long
Dim S As String
If TypeOf Selection Is Excel.Range Then
For Each R In Selection.Cells
If R.HasFormula = False Then
If R.HasArray = False Then
S = R.Text
For N = Asc("A") To Asc("Z")
S = Replace(S, Chr(N), " " & Chr(N))
Next N
S = LTrim(S)
R.Value = S
End If
End If
Next R
End If
End Sub

 

[mole999]

Are they the actual export for the DB programe