Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Non-adjacent cells

  1. #1
    Board Regular kojak43's Avatar
    Join Date
    Feb 2002
    Posts
    270
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi K

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

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

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  6. #6
    Board Regular kojak43's Avatar
    Join Date
    Feb 2002
    Posts
    270
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

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

    Code:
    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •