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

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

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

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

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

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?

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?

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!