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

  • 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?

Frncis

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

Attachments

Chihiro

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

Frncis

Member
While pressing CTRL Key, using mouse to select both sheets (using sheet tab at bottom).
View attachment 54388
While you have both sheets grouped in above manner. Change you make in cell will be replicated to both sheets.
Thank you for being more descriptive. The sheet tabs were removed because issues, the other users were having.
 

Chihiro

Excel Ninja
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
 

Frncis

Member
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:

Chihiro

Excel Ninja
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:
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
 

Frncis

Member
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:
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
[/quote
I only want to copy the cells that have the letter "H". H= holidays.
 

Frncis

Member
Then just add if condition to check if Target.Value = "H".
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?
 

Attachments

Chihiro

Excel Ninja
Code:
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
 

Frncis

Member
Code:
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
Probably 11. Could you use post #1 as your working sample?
 

Chihiro

Excel Ninja
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

Member
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:

bobhc

Excel Ninja
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
 
Top