Update worksheet when changing another sheet

[Sarah]

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.

 

[AltaEgo]

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.

 

[Sarah]

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

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.

 

[Roger Govier]

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 tab>Move or
Copy>Create Copy>Choose new Workbook

--
Regards
Roger Govier

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

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

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.

 

[Sarah]

Thanks for the help Roger. I fiddled around and came up with the following
Macro:
Application.ScreenUpdating = False

Sheets("Master").Select
Range("A71500").Copy
Sheets("Retail").Select
Range("A71500").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

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 tab>Move or
Copy>Create Copy>Choose new Workbook

--
Regards
Roger Govier

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

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

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.

 

[Gord Dibben]

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("A711500").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

 

[Sarah]

Thank you so much Gord! Works so much better!

 

[Gord Dibben]

Thanks for the feedback


Gord