• 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

  • Leave calculator.xlsm
    71.4 KB · Views: 9
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.
 
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.
 
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
 
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:
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
 
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.
 
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

  • sample 2.xlsm
    74 KB · Views: 3

panneau-tournant-danger.gif
Virus alert on my end whatever from post #1 or 11 attachments …
 
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
 
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?
 
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).
 
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:
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
 
Back
Top