automatically replicating my first page.

Ok, I'm creating a basic table for recording data.  I have the page set up 
exactly how I want it...now, what I need it to do is when I fill all the 
cells on the first page and activate a second page it gives me an exact copy 
of my original page, with obviously just page number changed in the footer.  
I don't want to have to mess around with recreating the form every time I 
fill a page in.
0
rusty (25)
2/1/2009 7:58:00 PM
excel 39879 articles. 2 followers. Follow

3 Replies
530 Views

Similar Articles

[PageSpeed] 41

Hi Rusty,

Your comment "what I need it to do is when I fill all the cells on the first 
page and activate a second page it gives me an exact copy of my original 
page". Do you mean pages or worksheets? Multiple pages of data can exist on 
one worksheet but  each tab for Sheet1, Sheet2, Sheet3 etc are worksheets.

If it is worksheets then you can have one worksheet as your 'template' 
without any data; just your setup, formulas and formatting etc. Right click 
on the name tab and select Move or Copy and check the box for create copy and 
select Before sheet (or Move to end).

With the above method it copies the sheet header and footer. If you want to 
print more than one worksheet with the page numbers sequential, then you can 
select multiple sheets to print. To do this, select the first sheet then hold 
the Ctrl key while you click on each of the other worksheet tabs. Or you can 
select the first sheet and hold the shift key and click the last sheet to 
print and it selects all sheets in between.

With printing, the order of worksheets is left to right across the tabs.


-- 
Regards,

OssieMac


"Rusty" wrote:

> Ok, I'm creating a basic table for recording data.  I have the page set up 
> exactly how I want it...now, what I need it to do is when I fill all the 
> cells on the first page and activate a second page it gives me an exact copy 
> of my original page, with obviously just page number changed in the footer.  
> I don't want to have to mess around with recreating the form every time I 
> fill a page in.
0
OssieMac (238)
2/2/2009 5:01:01 AM
I actually just got it a few minutes ago.  I wrote out a little macro that 
recreates consecutive pages from a template.

Sub NewPage()

Sheets("Template").Visible = True
Sheets("Template").Select
Range("A1:K44").Select
Selection.Copy
Sheets("Step Wedge Test").Select
X = WorksheetFunction.CountA(Range("A:A"))
Range("A1").Select
ActiveCell.Offset(rowOffset:=X, columnOffset:=0).Activate
ActiveSheet.Paste
Sheets("Template").Visible = False


End Sub 

Thanks though!  :)

"OssieMac" wrote:

> Hi Rusty,
> 
> Your comment "what I need it to do is when I fill all the cells on the first 
> page and activate a second page it gives me an exact copy of my original 
> page". Do you mean pages or worksheets? Multiple pages of data can exist on 
> one worksheet but  each tab for Sheet1, Sheet2, Sheet3 etc are worksheets.
> 
> If it is worksheets then you can have one worksheet as your 'template' 
> without any data; just your setup, formulas and formatting etc. Right click 
> on the name tab and select Move or Copy and check the box for create copy and 
> select Before sheet (or Move to end).
> 
> With the above method it copies the sheet header and footer. If you want to 
> print more than one worksheet with the page numbers sequential, then you can 
> select multiple sheets to print. To do this, select the first sheet then hold 
> the Ctrl key while you click on each of the other worksheet tabs. Or you can 
> select the first sheet and hold the shift key and click the last sheet to 
> print and it selects all sheets in between.
> 
> With printing, the order of worksheets is left to right across the tabs.
> 
> 
> -- 
> Regards,
> 
> OssieMac
> 
> 
> "Rusty" wrote:
> 
> > Ok, I'm creating a basic table for recording data.  I have the page set up 
> > exactly how I want it...now, what I need it to do is when I fill all the 
> > cells on the first page and activate a second page it gives me an exact copy 
> > of my original page, with obviously just page number changed in the footer.  
> > I don't want to have to mess around with recreating the form every time I 
> > fill a page in.
0
rusty (25)
2/2/2009 5:13:01 AM
Hi again Rusty,

Definitely the way to learn. Jump in and have a go. I didn't realize that 
you actually wanted a macro. 

You might like to look at the following code. With this code you don't need 
to make the template visible but you do need to make the new sheet visible 
because the copysheet copies all of the properties of the original sheet.

The good part is that it copies all the formatting, print/page setup 
including column widths that often do not copy with other methods. Also added 
just one of many methods of automatically naming the new sheet.

See the comments for what the macro is doing.

Dim strShtName As String

strShtName = Format(Now, "yyyy mmm dd hhmmss")

'Sheet1 does not need to be visible
'Following line copies sheet1 to new sheet at end of sheets
Sheets("Sheet1").Copy After:=Sheets(Sheets.Count)

'Following line makes new copied sheet visible
Sheets(Sheets.Count).Visible = True

'Activate the new sheet
Sheets(Sheets.Count).Activate

'Name the new sheet
ActiveSheet.Name = strShtName

-- 
Regards,

OssieMac


"Rusty" wrote:

> I actually just got it a few minutes ago.  I wrote out a little macro that 
> recreates consecutive pages from a template.
> 
> Sub NewPage()
> 
> Sheets("Template").Visible = True
> Sheets("Template").Select
> Range("A1:K44").Select
> Selection.Copy
> Sheets("Step Wedge Test").Select
> X = WorksheetFunction.CountA(Range("A:A"))
> Range("A1").Select
> ActiveCell.Offset(rowOffset:=X, columnOffset:=0).Activate
> ActiveSheet.Paste
> Sheets("Template").Visible = False
> 
> 
> End Sub 
> 
> Thanks though!  :)
> 
> "OssieMac" wrote:
> 
> > Hi Rusty,
> > 
> > Your comment "what I need it to do is when I fill all the cells on the first 
> > page and activate a second page it gives me an exact copy of my original 
> > page". Do you mean pages or worksheets? Multiple pages of data can exist on 
> > one worksheet but  each tab for Sheet1, Sheet2, Sheet3 etc are worksheets.
> > 
> > If it is worksheets then you can have one worksheet as your 'template' 
> > without any data; just your setup, formulas and formatting etc. Right click 
> > on the name tab and select Move or Copy and check the box for create copy and 
> > select Before sheet (or Move to end).
> > 
> > With the above method it copies the sheet header and footer. If you want to 
> > print more than one worksheet with the page numbers sequential, then you can 
> > select multiple sheets to print. To do this, select the first sheet then hold 
> > the Ctrl key while you click on each of the other worksheet tabs. Or you can 
> > select the first sheet and hold the shift key and click the last sheet to 
> > print and it selects all sheets in between.
> > 
> > With printing, the order of worksheets is left to right across the tabs.
> > 
> > 
> > -- 
> > Regards,
> > 
> > OssieMac
> > 
> > 
> > "Rusty" wrote:
> > 
> > > Ok, I'm creating a basic table for recording data.  I have the page set up 
> > > exactly how I want it...now, what I need it to do is when I fill all the 
> > > cells on the first page and activate a second page it gives me an exact copy 
> > > of my original page, with obviously just page number changed in the footer.  
> > > I don't want to have to mess around with recreating the form every time I 
> > > fill a page in.
0
OssieMac (238)
2/2/2009 6:10:01 AM
Reply:

Similar Artilces:

Last auto-generated replication-id
Hi, In an MS Access table I have a one-field primary key, that is an auto- increment field of type Replication-id. After inserting a single record using SQL INSERT, I want to obtain the key of the new record. I have done this before with auto-increment fields of type Numeric and then I can use "SELECT @@IDENTITY AS fieldname". But this doesn't work for replication id's. So how do I find my new record? Peter. Peter Laman <p.n.laman@scarlet.be> wrote in news:1178262329.725006.29060@y5g2000hsa.googlegroups.com: > In an MS Access table I have a one-field primary key...

I want to print a 26 page 5.5 X8.5 booklet
I want to print a a 5.5 8.5 booklet in publisher. I'm using 8 X 10 paper and printing in the landscape mode, but I'm having a difficult time getting the page order right when I print the book out. Can anyone help? Fred M calpoet <calpoet@discussions.microsoft.com> was very recently heard to utter: > I want to print a a 5.5 8.5 booklet in publisher. I'm using 8 X 10 > paper and printing in the landscape mode, but I'm having a difficult > time getting the page order right when I print the book out. Can > anyone help? How exactly do you plan on getti...

A program is trying to automatically send e-mail on your behalf.
I tried posting this in microsoft.public.outlook.general, but no nibbles. ------------------------------------------------------------------------------------------------------ Does anybody know how to turn the "A program is trying to automatically send e-mail on your behalf." prompte off? It pops when one of my MS Access apps is emailing one of it's reports to somebody. It happens on the Citrix server that one of my MS Apps is running on, but it does not happen when the app is running on my own PC. Consequently I'm hoping for some user-configurable setting that controls...

Money 2006 constantly faults with automatic updates
Whenever I use the Internet automatic updates, money faults out and wants to send a report to Microsoft. Also my account list shows most accounts with a status of 'Update In Progress' I've done a complete reinstall after removing it and power cycling my machine. I thought it may have been something with removing 2004 and installing 2006, but got same problem Has anyone else been having this problem? I really just want to go back to Money 2004. What's the error message? -- Glyn Simpson, Microsoft MVP - Money http://money.mvps.org Check http://money.mvps.org/faq f...

Automatic CC
Hello, Is there any way to create a template where a contact is automatically CC in the email. So I dont forget to cc them every time I send an email. Thanks ...

page margins
Can someone please advise how to change page margins from inches to centimeters in excel. i can change this in all other office programs except excel - help!!! Excel takes those settings from Windows Regional and Language settings. You must make the change there. Start>Settings>Control Panel. Gord Dibben Excel MVP On Thu, 29 Jul 2004 00:21:08 -0700, <s3564657@students.vu.edu.au> wrote: >Can someone please advise how to change page margins from >inches to centimeters in excel. i can change this in all >other office programs except excel - help!!! > > ...

Automatic axis format
Hi, I'm need to make a automatic axis format within excel. The number of rows is depending of a imported file. No problem to get the max. number of rows. But to put this number into the chart ( category axis) is more problematic. I try VB but my knowledge is at dummy level ! Waiting for your reply............. Hi, Have a look at Jon's examples of dynamic charts. http://peltiertech.com/Excel/Charts/Dynamics.html Cheers Andy jos wrote: > Hi, > I'm need to make a automatic axis format within excel. > The number of rows is depending of a imported file. > No problem to g...

How to add a 2nd page to an online email newsletter in Publisher?
I have inserted a 2nd page to my online email newsletter in publisher but when I view it by either the preview or sending it to my self I cannot get to the second page!! Can someone tell me what I am doing wrong and how to fix this please. This is for my small business and I am trying to have this ready for a June 1 release. Thank you. I think the only way of getting additional story to your email is make a Publisher page twice as long (22"). Creating a multi-page PDF file may be more appreciated by your readers for it is a nicer file to save, and print out. -- Don Vancouver, ...

Subreport Height not Grow/Shrink when change Page
Hello, I have a calendar report which uses 30 copies of a sub-report to build each day. Working just fine except: Let's say January 16th, there are 2 items to be listed for that date and the sub-report grows to a height of 0.5". I hit Next Page, go to February. There are now 4 items that need to be listed in that box. But instead of growing to accomodate these 4 items, the box stays at the 0.5" from January, and thus cuts off the February data. I have set Can Grow and Can Shrink everywhere -- in the sub-report details, on the sub-report control, and on the main report details...

I open the MDB, the form runs automatically
Simple question I have an MDB file with 20 files and 7 forms in it. When I open it up, the form runs immediately an access goes into some sort of protected mode. I can't view the tables, queries etc. I can see the code by pressing Alt F11 but not the data. I created a blank db and imported the mdb so I could see the tables. But how do I open the original without it running the code? When I look at the code. I don't see anything that sets properties or invokes the code automatically. There are no macros in this, just the forms and associated code with them. Any ideas Thanks Colm ...

Automatic updates, automatically disabling itself
Automatic updates, automatically disabling itself. 2nd day now onecare popped up to tell me the updates turned themselves = off. I'm thinking what malware would want to turn the updates off ? Any idea what's going on here ? Did I AUTOMATICALLY install an update = that caused this ? <keepout@yahoo.com.invalid> wrote in message news:8l7mi5tkgp1gd15fkef71ll5mrui9ukmht@4ax.com... > Automatic updates, automatically disabling itself. > > 2nd day now onecare popped up to tell me the updates turned themselves > off. Drop OneCare and install Microsoft Sec...

My worksheet automatically adds a fill color when I enter text.
When I type text in a blank cell, the cell automatically is highlightes with the bright yellow fill. There is no conditional formatting on this workbook. Check your VBA editor. You might have an Event Macro which does what, usually, a CF does. Try to Increase the Macro Security to its maximum level in order to eliminate the Event Macro from running... You may, also consider to "rem" all the Macro commands by adding a Preceding apostrophe to each command. Micky > When I type text in a blank cell, the cell automatically is highlightes with > the bright yello...

How do I get an automatic equal sign to begin the formula bar?
How do I get an automatic equal sign to appear in the formula bar in Excel for Microsoft 2003? This happened for me in the 2000 version and I could just click on it with my mouse pointer, but now all that appears is the function sign with no equal sign after it. It makes my job a lot more time consuming to have to type in the equal sign while building formulas. Please help, anybody? The = icon disappeared in newer versions of excel (xl2002???). You can add an = icon Tools|customize|commands tab|Insert category (Scroll down the list until you find the = icon) And drag it to your fav...

Having numbers automatically appear bold
Hi all, Had such great advise last time, thought I'd try again. I have a large range of cells where I will be inputing sales figures i the form of currency revenue. The range is about G-I and 1-400. Th sales data will be a comparisson of YTD 2001 - 2004 revenue results. What I hope to do is have any figure that is above $30,000 appea automatically in bold so as to stand out as a good client. I gues there is something in the IF function to do this but I'm stumped. Any ideas? Thanks again in advance. Saxte -- Message posted from http://www.ExcelForum.com select the range you wa...

Change to lastname first in GAL
This seems like it should be simple, but I cannot find where to change the display format of the global address list. It formats with firstname lastname instead of lastname firstname... I must be missing something obvious...can someone please point me in the right direction... Thank you kindly. Try this: http://www.msexchange.org/tutorials/MF023.html -- Mark Fugatt "Shawn" <anonymous@discussions.microsoft.com> wrote in message news:3b2c01c48f94$a3b38700$a301280a@phx.gbl... > This seems like it should be simple, but I cannot find > where to change the display for...

Is it possible to automatically move data entered in columns into rows?
Is it possible to automatically move data entered in columns into rows? It seems like a simple thing to do but I can't find a function anywhere. Cheers, Richard Thorneycroft Husky Products If you enter something in ONE cell it is automatically in a row and a column. So, perhaps a bit more explanation is in order. -- Don Guillett SalesAid Software donaldb@281.com "Richard Thorneycroft" <dancecommander81@hotmail.com> wrote in message news:9e2f3f75.0411160750.7003c6ab@posting.google.com... > Is it possible to automatically move data entered in columns into rows? >...

Automatic Forward
How can I automatically forward messages received from a specific sender to another's e-mail person? Thanks, ...

CE6: How to Disable SIP popup automatically
Hi, I know SIP popup can be disabled by the following registry setting in CE5: [HKEY_CURRENT_USER\ControlPanel\Sip] "TurnOffAutoDeploy"=dword:1 But It doesn't work in CE6. How to disable SIP auto-Popup in CE6? Thanks. Eric The SIP Auto Deploy in CE is due to Windows Controls invoke SHSipPreference (exported by AYGSHELL.DLL). So the easiest way is to avoid including AGYSHELL (SYSGEN_AYGSHELL) But if excluding AYGSHELL is not an option, you may need to modify the SHSipPreference (PRIVATE\SHELL\SHELLPSL\HAVEAYGSHELL\shellpsl.cpp) to supress SIP s...

Missing Page 1
I have a document with a page field in the footer for all pages, including the first. I am unable to get Word to start at page one on the first page. It will begin as page 2 even if I manually set the first pate as one in the format page number dialog box. If I set it to zero, it works, but I don't want zero, of course, I want it to start at one. I do have section breaks so that all chapters start on an odd page, which might be causing the problem. Any ideas? Thank you. Sounds like "Section start" for the very first section is set to "Even page"; y...

please help me
915 441722 COTTRELL, ERIN M 915 Total 922 155872 SABIN, DEAN I need to insert a page break after 915 Total, but leave the blank line above 922. -- yepidu I hope that the below link will help you... http://www.microsoft.com/office/community/en-us/default.mspx?&query=MarkN&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.misc&p=1&tid=641f4360-7ad4-47d3-ac41-716c205d07ea&mid=641f4360-7ad4-47d3-ac41-716c205d07ea -------------------- (Ms-Exl-Learner) -------------------- "yepidu" wrote: > 915 441722 COTTR...

Public Folder Replication #32
Hi there, we've got 2 Exchange 2K3 Std edition and 2 DC 2K3 in 2 different sites under 1 Domain. All users, Global groups, their mailboxes and departmental public folders have been created on the servers depending of where they are based at. My question is I'm trying to set "Directors" Global Group and their users to able to access all public folders on both sides. At the moment it seems like only mails replicate between 2 sites. But public folders can not be seen on an exchange server if it's been created on the other site. Is there any reason why public folders don...

3rd party tool for replicating Outlook Calendar
Can anyone recommend a 3rd party tool for replicating Outlook 2007 or 2010 calendar items into sharepoint 2007 or 2010? I know you can link Outlook and SharePoint together and can drag calendar items around but I want a tool that will automaticaly synch new and updated Outlook calendar items to my shared team calendar in sharepoint. Thanks, Ken Im not too familiar with sharepoint, however i do know of a 3rd party MS Office addin that replicates Outlook Calendars as MS Word and Excel format... This hay help achieve your needs: http://www.wincalendar.com/Outlook-Calenda...

Automatically format charts with range of dates
What I am trying to do is I have 4 charts that are linked with data from another spreadsheet. They are basically week totals for my business and make a chart compared to say last year. What I want to do though is have an option on my report that I can enter in a specific date or a range of dates and have the charts automatically filter the new data in and change. Instead of having to go into the chart data and changing what series of data I want to have a chart for I want to be able to just enter eg. "April 12-April 18" and have the charts bring up that data to reflect the ...

Automatic Database Backup
Is it possible to right code to automatically backup a database? And if so how, or where, can I get information on this? Hi There are a few way to do this. I have windows scheuler doing this on all my databases (at 1 min past midnight each day). Open Scheduled Tasks (it's in the control panel). It's very simple and not likely to go wrong -- Wayne Manchester, England. "Ayo" wrote: > Is it possible to right code to automatically backup a database? And if so > how, or where, can I get information on this? Thanks Wayne. So I take it that, there is no way t...

automatic output from known data
i have 2 spreadsheets of data: 1=english, 2=metric each spreadsheet has 3 columns, with the first column being an ID #. on my output spreadsheet i have 4 blanks. in blank 1 i enter a "1" or "2" for english/metric in blank 2 i enter the ID # i want blanks 3 and 4 to automatically be filled with the other tw numbers from the id#'s row. DATA WORKSHEETS english 1 123.8 9.8 2 122.7 10.3 ... metric 1 1983 9.8 2 1965 10.3 ... OUTPUT WORKSHEET blank 1 = english <----- manual blank 2 = 1 <----- manual blank 3 = 123.8 <---- automatic blank 4 = 9.8 <---- automati ...