Non-adjacent cells

[kojak43]

A week or so ago Dave Hawley kindly gave me the following code.

On the sheet you wish to enter your data, right click on the sheet name tab, select "view Code" and paste in this


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2:Q2000")) Is Nothing Then
Sheets("Sheet2").Range(Target.Address) = Target
Sheets("Sheet3").Range(Target.Address) = Target
End If
End Sub


From now on whenever you enter any data on this sheet it within the range "A2:Q1000" it will be automatically mirrored on sheet2 and 3

As it say's it will place all the values from one sheet into two other sheets.

In Sheet 1 the data goes from A2:Q2000. However I only wish to take certain cells from Sheet 1 and place them in Sheet2, and those cells, from sheet1 are not adjacent
I start in A2:A2000, C2:C2000, F2:G2000, J2:O2000 and R2:S2000.

So how can I modify the code to make it work.
What I put in, results in a runtime error 1004. What I did was change A2:Q2000 to read ("Sheet1!A2:A2000, Sheet1C2:C2000") Etc

Any help is greatly appreciated.
K

[Tom Schreiner]

Here's a modification of Dave's code which is what I think you want.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2:A2000")) Is Nothing Then
Sheets("Sheet2").Range(Target.Address) = Target
End If
If Not Intersect(Target, Range("C2:C2000")) Is Nothing Then
Sheets("Sheet2").Range(Target.Address) = Target
End If
If Not Intersect(Target, Range("F2:G2000")) Is Nothing Then
Sheets("Sheet2").Range(Target.Address) = Target
End If
If Not Intersect(Target, Range("J2:O2000")) Is Nothing Then
Sheets("Sheet2").Range(Target.Address) = Target
End If
If Not Intersect(Target, Range("R2:S2000")) Is Nothing Then
Sheets("Sheet2").Range(Target.Address) = Target
End If
End Sub

Tom

[Dave Hawley]

Hi K

Tom's method will certainly work but you could simplify it to:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2:A2000,C2:C2000,F2:G2000,J2:O2000, R2:S2000")) Is Nothing Then
Sheets("Sheet2").Range(Target.Address) = Target
Sheets("Sheet3").Range(Target.Address) = Target
End If
End Sub

[Tom Schreiner]

You know, I tried that Dave and it did not work?
Will copy your code and see if I was doing it wrong...
Tom

[Tom Schreiner]

Ah. It works!
I was doing it wrong...
If Not Intersect(Target, Range("A2:A2000),Range(C2:C2000), ect...) Is Nothing Then

[kojak43]

On 2002-04-09 16:32, Dave Hawley wrote:
Hi K

Tom's method will certainly work but you could simplify it to:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2:A2000,C2:C2000,F2:G2000,J2:O2000, R2:S2000")) Is Nothing Then
Sheets("Sheet2").Range(Target.Address) = Target
Sheets("Sheet3").Range(Target.Address) = Target
End If
End Sub

Thank you both. I entered the code. I only changed the sheet name to match my existing sheet. I then entered data into Sheet1 and when I went to my sheet, the new data was not there.
I then inserted a new worksheet, named it, and change the code to reflect the new worksheet. I added data to Sheet1 and it added the data to the new sheet.
The data from Sheet1 was non-adjacent cells, that is, A,C,F,G,H ect. And that is how it placed the data in the new worksheet. Which means columns B,D,E were blank.

Oh Geeze, As the late Gilda Radner via Roseanne Rosanna Danna, would say, Never Mind!
I just realized why this did not work how I wished.
This project started with what is now Worksheet2. It had about 100 rows of data. WorkSheet1 evolved as a way to include more information. Part of which would be placed into Worksheet2. However Worksheet1 only has about 30 rows of test data, when I tried to flow it into the other sheet, those rows were filled, so it did not show up.
I have to populate the new sheet1 with all the data from the current sheet2. Then I can begin to added new data in sheet1 and have it move into sheet2.
I am such an idiot!. But thank you both. When I get this sorted out, it is going to be great.