-
Nov 20th, 2008, 04:25 AM
#1
Thread Starter
Addicted Member
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.
-
Nov 20th, 2008, 04:40 AM
#2
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)
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...
-
Nov 20th, 2008, 04:59 AM
#3
Thread Starter
Addicted Member
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
-
Nov 20th, 2008, 06:26 AM
#4
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?
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|