Copy and Paste certain values to a certain cell

[ExcelNewbie2020]

Experts,

I have a table with columns from week 1 to week 52. I would like to ask for assistance if it is possible that every time I enter certain text into a cell, it will copy and paste the same text into another cell. Really appreciate your help..


See below;

Let say, In Name1 I insert a check (1) on W1, it should copy and paste automatic quarterly, (W12,W24, W36, and W48)
In Name2 I insert a check (1) on W4 it should copy and paste to W15,W27,W39 and W51
The first entry is always in random, it could be in any week.


Note:1 = check symbol on my original file

Name

W1

W2

W3

W4

W5

W6

W7

W8

W9

W10

W11

W12

W13

W14

W15

W16

W17

W18

W19

W20

W21

W22

W23

W24

W25

W26

W27

W28

W29

W30

W31

W32

W33

W34

W35

W36

W37

W38

W39

W40

W41

W42

W43

W44

W45

W46

W47

W48

W49

W50

W51

W52

Name1​

1

1

1

1

1

Name2​

1

1

1

1

1

Name3​

Name4​

Name5​

Name6​

Name7​

Name8​

Name9​

Name10​

 

[My Aswer Is This]

Is Name1 in column 1 Row 2 of you sheet ?

 

[ExcelNewbie2020]

Is Name1 in column 1 Row 2 of you sheet ?

Yes it is

 

[My Aswer Is This]

Try this:
Will work with column 2 to 53 it looks like you have names in column 1
Modify if needed
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  7/17/2022  2:56:28 PM  EDT
Application.EnableEvents = False
If Target.Column > 1 And Target.Column < 54 And Target.Row > 1 Then
Target.Offset(, 11).Value = Target.Value
Target.Offset(, 23).Value = Target.Value
Target.Offset(, 35).Value = Target.Value
Target.Offset(, 47).Value = Target.Value
Application.EnableEvents = True

MsgBox "Done"
End If
End Sub

 

[ExcelNewbie2020]

Try this:
Will work with column 2 to 53 it looks like you have names in column 1
Modify if needed
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  7/17/2022  2:56:28 PM  EDT
Application.EnableEvents = False
If Target.Column > 1 And Target.Column < 54 And Target.Row > 1 Then
Target.Offset(, 11).Value = Target.Value
Target.Offset(, 23).Value = Target.Value
Target.Offset(, 35).Value = Target.Value
Target.Offset(, 47).Value = Target.Value
Application.EnableEvents = True

MsgBox "Done"
End If
End Sub

Thanks a lot it works . However when I insert another value (other than 1) it also replicates.. Would it be possible that if the other value was used, it would not replicate?

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  7/18/2022  11:09:33 AM  EDT
Application.EnableEvents = False
If Target.Column > 1 And Target.Column < 54 And Target.Row > 1 Then
If Target.Value = 1 Then
    Target.Offset(, 11).Value = Target.Value
    Target.Offset(, 23).Value = Target.Value
    Target.Offset(, 35).Value = Target.Value
    Target.Offset(, 47).Value = Target.Value
End If

Application.EnableEvents = True

End If
End Sub

 

[ExcelNewbie2020]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  7/18/2022  11:09:33 AM  EDT
Application.EnableEvents = False
If Target.Column > 1 And Target.Column < 54 And Target.Row > 1 Then
If Target.Value = 1 Then
    Target.Offset(, 11).Value = Target.Value
    Target.Offset(, 23).Value = Target.Value
    Target.Offset(, 35).Value = Target.Value
    Target.Offset(, 47).Value = Target.Value
End If

Application.EnableEvents = True

End If
End Sub

cool.. works like a charm.. thank you very much..

 

[My Aswer Is This]

cool.. works like a charm.. thank you very much..

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.