Try the code below after running a print preview on the spreadsheet:
Sub fmtprntrng()
Dim rngCell As Range
Dim hpb As HPageBreak
Dim wks As Worksheet
Dim rg1 As String, rg2 As String, rg3 As String
' Application.ScreenUpdating = 0
Set wks = Worksheets("Sample Data")
For Each rngCell In wks.Range("A4:A" & wks.Cells(Rows.Count,
1).End(xlUp).Row)
If rngCell.Value = "Do" Then
rngCell.Resize(1, 3).NumberFormat = ";;;"
Else
rg1 = rngCell.Value
rg2 = rngCell(, 2).Value
rg3 = rngCell(, 3).Value
End If
For Each hpb In wks.HPageBreaks
If hpb.Location.Row = rngCell.Row Then
rngCell.Resize(1, 3).NumberFormat = "General"
rngCell.Value = rg1
rngCell(, 2).Value = rg2
rngCell(, 3).Value = rg3
Exit For
End If
Next hpb
rngCell(, 6).NumberFormat = "#####-#######-#"
Next rngCell
For Each rngCell In wks.Range("A3:A" & wks.Cells(Rows.Count,
1).End(xlUp).Row)
rngCell(, 6).NumberFormat = "#####-#######-#"
rngCell(, 6).Replace What:="-", Replacement:="",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Next rngCell
' Application.ScreenUpdating = 1
End Sub
On Nov 19, 11:02 pm, Sam Mathai Chacko <samde...@gmail.com> wrote:
> And if you have the actual names listed multiple times, and not DO, then
> use the macro below. Can be found in the attached file also.
>
> Sub BELOWHBREAK()
>
> Dim rngCell As Range
> Dim hpb As HPageBreak
> Dim wks As Worksheet
> ' Application.ScreenUpdating = 0
> Set wks = Worksheets("Sample Data")
> For Each rngCell In wks.Range("A4:A" & wks.Cells(Rows.Count,
> 1).End(xlUp).Row)
> If rngCell.Value = rngCell(0).Value Then
> If rngCell(, 2).Value = rngCell(0, 2).Value Then
> If rngCell(, 3).Value = rngCell(0, 3).Value Then
> rngCell.Resize(1, 3).NumberFormat = ";;;"
> End If
> End If
> End If
>
> For Each hpb In wks.HPageBreaks
> If hpb.Location.Row = rngCell.Row Then
> rngCell.Resize(1, 3).NumberFormat = "General"
> Exit For
> End If
> Next hpb
> Next rngCell
> ' Application.ScreenUpdating = 1
>
> End Sub
>
> Regards,
> Sam Mathai Chacko
>
>
>
>
>
>
>
> On Sat, Nov 19, 2011 at 11:24 PM, dguillett1 <dguille...@gmail.com> wrote:
> > If you mean to have blanks where DO is then see attached for a macro that
> > will do as desired. You may have to adjust based on the page break row for
> > your printer.
>
> > Don Guillett
> > SalesAid Software
> > dguille...@gmail.com
> > -----Original Message----- From: dguillett1
> > Sent: Saturday, November 19, 2011 11:20 AM
> > To: excel-macros@googlegroups.com
>
> > Subject: Re: $$Excel-Macros$$ Fwd: How can I repeat top most data of a
> > column on each page
>
> > How does that relate to the sample provided? If you want for a DIFFERENT
> > sample, please provide. If not, tell us which columns you want to print and
> > why is the word do in the otherwise blank cells???
>
> > Don Guillett
> > SalesAid Software
> > dguille...@gmail.com
> > -----Original Message----- From: MRafique Ujjan
> > Sent: Saturday, November 19, 2011 6:33 AM
> > To: MS EXCEL AND VBA MACROS
> > Subject: Re: $$Excel-Macros$$ Fwd: How can I repeat top most data of a
> > column on each page
>
> > Dear All,
>
> > I am thankful to all of friends who responded me well, but it is my
> > fault that i could not mention my problem clearly. Therefore I once
> > again try to mention please help me in this regard as earlier.
>
> > Problem:
> > Suppose I have one two columns A1 & B1. In column A1 I entered
> > Different Countries and in other column B1 I enter Sale in different
> > cities. So now i have entered thousands of entries in sale column. If
> > in the column A1 I enter every country name against column B1 for
> > sales. Then it looks like as below
>
> > A1 B1
> > ============================
> > 1. Pakistan Karachi
> > 2. Pakistan Hyderabad
> > 3. Pakistan Islamabad
> > 4. India Delhi
> > 5. India Kolkata
> > 6. India Mumbai
> > 1. Pakistan Karachi
> > 2. Pakistan Hyderabad
> > 3. Pakistan Islamabad
> > 4. India Delhi
> > 5. India Kolkata
> > 6. India Mumbai
> > 7. Pakistan Karachi
> > 8. Pakistan Hyderabad
> > 9. Pakistan Islamabad
> > 10. India Delhi
> > 11. India Kolkata
> > 12. India Mumbai
> > and so on .................
>
> > Now if I have entered thousands of rows entries for both columns. Then
> > it looks bad to enter each country name against every city. So I
> > entered the data in this way.
>
> > A1 B1
> > ============================
> > 1. Pakistan Karachi
> > 2. Hyderabad
> > 3. Islamabad
> > 4. India Delhi
> > 5. Kolkata
> > 6. Mumbai
> > 7. Pakistan Karachi
> > 8. Hyderabad
> > 9. Islamabad
> > 10. India Delhi
> > 11. Kolkata
> > 12. Mumbai
> > 13. Pakistan Karachi
> > 14. Hyderabad
> > 15. Islamabad
> > 16. India Delhi
> > 17. Kolkata
> > 18. Mumbai
> > Now as I have already told you that I have entered thousands of
> > entries like above. Therefore, when i print the data it looks like
> > this
>
> > Page 1
> > ======
> > 1. Pakistan Karachi
> > 2. Hyderabad
> > 3. Islamabad
> > 4. India Delhi
> > 5. Kolkata
> > 6. Mumbai
> > 7. Pakistan Karachi
> > 8. Hyderabad
> > 9. Islamabad
> > 10. India Delhi
> > 11. Kolkata
> > 12. Mumbai
> > 13. Pakistan Karachi
> > 14. Hyderabad
> > 15. Islamabad
> > 16. India Delhi
> > 17. Kolkata
> > 18. Mumbai
>
> > Page 2
> > ======
> > 19. Karachi
> > 20. Hyderabad
> > 21. Islamabad
> > 22. India Delhi
> > 23. Kolkata
> > 24. Mumbai
> > 25. Pakistan Karachi
> > 26. Hyderabad
> > 27. Islamabad
> > 28. India Delhi
> > 29. Kolkata
> > 30. Mumbai
> > 31. Pakistan Karachi
> > 32. Hyderabad
> > 33. Islamabad
> > 34. India Delhi
> > 35. Kolkata
> > 36. Mumbai
>
> > Now suppose in the page 2 there is no name mentioned in the Column A1
> > (1st row of page 2) against the column B1. In the page 1 the data is
> > clear to understand but in page 2 it is not clear.
> > Therefore I need a formula or macro for each page to show the name of
> > country automatically in the 1st row of each next page.
>
> > I have tried my level best to mention clearly my problem. I hope that
> > I will get the problem solved soon.
>
> > Thanking you in anticipation.
>
> > M. Rafique Ujjan
>
> > On Nov 19, 1:16 am, "dguillett1" <dguille...@gmail.com> wrote:
>
> >> You can use the change event macro so when you type in the sample number
> >> you will get what you requested.
> >> However,I suspect that you want more
> >> On the print question, you don’t tell us WHAT you intend to print......
>
> >> Don Guillett
> >> SalesAid Software
> >> dguille...@gmail.com
>
> >> From: M.Rafique MRU
> >> Sent: Friday, November 18, 2011 9:47 AM
> >> To: excel-macros@googlegroups.com
> >> Subject: $$Excel-Macros$$ Fwd: How can I repeat top most data of a column
> >> on each page
>
> >> Sorry I forgot to attach sheet.
> >> Now I have attached the sheet
>
> >> ---------- Forwarded message ----------
> >> From: MRafique Ujjan <imrafiqu...@gmail.com>
> >> Date: Fri, Nov 18, 2011 at 4:23 PM
> >> Subject: How can I repeat top most data of a column on each page
> >> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com**>
>
> >> Dear Group Members,
>
> >> I am attaching a sample data sheet here. In which following are the
> >> columns
>
> >> 1. Union Council
> >> 2. Name of Deh
> >> 3. Name of Village
> >> 4. S. No
> >> 5. Full Name
> >> 6. N.I. C No
> >> 7. Nature of House
>
> >> Now I have entered data in this sheet in thousands of names and
> >> hundreds of villages.
> >> 1) Now I want Column 1 to 3 top most data in the page should be
> >> repeated on each page when I print 100s of pages.
> >> 2) I want to know any special formatting for column 6 when i enter
> >> numbers dashes should automatically be inserted in the numbers like
> >> below
>
> >> 4140564843677 this should be like thise 41405-6487367-7
>
> >> You can see this data in my sample worksheet. Please help me in this
> >> regard I shall remain thank full to you forever
>
> >> --
> >> FORUM RULES (934+ members already BANNED for violation)
>
> >> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> >> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> >> will not get quick attention or may not be answered.
>
> >> 2) Don't post a question in the thread of another member.
>
> >> 3) Don't post questions regarding breaking or bypassing any security
> >> measure.
>
> >> 4) Acknowledge the responses you receive, good or bad.
>
> >> 5) Cross-promotion of, or links to, forums competitive to this forum in
> >> signatures are prohibited.
>
> >> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> >> owners and members are not responsible for any loss.
>
> >> ------------------------------**------------------------------**
> >> ------------------------------**------------
> >> To post to this group, send email to excel-macros@googlegroups.com
>
> >> Rafique_Sample Worksheet1.xls
> >> 52KViewDownload
>
> > --
> > FORUM RULES (934+ members already BANNED for violation)
>
> > 1) Use concise, accurate thread titles. Poor thread titles, like Please
> > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> > will
> > not get quick attention or may not be answered.
>
> > 2)
>
> ...
>
> read more »
>
> Sample Worksheet(2).xls
> 62KViewDownload
--
FORUM RULES (934+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
