1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Is it possible to enter text in a cell & have it copy to another sht, even when cell changes?

Discussion in 'Ask an Excel Question' started by Frncis, Aug 10, 2018.

  1. Frncis

    Frncis Member

    Messages:
    43
    I have a workbook to assist me & other staff track our days off. Since the holidays change on the worksheets every year I am currently manually entering them on both sheets. I am trying to find a way to enter the holidays on one sheet & have the same holidays entered on the second sheet. I have attached a sample to make it a little easier to understand, my question. Is what I am asking even possible? Thank you for your time.

    Attached Files:

  2. pecoflyer

    pecoflyer Active Member

    Messages:
    238
    Perhaps group both sheets before entering the holidays?
  3. Frncis

    Frncis Member

    Messages:
    43
    I don't quite understand what you mean. Could you be a little more descriptive.
  4. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,012
    While pressing CTRL Key, using mouse to select both sheets (using sheet tab at bottom).
    upload_2018-8-10_11-11-21.png
    While you have both sheets grouped in above manner. Change you make in cell will be replicated to both sheets.
  5. Frncis

    Frncis Member

    Messages:
    43
    Thank you for being more descriptive. The sheet tabs were removed because issues, the other users were having.
  6. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,012
    Can't you just enter in one sheet, copy paste value to 2nd?

    Using VBE's object browser to navigate between the two.
    upload_2018-8-10_12-29-10.png
  7. Frncis

    Frncis Member

    Messages:
    43
    Yes. We have been copying & pasting. I was wondering if there is formula, or some other way of do it.
    Last edited by a moderator: Aug 10, 2018
  8. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,012
    There are ways.

    1. formula
    =Sheetname!Cell

    So just direct link to value stored in another sheet.

    2. VBA
    Using Worksheet_Change event to trigger code.
    Something like below in Sheet1 to make same change in Sheet2.
    Code (vb):
    Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("E4:P29")) Is Nothing Then
        Sheet2.Range(Target.Address).Value = Target.Value
    End If
    End Sub
  9. Frncis

    Frncis Member

    Messages:
    43
  10. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,012
    Then just add if condition to check if Target.Value = "H".
  11. Frncis

    Frncis Member

    Messages:
    43
    ok. I placed the Worksheet_Change macro on sheet 1 (Annual Leave Record). When I entered "H" on sheet 1, I received a False in the corresponding cell on sheet 2 (Sick Leave Record) I have included a sample. What am I doing wrong?

    Attached Files:

  12. Marc L

    Marc L Excel Ninja

    Messages:
    4,253

    [​IMG] Virus alert on my end whatever from post #1 or 11 attachments …
  13. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,012
    Code (vb):
    Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("E4:P29")) Is Nothing Then
        If Target.Value = "H" Then
            Sheet2.Range(Target.Address).Value = Target.Value
        End If
    End If
    End Sub
  14. Frncis

    Frncis Member

    Messages:
    43
    Probably 11. Could you use post #1 as your working sample?
  15. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,012
    I used file from #1 as sample. I didn't even open #11.

    Either case, I believe you are using MSForms which I don't use/have on my machine. So I didn't test the entire thing. But code snippet is tested.

    Copy and paste the code into Sheet1 module (Sick Leave Record).
    Frncis likes this.
  16. Frncis

    Frncis Member

    Messages:
    43
    No idea. What I was doing wrong. But it does work. I viewed the objects & found that I the sheet names backward. Thanks for your assistance & teaching.
    Last edited by a moderator: Aug 14, 2018
  17. Devender Kumar Yadav

    Devender Kumar Yadav New Member

    Messages:
    5
    Simple way:

    Just right click on sheet name and choose select all sheets.
    after this you have to changed in one sheet , all sheets will changed
  18. bobhc

    bobhc Excel Ninja

    Messages:
    3,403
    Enter your data in the main sheet, go to the sheet where you want to paste/copy. Select the cell where you want to place the copied data and in the formula bar insert the equals sigh, click on the sheet tab of your main data select the cell with data and press enter. Done

Share This Page