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!

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

"ka2cil" wrote:

> 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!

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

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!

"GS" wrote:

> 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

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

"ka2cil" wrote:

> 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!
>
> "GS" wrote:
>
> > 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

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

"GS" wrote:

> 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
>
> "ka2cil" wrote:
>
> > 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!
> >
> > "GS" wrote:
> >
> > > 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