Is it possible to replace a series of characters by a page break?

S

Sue Clay

I'm important data from a text file and would like to be able to
automatically replace a pre-set series of characters (say, $$$$) by a page
break. Is this possible?
 
S

Sue Clay

Thanks for the response. Tried it, but instead of replacing with page break
- it gave me a larger cell size. Odd. On this machine, I'm using version
2000. Would that be my problem?
 
S

Sue Clay

Thanks for the response. Unfortunately, it replaces my cell with a larger
one - but no page break. Any other ideas?
 
P

PCLIVE

What exactly do you mean by "Page Break"? Give me an example of what the
cell looks like before, and what it should look like after.
 
S

Sue Clay

My goal is to be able to open a text file in Excel, find a character series
($$$$) and replace it with a manual page break so that when I actually print
it - it will start a new sheet where the "$$$$" was. The $$$$ will always
appear on its own line in the first column.
 
G

Gord Dibben

You have been given a couple of suggestions which give you a carriage return
within a cell................alt + 0010 or CTRL + j

Is this what you want or do you want an actual "pagebreak" as in
Insert>PageBreak.

If the latter, I would suggest you need code.


Gord Dibben MS Excel MVP

On Thu, 24 May 2007 11:36:01 -0700, Sue Clay <Sue
 
T

Toppers

try:

Select column with "$$$$" and run this macro:

Sub SetPageBreak()

With ActiveSheet
For Each cell In Selection
If cell = "$$$$" Then
cell.Select
.HPageBreaks.Add Before:=ActiveCell
End If
Next cell
End With
End Sub
 
G

Gord Dibben

Which Toppers has provided in his post.

What will you do with the cells that conatin $$$$?

Do you want to clear them or leave as is?

To clear, revise Toppers code to this.

Sub SetPageBreak()

With ActiveSheet
For Each cell In Selection
If cell = "$$$$" Then
cell.Select
.HPageBreaks.Add Before:=ActiveCell
cell.EntireRow.ClearContents
'or cell.ClearContents
End If
Next cell
End With
End Sub


Gord
 
S

Sue Clay

That does the trick. Great.

Thanks so much!

Toppers said:
try:

Select column with "$$$$" and run this macro:

Sub SetPageBreak()

With ActiveSheet
For Each cell In Selection
If cell = "$$$$" Then
cell.Select
.HPageBreaks.Add Before:=ActiveCell
End If
Next cell
End With
End Sub
Click to expand...
 
S

Sue Clay

Thanks for all your help. That'll do it!

Gord Dibben said:
Which Toppers has provided in his post.

What will you do with the cells that conatin $$$$?

Do you want to clear them or leave as is?

To clear, revise Toppers code to this.

Sub SetPageBreak()

With ActiveSheet
For Each cell In Selection
If cell = "$$$$" Then
cell.Select
.HPageBreaks.Add Before:=ActiveCell
cell.EntireRow.ClearContents
'or cell.ClearContents
End If
Next cell
End With
End Sub


Gord
Click to expand...
 
Top