How to change same cell in multiple worksheets?

[RockDawg]

I have a workbook that is made up of many worksheets. Each worksheet is an individual price quote. I neeed I change the value of cell B6 for every worksheet?

Example:

The value of cell B6 is currently .66 across all the worksheets. I want to change them all to .85 without having to manually edit each worksheet. Can I do this easily?

 

[BuddieB]

Select the first sheet you want to modify, then hold the SHIFT key and select the last sheet you want to modify. All Sheets between will be selected. (or you can right click on one sheet tab and SELECT ALL SHEETS, if it really does apply to every sheet.

Then make the change and all sheets will be affected.

Be sure to unselect all sheets before making further changes...

 

[Norie]

You could select all the sheet tabs and then enter the value in B6.

Or you could put the value in B6 of one sheet and references to that cell in the other sheets.

 

[Von Pookie]

You could use formulas on the other worksheets pointing back to the main one.

=Sheet1!B6 would show the value of B6 on sheet1 for whatever worksheet it is used on.

 

[Smitty]

Welcome to the Board!

Here's some code that'll do it for you:

Sub ChangeB6()
    Dim ws As Worksheet
    Dim NewValue As Currency
        NewValue = Application.InputBox("Please enter the new value", Type:=1)
        For Each ws In ActiveWorkbook.Worksheets
            ws.[B6] = NewValue
        Next ws
End Sub

You could also add another input box to prompt for the cell to change.

Hope that helps,

Smitty

 

[RockDawg]

Thanks a lot! I think I'll try the code out later on a spereate data entry page.