Put data rows per sheet

I have an Excel workbook with 2 sheets. One of them is for data entering
(e.g. rows: employee's name, ID#, working dates, fee etc.) and the other
presents that data among other constant value cells (e.g. name of company,
titles of columns, place for signatures etc.). What I want is when data in
the presentation sheet reach at 22 rows to be automatically continued to the
next page of the sheet. So, each printed page will always shows 22 rows of
data with the constant data ABOVE and BELOW of these 22 rows. Thanks,


0
girapas (4)
4/20/2004 6:41:46 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
939 Views

Similar Articles

[PageSpeed] 31

Hi
not fully automatic but try the following two macros (assumption: row 1
is a heading row and you have setup the page that row 1 is repeated on
each page 'File - Pagesetup - Sheet'):

Option Explicit
Sub insert_pagebreak()
Dim lastrow As Long
Dim row_index As Long

lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
For row_index = 23 to lastrow - 1 Step 22
      ActiveSheet.HPageBreaks.Add Before:= _
Next
End Sub

Sub remove_them()
ActiveSheet.ResetAllPageBreaks
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany


girapas wrote:
> I have an Excel workbook with 2 sheets. One of them is for data
> entering (e.g. rows: employee's name, ID#, working dates, fee etc.)
> and the other presents that data among other constant value cells
> (e.g. name of company, titles of columns, place for signatures etc.).
> What I want is when data in the presentation sheet reach at 22 rows
> to be automatically continued to the next page of the sheet. So, each
> printed page will always shows 22 rows of data with the constant data
> ABOVE and BELOW of these 22 rows. Thanks,

0
frank.kabel (11126)
4/20/2004 6:49:37 PM
Typo

Sub insert_pagebreak()
    Dim lastrow As Long
    Dim row_index As Long

    lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    For row_index = 23 To lastrow - 1 Step 22
        ActiveSheet.HPageBreaks.Add Before:=Cells(row_index, 1)
    Next
End Sub


-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"Frank Kabel" <frank.kabel@freenet.de> wrote in message news:OZzrwgwJEHA.644@tk2msftngp13.phx.gbl...
> Hi
> not fully automatic but try the following two macros (assumption: row 1
> is a heading row and you have setup the page that row 1 is repeated on
> each page 'File - Pagesetup - Sheet'):
>
> Option Explicit
> Sub insert_pagebreak()
> Dim lastrow As Long
> Dim row_index As Long
>
> lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
> For row_index = 23 to lastrow - 1 Step 22
>       ActiveSheet.HPageBreaks.Add Before:= _
> Next
> End Sub
>
> Sub remove_them()
> ActiveSheet.ResetAllPageBreaks
> End Sub
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
>
> girapas wrote:
> > I have an Excel workbook with 2 sheets. One of them is for data
> > entering (e.g. rows: employee's name, ID#, working dates, fee etc.)
> > and the other presents that data among other constant value cells
> > (e.g. name of company, titles of columns, place for signatures etc.).
> > What I want is when data in the presentation sheet reach at 22 rows
> > to be automatically continued to the next page of the sheet. So, each
> > printed page will always shows 22 rows of data with the constant data
> > ABOVE and BELOW of these 22 rows. Thanks,
>


0
rondebruin (3790)
4/20/2004 7:18:25 PM
Hi Ron
thanks for the correction :-)

--
Regards
Frank Kabel
Frankfurt, Germany


Ron de Bruin wrote:
> Typo
>
> Sub insert_pagebreak()
>     Dim lastrow As Long
>     Dim row_index As Long
>
>     lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
>     For row_index = 23 To lastrow - 1 Step 22
>         ActiveSheet.HPageBreaks.Add Before:=Cells(row_index, 1)
>     Next
> End Sub
>
>
>
> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> news:OZzrwgwJEHA.644@tk2msftngp13.phx.gbl...
>> Hi
>> not fully automatic but try the following two macros (assumption:
>> row 1 is a heading row and you have setup the page that row 1 is
>> repeated on each page 'File - Pagesetup - Sheet'):
>>
>> Option Explicit
>> Sub insert_pagebreak()
>> Dim lastrow As Long
>> Dim row_index As Long
>>
>> lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
>> For row_index = 23 to lastrow - 1 Step 22
>>       ActiveSheet.HPageBreaks.Add Before:= _
>> Next
>> End Sub
>>
>> Sub remove_them()
>> ActiveSheet.ResetAllPageBreaks
>> End Sub
>>
>> --
>> Regards
>> Frank Kabel
>> Frankfurt, Germany
>>
>>
>> girapas wrote:
>>> I have an Excel workbook with 2 sheets. One of them is for data
>>> entering (e.g. rows: employee's name, ID#, working dates, fee etc.)
>>> and the other presents that data among other constant value cells
>>> (e.g. name of company, titles of columns, place for signatures
>>> etc.). What I want is when data in the presentation sheet reach at
>>> 22 rows to be automatically continued to the next page of the
>>> sheet. So, each printed page will always shows 22 rows of data with
>>> the constant data ABOVE and BELOW of these 22 rows. Thanks,

0
frank.kabel (11126)
4/20/2004 7:24:28 PM
Promise to correct my typo's also Frank<g>


-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"Frank Kabel" <frank.kabel@freenet.de> wrote in message news:OZLWW0wJEHA.3704@TK2MSFTNGP11.phx.gbl...
> Hi Ron
> thanks for the correction :-)
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
>
> Ron de Bruin wrote:
> > Typo
> >
> > Sub insert_pagebreak()
> >     Dim lastrow As Long
> >     Dim row_index As Long
> >
> >     lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
> >     For row_index = 23 To lastrow - 1 Step 22
> >         ActiveSheet.HPageBreaks.Add Before:=Cells(row_index, 1)
> >     Next
> > End Sub
> >
> >
> >
> > "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> > news:OZzrwgwJEHA.644@tk2msftngp13.phx.gbl...
> >> Hi
> >> not fully automatic but try the following two macros (assumption:
> >> row 1 is a heading row and you have setup the page that row 1 is
> >> repeated on each page 'File - Pagesetup - Sheet'):
> >>
> >> Option Explicit
> >> Sub insert_pagebreak()
> >> Dim lastrow As Long
> >> Dim row_index As Long
> >>
> >> lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
> >> For row_index = 23 to lastrow - 1 Step 22
> >>       ActiveSheet.HPageBreaks.Add Before:= _
> >> Next
> >> End Sub
> >>
> >> Sub remove_them()
> >> ActiveSheet.ResetAllPageBreaks
> >> End Sub
> >>
> >> --
> >> Regards
> >> Frank Kabel
> >> Frankfurt, Germany
> >>
> >>
> >> girapas wrote:
> >>> I have an Excel workbook with 2 sheets. One of them is for data
> >>> entering (e.g. rows: employee's name, ID#, working dates, fee etc.)
> >>> and the other presents that data among other constant value cells
> >>> (e.g. name of company, titles of columns, place for signatures
> >>> etc.). What I want is when data in the presentation sheet reach at
> >>> 22 rows to be automatically continued to the next page of the
> >>> sheet. So, each printed page will always shows 22 rows of data with
> >>> the constant data ABOVE and BELOW of these 22 rows. Thanks,
>


0
rondebruin (3790)
4/20/2004 7:31:08 PM
Thank you Frank and Ron for your help.




"Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message
news:ONg4FxwJEHA.2704@TK2MSFTNGP10.phx.gbl...
> Typo
>
> Sub insert_pagebreak()
>     Dim lastrow As Long
>     Dim row_index As Long
>
>     lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
>     For row_index = 23 To lastrow - 1 Step 22
>         ActiveSheet.HPageBreaks.Add Before:=Cells(row_index, 1)
>     Next
> End Sub
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:OZzrwgwJEHA.644@tk2msftngp13.phx.gbl...
> > Hi
> > not fully automatic but try the following two macros (assumption: row 1
> > is a heading row and you have setup the page that row 1 is repeated on
> > each page 'File - Pagesetup - Sheet'):
> >
> > Option Explicit
> > Sub insert_pagebreak()
> > Dim lastrow As Long
> > Dim row_index As Long
> >
> > lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
> > For row_index = 23 to lastrow - 1 Step 22
> >       ActiveSheet.HPageBreaks.Add Before:= _
> > Next
> > End Sub
> >
> > Sub remove_them()
> > ActiveSheet.ResetAllPageBreaks
> > End Sub
> >
> > --
> > Regards
> > Frank Kabel
> > Frankfurt, Germany
> >
> >
> > girapas wrote:
> > > I have an Excel workbook with 2 sheets. One of them is for data
> > > entering (e.g. rows: employee's name, ID#, working dates, fee etc.)
> > > and the other presents that data among other constant value cells
> > > (e.g. name of company, titles of columns, place for signatures etc.).
> > > What I want is when data in the presentation sheet reach at 22 rows
> > > to be automatically continued to the next page of the sheet. So, each
> > > printed page will always shows 22 rows of data with the constant data
> > > ABOVE and BELOW of these 22 rows. Thanks,
> >
>
>


0
girapas (4)
4/21/2004 3:23:52 PM
Reply:

Similar Artilces:

Displaying Data From Joined Queries
In the Account form, it displays the Primary Contact. Which is really the primarycontactid field. Can I display other info from the Contact on the Acount form, like Contact Job Title? Is there a way to grab data from other tables that are related/joined (in a query) in v1.2 or v3.0? James This would not appear to be possible. I have tried to get this in serveral ways. In a form you could do this using an Iframe and some aspx, but in lists I can find a way to do this. I would like to see this functionality (out of the box) pretty much especially in lists. "James" wrote: &g...

How do I pass series data x-value range to a chart from a cell?
I have a series on a line chart (x-values "=Sheet1!$A$1:$A$10"). I also have two worksheet cells (B1 & B2) that contain the start and end date for the x-axis of the chart. I want to be able to link the chart series data to the start and end date values so that the chart automatically adjusts when I change the dates in the worksheet cells. I can create a string or address for the x-values but how do I do not know how to pass this to the chart series?? I know I can do this using a macro, but I want to do it without macros so that friends who use MicrosoftCharts (the cheap, n...

Delete color row
Hi, After I compare two worksheets (VB as below) and highlight the same value with colour, now I want to delete the same value row. The comparison VB that I have worked: Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=COUNTIF(New,H5)=1" Selection.FormatConditions(1).Interior.ColorIndex = 6 How do I write VB to delete color row? Raymond ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ You can't programmatically check for a color produc...

Cannot import data from outlook express into outlook. Help?!
My version of outlook only offers me the choice of importing from O.Express 4.x or 5. I have O.Express 6.0 and wonder if there is some patch that I need to download in order to update my Outlook programme or am I fighting a losing battle? OL2003 allows for importing from OE 6.x. Not sure about OL2002. "Seahorse" <Seahorse@discussions.microsoft.com> wrote in message news:2B7733D6-3218-4CF6-8330-7B6899F1380B@microsoft.com... > My version of outlook only offers me the choice of importing from > O.Express > 4.x or 5. I have O.Express 6.0 and wonder if there is some ...

make dropdown list greater than 8 rows
Hello How can I make my dropdown list 12 rows instead of only 8? thanks for any help For a Forms Combo Box right click and choose Format Control-Control tab For an ActiveX ComboBox right click and choose Properties and adjust ListRows. -- Regards Dave Hawley www.ozgrid.com "SteveZmyname" <SteveZmyname@discussions.microsoft.com> wrote in message news:105058EA-1275-417C-B4BC-4023DFDAC16C@microsoft.com... > Hello > How can I make my dropdown list 12 rows instead of only 8? > thanks for any help I can change the drop down lines to 12 in the F...

Data File
I am using 2007 and Vista Home 32 bit and very frequently when I open Outlook there is a balloon in the bottom right which says 'Last time there was a data file which did not close properly' or words to that effect and that MS is checking. Just live with it ? Or is there something I can do ? Thanks Sheena lucretiaborgia@fl.it wrote: > I am using 2007 and Vista Home 32 bit and very frequently when I open > Outlook there is a balloon in the bottom right which says 'Last time > there was a data file which did not close properly' or words to that > effect and that M...

Data Validation Audits
Hi all... Is there a way to trace where a range of cells that represent a picklist is used? The range has not been named. Thanks../Randy try the option Go To and then click on Special. "Randy Numbers" <ran@hotmail.com> wrote in message news:YPmXc.72015$n2.1448@fe11.usenetserver.com... > Hi all... > > Is there a way to trace where a range of cells that represent a picklist > is > used? The range has not been named. Thanks../Randy > Hi Randy, Presume you have a pick-list but do not know where is the SOURCE use for the pick-list - right? If so, pl...

Rows and Columns #2
Hello, I have excel 2002. If I have say 100 words in the first row one in ecah column and now I want to copy the same words to have them going down the worksheet is there an easier way then copy and paste 100 times? Thanks, Bec Bec If I understand, copy all the words in the row, select a cell below the row, say A2 and select Edit>Paste special>Transpose (Checked) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England www.nickhodge.co.uk nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "beccas1963" <beccas1963@discussions.microsoft.com> wrote in message news:C08F...

Sort data Excel 2007
How to sort using date and then time in an "a" and "p" format for Excel A normal sort should to what you want. Nothing special here... Bob Umlas Excel MVP "Billy Bob" wrote: > How to sort using date and then time in an "a" and "p" format for Excel Bob, I entered the dates under "date" and the time under "time". The time was entered as 10:00a - 2:00p - and 1:00p but it did not sort them. The date came out correct but I added another level and the sort asked for A - Z. I finaly went to military time and that worke...

Do not show a portion of the data series if the value is 0
Say I have data as follows Apr-09 May-09 Jun-09 1.00 2.00 2.00 0.00 1.00 1.00 0.00 0.00 2.00 If the quantities I have listed are driven off of formulas, how could I not plot the zero values. Thanks Would have been nice to see the formula but I will make one up You might have =A1/10 Change it to =IF(A1=0,NA(),A1/10) When you make the chart, the cells with NA() (which display as #N/A) will not get plotted best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "asmithbcat" <asmithbcat@discussions.microsoft.com> wrote in ...

Macro entering data into a protected Cell??
I'm trying to create a time sheet and I want to have a macro insert the current time into a protected cell (to stop people editing the time manually) Is this possible?? Any tips/hints See your other thread -- Regards Ron de Bruin http://www.rondebruin.nl <chortler@fetchmail.co.uk> wrote in message news:1121246433.314393.17210@o13g2000cwo.googlegroups.com... > I'm trying to create a time sheet and I want to have a macro insert the > current time into a protected cell (to stop people editing the time > manually) > Is this possible?? Any tips/hints > ...

Access2000: reading data from excel
Hi The situation: From a 3rd-party program, monthly some reports are saved as Excel files. They don't have proper table structure, p.e. there is some additional info in cell A1 (report header), and in I1 too (report date), the header row is 3rd one, actual data start from row 5, and the last row contains some summary values. And there are some abundant columns too. I need to import those reports into acess. So long I have used a special Excel file as intermediate agent. When import routine is started, the old table is cleared, user is asked for source file, the source file is ope...

Comments & protected sheets
I'm guessing I already know the answer to this, but is it possible to leave comments on a protected sheet? Yeah??????? Did you mean add/edit/delete comments on a protected sheet? If yes, then there's an option to protect (or leave unprotected) objects when you do Tools|protection|protect sheet that you can choose. PaulM wrote: > > I'm guessing I already know the answer to this, but is it possible to leave > comments on a protected sheet? -- Dave Peterson ...

hiding/unhiding rows & columns with "+" and "-" buttons
I was given an Excel file from a colleague, and there are these "+" an "-" buttons above the rows and the columns. When you press them several rows or columns can be hidden or unhidden all at once. I'v never seen this before; can anyone tell me how they were implemented -- shellshoc ----------------------------------------------------------------------- shellshock's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2493 View this thread: http://www.excelforum.com/showthread.php?threadid=39240 Select rows (or columns) then from menus: Data...

Data Migration
Hello, I'm using the Data Migration Framework 1.2 to migrate Account, Contact & SalesOrder to CRM. The migrationcode for these 3 tables in cdf_entitymigrationinfo have been set to 1. CDF Initialization was run successfully. The AccountID, ContactID and TerritoryID values have been set whereever applicable. p_cdf_ListUnmappedUsers returns nothing, the owning user field is populated. I'm receiving the User ID or Team ID is missing" error for Account and Contact. For SalesOrder I'm receiving the following error: The Customer is invalid. </description><details&...

Mp3 data into excel spreadsheet
Can I take a file with mp.3's and convert them into a Excel spreadsheet? I am a DJ and would like to figure out an easy way to make a songlist for my clients, as well as a master catalog for myself. Take a look at this: http://j-walk.com/ss/excel/tips/tip103.htm Biff "djdrew" <djdrew@discussions.microsoft.com> wrote in message news:A650304B-EC47-42AC-BF95-3CCB3A7E8A0C@microsoft.com... > Can I take a file with mp.3's and convert them into a Excel spreadsheet? > I > am a DJ and would like to figure out an easy way to make a songlist for my > clients, ...

Unfilter a sheet which is filtered
Hi, all. Need some help, please. I have several sheets in a workbook that I need the code to look at and determine if an autofilter is "in use", (i.e., if a filter is applied on any of the columns of the sheet). If it is, I need the code to unfilter the sheets. If it is not, I need the code to ignore it. I've tried variations of "ShowAllData", but I find that when it tries to execute on a sheet where a filter is not applied, the macro crashes. Something like this would be helpful: If [autofilter is in use] then ShowAllData I don't want to c...

how do Ii remove the grey page numbers in a work sheet
how do Ii remove the grey page numbers in a work sheet Try going to the View menu and unchecking Page Break Preview On Wed, 2 Feb 2005 11:01:08 -0800, "parker1230" <parker1230@discussions.microsoft.com> wrote: >how do Ii remove the grey page numbers in a work sheet You may be in page break preview mode. Go to "View" and select "normal" Regards Bill K "parker1230" <parker1230@discussions.microsoft.com> wrote in message news:5A65FC73-CAA0-428D-8ECD-0C2A2E15B799@microsoft.com... > how do Ii remove the grey page numbers in a work she...

MDA to AA data migration
Has anyone successfully migrated MDA data to AA? We want to implement AA but are waiting for AA support from a 3rd party (AIT from mc2). We are considering implementing MDA for the short-term, then migrating to AA once it's supported by AIT. Mike, Currently working on a tool to do so, if you are interested in talking about it please contact me. MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com "Mike Muscarella" wrote: > Has anyone successfully migrated MDA data to AA? We want to implement AA but > are waiting fo...

Deleting rows containing common data
I have an Excel 2000 spreadsheet with 30,000 rows. The rows look like this: 007-007 68-4611 68-4682 total 007-007 total 007-007 28-4831 68-2454 68-4682 total 007-007 28-4831 68-2454 68-4682 total 007-007 28-4831 68-2454 68-4682 total 007-007 68-4682 total 007-007 68-4682 total How do I delete the rows containing "total" One way: 1. Select the range and apply an AutoFilter (Data > Filter > AutoFilter) 2. On the drop-down list, select "Total". 3. Select the range again if needed. 5. Press F5 > Special and click "Visible Cells Only." 6. Remove...

Lowest value in a selection of data
Hi What formula would I use to find the lowest value in a selection of data i.e. B49:E52. Much Appreciated Brian =MIN(B49:E52) -- Gary''s Student - gsnu2007a Jumping in here... Is it possible to know the address of the cell containing the minimum value? John. You can use the MATCH function to locate the (relative) position, but if you have more than one value which are minima then it will only find the first. Pete On Nov 16, 7:23 pm, John Google <JohnGoo...@hotmail.co.uk> wrote: > Jumping in here... > > Is it possible to know the address of the cell containin...

How do I reduce the number of data points I have??
Hi , I have some 10000 data points from which I need to reduce to something like 100 points before making an xy chart. Typically I would like to make a chart of every 10th point. So basically I need to reduce the length of my two rows. Is there any way I can do this easily?? Any help is highly appreciated. Thanks in advance -- swapan From the Microsoft Charting FAQ: http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=209#jon024 - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ ssan...

connecting to data access page with filter criteria
I have an access database that I connect to from a GIS application (ArcView) using VBscript. I need to port my GIS project to the free viewer (ArcReader) which does not support VBscript but does support hyperlinking to a URL based on a value supplied by an underlying record. In my current project I can send filter critera to open a particular database, form and record. Would it be possible to do the same using data access pages or would I need to create a separate page for each record I want linked? ...

Activated Outlook and lost all data on Outlook Express
When I downloaded Windows XP Patch 2 it included Outlook. I installed the patch and activated Outlook. My old Outlook Express disappeared, along with a lot of files that I hadn't read/responded to/saved. Is Outlook Express still somewhere in my computer system? How do I access it to review and save some of the files that were there? -- TeacherGuy If you use outlook 2003, try to import data from outlook express to outlook 2003. from menu of oulook 2003, file > import & export > import internet mail and addresses > Outlook Express 4.x, 5.x, 6.x "TeacherGuy"...

characters in 1 col, 1 per row
Hi all, I want to transform the text of a worddocument to Excel in a special way. In my case text is just a number of words in a number of lines (nothing special about headers, footers etc.), so something comparable with this post. I want in Excel the first charachter of the text in A1, the second in A2 and so on. This email document would result in: H i a l l , I w a n t etc. I can't figure out how to do this, your help wil be appreciated. Jack Sons The Netherlands ...