Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically update spreadsheet from another spreadsheet
I have 8 sheets on one spreadsheet and I need to create individual
spreadsheets for each of the 8 sheets. The problem is I need the information I put into the spreadsheet with all 8 sheets attached to update on the individual spreadsheets. How can I do this? I feel like Im just missing a step in the normal formulas that I use. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically update spreadsheet from another spreadsheet
I'm not sure what 8 sheets on one spreadsheet means, but the macro below will
parse all data into rows of 10s (change to suit your needs) and create new sheets for the results of each parse: Sub PaserIntoHundreds() Set sh = ActiveSheet For i = 1 To 991 Step 10 Set rng = sh.Cells(i, 1).Resize(10, 1).EntireRow Set sh1 = Worksheets.Add(after:=Worksheets(Worksheets.Count) ) rng.Copy sh1.Range("A1") ' optional 'sh1.move Next End Sub I may be way off target here. Post back if you need something different. Regards, Ryan-- -- RyGuy "Cindy" wrote: I have 8 sheets on one spreadsheet and I need to create individual spreadsheets for each of the 8 sheets. The problem is I need the information I put into the spreadsheet with all 8 sheets attached to update on the individual spreadsheets. How can I do this? I feel like Im just missing a step in the normal formulas that I use. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically update spreadsheet from another spreadsheet
Assume the 8-sheet book is called My Book.XLS
In cell A1 of sheet1 of the first new workbook, enter =IF('[My Book.xls]Sheet1'!A1="","",'[My Book.xls]Sheet1'!A1) There are two sets of double quotes in the formula: double-quote double-quote comma double-quote double-quote Copy this to the right as far as needed Now copy row 1 down as far as needed. Every time you open the new workbook it will be updated to show what is in My Book Note that if you add new data to My Book beyond the columns/row for formulas in the other books, the new stuff will not appear. It is generally considered poor practice to store the same data in two places (other than for backup, of course) There may be a better way if we knew the purpose of this exercise. best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Cindy" wrote in message ... I have 8 sheets on one spreadsheet and I need to create individual spreadsheets for each of the 8 sheets. The problem is I need the information I put into the spreadsheet with all 8 sheets attached to update on the individual spreadsheets. How can I do this? I feel like I'm just missing a step in the normal formulas that I use. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically update spreadsheet from another spreadsheet
Sorry I wasn't clear - it's hard to explain in writing (or for me).
I have an 8 worksheets in 1 excel workbook. I would like to have 8 separate workbooks but have them update automatically when I enter information into the workbook with all 8 worksheets. I figured out how to update by using Edit / Paste Link. My problem now is that any cell that wasn't filled in from the main document - shows up with zeros and now I am trying to figure out how to update the separate workbooks without the zeros. If NONE of this makes sense, I do apologize. I'm more of a talker than typer. :) "ryguy7272" wrote: I'm not sure what 8 sheets on one spreadsheet means, but the macro below will parse all data into rows of 10s (change to suit your needs) and create new sheets for the results of each parse: Sub PaserIntoHundreds() Set sh = ActiveSheet For i = 1 To 991 Step 10 Set rng = sh.Cells(i, 1).Resize(10, 1).EntireRow Set sh1 = Worksheets.Add(after:=Worksheets(Worksheets.Count) ) rng.Copy sh1.Range("A1") ' optional 'sh1.move Next End Sub I may be way off target here. Post back if you need something different. Regards, Ryan-- -- RyGuy "Cindy" wrote: I have 8 sheets on one spreadsheet and I need to create individual spreadsheets for each of the 8 sheets. The problem is I need the information I put into the spreadsheet with all 8 sheets attached to update on the individual spreadsheets. How can I do this? I feel like Im just missing a step in the normal formulas that I use. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatically update a link without opening the other spreadsheet | Excel Discussion (Misc queries) | |||
Have date automatically update each time I open the spreadsheet | Excel Discussion (Misc queries) | |||
How do I set up a spreadsheet where it will automatically update? | Excel Worksheet Functions | |||
Update one spreadsheet and reflect on another | Excel Worksheet Functions | |||
Is there a way to insert a formula, password or macro in an excel spreadsheet that will automatically delete the spreadsheet? | Excel Discussion (Misc queries) |