LinkBack URL
About LinkBacks
is it possible to have a sheet that i can type in formulae and the formulae will automatically copy itself into the same cells on any new sheets i open?
How would i do this?
regards, jamie
is it possible to have a sheet that i can type in formulae and the formulae will automatically copy itself into the same cells on any new sheets i open?
How would i do this?
regards, jamie
Hi,
What you could try is this, set up your first sheet with all your formulas, then click on the page tab, hold Ctrl key and with the left mouse key pressed drag the tab to the right. This will make an identical copy of your first sheet. Rename your sheet tab to your requirements.
oldchippy
-------------
Blessed are those who can give without remembering and take without forgetting
If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.
Click here >>> Top Excel links for beginners to Experts
Forum Rules >>>Please don't forget to read these
thanks for such a quick reply!
trouble i have is that each new sheet will already have data on it, i wanted to have a sheet where if i typed formulae in say, G6, it would appear on G6 on each new sheet opened, as well as the data already present, whether i opened 1 or 100 sheets.
Well another way is to go to Edit > Go to > Special > click formulas. This will highlight all the cells with formulas on the active sheet, with them all selected, copy, then go to your new sheet, select cell A1 and paste.
tried it and it works a treat, thanks!
if anyone knows a way of setting it up so it automatically does it when new sheets are opened, then i would be grateful (if it is even possible!!) if not, then this way should do the trick
thanks
Glad to hear it worked for you, but VBA is not my expertise. Anyboby out there who can HELP?
If this routine in put in the ThisWorkbook code module, every time a sheet in the workbook is activated, the formula in Master sheet, cell G6 will be put in G6 of the newly activated sheet.
The name "Master" and "G6" should be modified to fit your particular situation.
Instalation instructions:
Please Login or Register to view this content.
Open the VBEditor, View the ProjectExplorer, open the Microsoft Excel Objects folder, double click the ThisWorkbook icon, copy/paste the code into the window which will appear. Close the window, Exit the VBEditor and return to Excel.
Last edited by mikerickson; 01-14-2008 at 07:37 PM.
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
that is perfect!! does exactly what i need. Thanks to all,
jamie
one slight problem i have noticed, the new sheets on opening them have the data that they already contain, plus the formulae from the master. however, if i click the tab to view another sheet and then navigate back to the new sheet again, it now only contains the formulae and not the original data! Any ideas?
by the way, the data i do not want to loose is on E1:AH14 on each new sheet on opening, is there a way that the new sheet on opening would have those cells so that they cannot be overwritten (i would still want to delete the sheet after i am done!), or is this overcomplicating it?
When you say that new sheet already have data on them, do you mean that when the user uses Insert New Sheet, there is data on that sheet? If so, what is the code that populates that?
yes, i am playing with a spreadsheet i downloaded from somewhere (with permission to change it and alter stuff)- trying to teach myself excel at the same time (with varying results!!!)
The new sheet is inserted with a button that has been created which inserts it with data on which is being updated from a webpage at intervals. If i then navigate away from that sheet and back again, the data is missing but the formulae from the master is still there. Even when the web data updates, parts are still missing, as some data is from the web and some is not (if that makes sense)
As for the code that inserts it, i do not know where to start!!
Where would i look to find it if you need it?
By the way, thanks for spending the time to try and help
The "make new sheet" button is connected to a macro.
Try right clicking on the command button.
If the pop-up menu has a "view code" option, its an ActiveX button. Viewing the code would be where to start looking.
If it doesn't have that option, its a Forms control. In that case, select AssignMacro to see what macro is currently assigned. The Macro Dialog box will let you Edit (i.e. see) that macro.
figured it out! i was selecting the whole sheet to put data into, so it was overwriting the web data with blank cells (i think) anyway, now i have selected only the cells i am working with and all is ok, thanks for your help!
Is there any way i can get it to do this, but not write onto certain sheets. for example, if i had 'sheet2' and 'sheet3', coulod i make it so it does not write onto these? Not too important, but a help if any one knows
regards and thanks, jamie
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks