ALISONH613
New Member
- Joined
- Sep 26, 2014
- Messages
- 2
I am having a hard time trying to set a page break before a cell value. I have tried two codes (see below) and am getting an error with a number 400 for the first one and no macros to run on the second. I am a novice and need to split over 20000 excel rows to print (I want page breaks by class- then each page would be a different class period, class name and include student scores to be distributed to teachers)
Option ExplicitSub PageBreaks() Dim c As Range Dim FirstAddress AsStringDim Search AsStringDim Prompt AsStringDim Title AsString Prompt = "What do you want to search for?" Title = "Search Term Input" Search = InputBox(Prompt, Title) If Search = "" Then Exit SubEndIfWith ActiveSheet.UsedRange Set c = .Find(What:=Search, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True) IfNot c IsNothingThen FirstAddress = c.Address Do ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=c Set c = .FindNext(c) LoopWhileNot c IsNothingAnd c.Address <> FirstAddress EndIfEnd WithEnd Sub
Dim Search As String Search = "student" With ActiveSheet.UsedRange Set c = .Find(What:=Search, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True) If Not c Is Nothing Then FirstAddress = c.Address Do ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=c Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address <> FirstAddress End If End
Option ExplicitSub PageBreaks() Dim c As Range Dim FirstAddress AsStringDim Search AsStringDim Prompt AsStringDim Title AsString Prompt = "What do you want to search for?" Title = "Search Term Input" Search = InputBox(Prompt, Title) If Search = "" Then Exit SubEndIfWith ActiveSheet.UsedRange Set c = .Find(What:=Search, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True) IfNot c IsNothingThen FirstAddress = c.Address Do ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=c Set c = .FindNext(c) LoopWhileNot c IsNothingAnd c.Address <> FirstAddress EndIfEnd WithEnd Sub
Dim Search As String Search = "student" With ActiveSheet.UsedRange Set c = .Find(What:=Search, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True) If Not c Is Nothing Then FirstAddress = c.Address Do ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=c Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address <> FirstAddress End If End