Results 1 to 6 of 6

How to change same cell in multiple worksheets?

This is a discussion on How to change same cell in multiple worksheets? within the Excel Questions forums, part of the Question Forums category; I have a workbook that is made up of many worksheets. Each worksheet is an individual price quote. I neeed ...

  1. #1
    New Member
    Join Date
    Mar 2005
    Posts
    5

    Default How to change same cell in multiple worksheets?

    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?

  2. #2
    Board Regular
    Join Date
    Aug 2003
    Location
    Boston
    Posts
    174

    Default

    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...

  3. #3
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    65,658

    Default

    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.
    If posting code please use code tags.

  4. #4
    MrExcel MVP, Administrator Von Pookie's Avatar
    Join Date
    Feb 2002
    Location
    The act or process of locating.
    Posts
    13,686

    Default

    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.
    Kristy

  5. #5
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,104

    Default

    Welcome to the Board!

    Here's some code that'll do it for you:
    Code:
    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
    Smitty

    Every once in a while, there's a sudden gust of gravity...

    Check out my new book at the Mr. Excel Bookstore!

    Mr. Excel HTML Maker - Post a shot of your sheet

  6. #6
    New Member
    Join Date
    Mar 2005
    Posts
    5

    Default

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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com