Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Update worksheet when changing another sheet
Hi,
I have a main worksheet, columns A to H (8 columns), about 1000 rows and growing. I insert rows and change data regularly and I want to update another worksheet, which has only columns A to C plus E (4 columns, as some of the other info is not for everybody to see). Would a macro work? Would this be easy, or is there something else that would be better? Thanks. -- Regards, BB |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Update worksheet when changing another sheet
I take it you tried:
Click in a cell is the sheet to be updated Press = Navigate to the data sheet Click a cell and press [Enter] If this is not working for you, try the following formula: =INDIRECT("Sheet1!" & ADDRESS(ROW(),COLUMN())) Modify Sheet1 to the name of the sheet you wish to obtain values from. Drag or copy/paste the formula to all required cells. -- Steve "Sarah" wrote in message ... Hi, I have a main worksheet, columns A to H (8 columns), about 1000 rows and growing. I insert rows and change data regularly and I want to update another worksheet, which has only columns A to C plus E (4 columns, as some of the other info is not for everybody to see). Would a macro work? Would this be easy, or is there something else that would be better? Thanks. -- Regards, BB |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Update worksheet when changing another sheet
Thanks for the help Steve. It's just that I have over 1000 rows x 4 columns
of data, which is constantly being added to and ammended, was hoping to avoid having a formula in each cell. Appreciate the help. -- Regards, Sarah BB "AltaEgo" wrote: I take it you tried: Click in a cell is the sheet to be updated Press = Navigate to the data sheet Click a cell and press [Enter] If this is not working for you, try the following formula: =INDIRECT("Sheet1!" & ADDRESS(ROW(),COLUMN())) Modify Sheet1 to the name of the sheet you wish to obtain values from. Drag or copy/paste the formula to all required cells. -- Steve "Sarah" wrote in message ... Hi, I have a main worksheet, columns A to H (8 columns), about 1000 rows and growing. I insert rows and change data regularly and I want to update another worksheet, which has only columns A to C plus E (4 columns, as some of the other info is not for everybody to see). Would a macro work? Would this be easy, or is there something else that would be better? Thanks. -- Regards, BB |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Update worksheet when changing another sheet
Hi Sarah
Two suggestions. 1. Just hide columns D,F,G,H before letting others see your worksheet 2. Hold down Control as you click on the sheet tab and drag to the right. This will create a copy of your worksheet. Delete columns D,F,G,H. Next time, delete this Worksheet and repeat procedure to create copy of main worksheet with your changes. This can be recorded as a macro to automate the process. Alternatively, for making the copy, right click sheet tabMove or CopyCreate CopyChoose new Workbook -- Regards Roger Govier "Sarah" wrote in message ... Thanks for the help Steve. It's just that I have over 1000 rows x 4 columns of data, which is constantly being added to and ammended, was hoping to avoid having a formula in each cell. Appreciate the help. -- Regards, Sarah BB "AltaEgo" wrote: I take it you tried: Click in a cell is the sheet to be updated Press = Navigate to the data sheet Click a cell and press [Enter] If this is not working for you, try the following formula: =INDIRECT("Sheet1!" & ADDRESS(ROW(),COLUMN())) Modify Sheet1 to the name of the sheet you wish to obtain values from. Drag or copy/paste the formula to all required cells. -- Steve "Sarah" wrote in message ... Hi, I have a main worksheet, columns A to H (8 columns), about 1000 rows and growing. I insert rows and change data regularly and I want to update another worksheet, which has only columns A to C plus E (4 columns, as some of the other info is not for everybody to see). Would a macro work? Would this be easy, or is there something else that would be better? Thanks. -- Regards, BB |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Update worksheet when changing another sheet
Thanks for the help Roger. I fiddled around and came up with the following
Macro: Application.ScreenUpdating = False Sheets("Master").Select Range("A7:D1500").Copy Sheets("Retail").Select Range("A7:D1500").PasteSpecial Sheets("Master").Select Range("F7:F1500").Copy Sheets("Retail").Select Range("E7:E1500").PasteSpecial Application.ScreenUpdating = True However......I get the "copy lines" around the range F7:F1500 in Master sheet, and a gray background in the range selected to paste to in Retail sheet. Are you able to help me where I've gone wrong with this? -- Regards, Sarah "Roger Govier" wrote: Hi Sarah Two suggestions. 1. Just hide columns D,F,G,H before letting others see your worksheet 2. Hold down Control as you click on the sheet tab and drag to the right. This will create a copy of your worksheet. Delete columns D,F,G,H. Next time, delete this Worksheet and repeat procedure to create copy of main worksheet with your changes. This can be recorded as a macro to automate the process. Alternatively, for making the copy, right click sheet tabMove or CopyCreate CopyChoose new Workbook -- Regards Roger Govier "Sarah" wrote in message ... Thanks for the help Steve. It's just that I have over 1000 rows x 4 columns of data, which is constantly being added to and ammended, was hoping to avoid having a formula in each cell. Appreciate the help. -- Regards, Sarah BB "AltaEgo" wrote: I take it you tried: Click in a cell is the sheet to be updated Press = Navigate to the data sheet Click a cell and press [Enter] If this is not working for you, try the following formula: =INDIRECT("Sheet1!" & ADDRESS(ROW(),COLUMN())) Modify Sheet1 to the name of the sheet you wish to obtain values from. Drag or copy/paste the formula to all required cells. -- Steve "Sarah" wrote in message ... Hi, I have a main worksheet, columns A to H (8 columns), about 1000 rows and growing. I insert rows and change data regularly and I want to update another worksheet, which has only columns A to C plus E (4 columns, as some of the other info is not for everybody to see). Would a macro work? Would this be easy, or is there something else that would be better? Thanks. -- Regards, BB |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Update worksheet when changing another sheet
To get rid of the "copy lines" add this line after the second pastespecial.
Application.CutCopyMode = False Then select any one cell to clear the selected range. But........................................... You can shorten the code and make it run faster by not selecting things. Note also you won't need the Application.CutCopyMode = False With Sheets("Master") .Range("A7:D11500").copy Destination:= _ Sheets("Retail").Range("A7") .Range("F7:F1500").copy Destination:= _ Sheets("Retail").Range("E7") End With Range("A7").Select Gord Dibben MS Excel MVP On Sat, 30 May 2009 05:28:01 -0700, Sarah wrote: Thanks for the help Roger. I fiddled around and came up with the following Macro: Application.ScreenUpdating = False Sheets("Master").Select Range("A7:D1500").Copy Sheets("Retail").Select Range("A7:D1500").PasteSpecial Sheets("Master").Select Range("F7:F1500").Copy Sheets("Retail").Select Range("E7:E1500").PasteSpecial Application.ScreenUpdating = True However......I get the "copy lines" around the range F7:F1500 in Master sheet, and a gray background in the range selected to paste to in Retail sheet. Are you able to help me where I've gone wrong with this? |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Update worksheet when changing another sheet
Thank you so much Gord! Works so much better!
-- Regards, Sarah "Gord Dibben" wrote: To get rid of the "copy lines" add this line after the second pastespecial. Application.CutCopyMode = False Then select any one cell to clear the selected range. But........................................... You can shorten the code and make it run faster by not selecting things. Note also you won't need the Application.CutCopyMode = False With Sheets("Master") .Range("A7:D11500").copy Destination:= _ Sheets("Retail").Range("A7") .Range("F7:F1500").copy Destination:= _ Sheets("Retail").Range("E7") End With Range("A7").Select Gord Dibben MS Excel MVP On Sat, 30 May 2009 05:28:01 -0700, Sarah wrote: Thanks for the help Roger. I fiddled around and came up with the following Macro: Application.ScreenUpdating = False Sheets("Master").Select Range("A7:D1500").Copy Sheets("Retail").Select Range("A7:D1500").PasteSpecial Sheets("Master").Select Range("F7:F1500").Copy Sheets("Retail").Select Range("E7:E1500").PasteSpecial Application.ScreenUpdating = True However......I get the "copy lines" around the range F7:F1500 in Master sheet, and a gray background in the range selected to paste to in Retail sheet. Are you able to help me where I've gone wrong with this? |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Update worksheet when changing another sheet
Thanks for the feedback
Gord On Sat, 30 May 2009 19:04:01 -0700, Sarah wrote: Thank you so much Gord! Works so much better! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update one worksheet tab to automatically update all other tabs? | Excel Worksheet Functions | |||
update a sheet automatically with changing values from another she | Excel Worksheet Functions | |||
how to update a worksheet and have data copied to another sheet? | Excel Worksheet Functions | |||
Why won't my pie charts update with changing data? | Charts and Charting in Excel | |||
How do I set my webpage to automatically update a changing spread. | Excel Discussion (Misc queries) |