Adding spaces to report title

[Guest]

Hi, I have a report with 4 lines of title which are centered & span over 12
columns. The first line contains a report number and rundate; separated only
by a single space. I would like to have the report number on the far left
(left justify) and the rundate on the far right (right justify). Any easy
way to add LOTS of spaces between these 2 fields? There are 100+ pages in
the report, therefore, a macro would be helpful. TIA!

 

[Guest]

Can you do this in the header? It has left, center, and right sections that
can be aligned as you specify. The spacing is automatic.

You can find out how to use this in VBA help under the topic "PageSetup".
Here's a basic example:

With ActiveSheet.PageSetup
.LeftHeader = "YourText"
.CenterHeader = "YourText"
.RightHeader = "YourText"
End With

You could modify it to iterate the sheets in the entire workbook as follows:

'***************************************
Dim Wks As WorkSheet

For each Wks In ActiveWorkbook.WorkSheets
With Wks.PageSetup
.LeftHeader = "YourText"
.CenterHeader = "YourText"
.RightHeader = "YourText"
End With
Next Wks
'****************************************

HTH
Regards,
Garry

 

[Guest]

Alternatively, if you want the data to display in cells on the worksheet:

Set the leftmost column to .HorizontalAlignment = xlLeft
Set the rightmost column to .HorizontalAlignment = xlRight

'Select the center columns of the report, then
Set the center columns to .HorizontalAlignment = xlCenterAcrossSelection

HTH
Garry

 

[Guest]

Garry, thx for your ideas. I'm just a recipient of the report files,
therefore, titles are already part of the text. In some cases, I can't left
justify the leftmost column and/or right justify the rightmost column.
However I think I can work off your ideas and try to come up with something.
If not, you might hear from me again Thx!

 

[Guest]

Here's a different approach. It parses the data and puts it into the leftmost
and rightmost cells respectively. It assumes the number and date are in A1,
the number is followed by a space and the date, and this is the only text in
the cell. If otherwise, post a realistic sample.

Sub ParseNumberAndDate()
Dim sData As String
Dim iPos As Integer
Dim Wks As Worksheet

For Each Wks In ActiveWorkbook.Worksheets

'Get the data
sData = Wks.Range("A1").Value

'Find the position of the delimeter
iPos = InStr(1, sData, " ", vbTextCompare)

'Populate the leftmost cell of the report
With Wks.Cells(1, 1)
.HorizontalAlignment = xlLeft
.NumberFormat = "@"
.Value = Left$(sData, iPos - 1)
End With

'Populate the rightmost cell of the report
With Wks.Cells(1, 12)
.HorizontalAlignment = xlLeft
.NumberFormat = "@"
.Value = Mid$(sData, iPos + 1)
End With
Next Wks

End Sub

You should be able come up with something suitable from this.

Regards,
Garry

 

[Guest]

GS - thanks for the tips; they were great base for my coding!