Results 1 to 4 of 4

Thread: moving data between worksheets

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Sep 2008
    Posts
    128

    moving data between worksheets

    I have a form which makes use of the WORKSHEETSELECTCHANGE function, the worksheetselectchange function runs this code.

    Code:
    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
        MyDate = Date
    
        If UCase(Sh.Name) = "ALL" Then
            With Target
                If .Row > 5 And .Column = 19 Then
                    If Cells(.Row, "S") = "OPEN" Then
                        Application.EnableEvents = True
                        Cells(.Row, "S") = "CLOSED"
                        Cells(.Row, "E") = MyDate
                        Application.EnableEvents = True
                        Range("E:E").EntireColumn.AutoFit
                        Range("S:S").EntireColumn.AutoFit
                    End If
                End If
            End With
            Exit Sub
        End If
    this procedure allows me to select certain cells on my worksheet called "ALL". When you click in a cell in column S, it makes a note of the cell actually clicked in and then updates cell E and cell S of the same row with certain information. Although this allows me to perform this action on the activesheet.

    All the data showing on worksheet "ALL" is received from what I call a history worksheet, which contains all rows of data at any given point in time. As a result if someone invokes the worksheetselectchange event on worksheet "ALL" I would like to update the worksheet "ALL BILLS" at the same time, without further interaction from the user.

    some background information

    I use the following code to obtain the original information

    Code:
        For i = 1 To Active
            For j = 0 To 18
                'Store values from Sheet "ALL BILLS" in an array
                arrayvalues(i, j) = Sheets("ALL BILLS").Cells(r, j + 1).Value
            Next
            'increase row number
            r = r + 1
        Next
    as a result the array holds the original data before the worksheetselectchange event is run, could I update this array with the changed values and then use the array in some way to do a compare on the original worksheet to move the values or is there a better way.

    hopefully this makes sense and someone will be able to help me.

  2. #2
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: moving data between worksheets

    What is stored on All Bills?
    Is it an audit of the changes made?

    Is this excel 2003?
    If so what happens when all bills has more than 65534rows?

    Do you want it to find the data in all bills and amend it?
    Or just add a new row to the bottom?
    (the latter is pretty easy to do)

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Sep 2008
    Posts
    128

    Re: moving data between worksheets

    All Bill contains rows of user entered information from a userform

    Once the user has entered the values, the code places all the entered information in to a row occupying various cells 1 value per column.

    I am using office xp

    the information that is being changed needs to be transferred to its original row.

    i am currently pondering over adding a cell when the information is created which adds a unique reference point per row which doesn't change a bit like a record number, as this record number is unique to a specific row I was thinking that I could use the record number for comparing, as a result it would allow me to use this reference point to allow changes to be performed

  4. #4
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: moving data between worksheets

    Sounds like a plan At least you will be able to find the data in the All sheet quicker/more accurately

    Post up if that doesn't work.

    It seems to be moving more towards a database than an excel sheet. Perhaps it is worth considering MS Access?

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

Posting Permissions

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



Click Here to Expand Forum to Full Width