Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jan 2002
    Texas, USA
    Thanked 0 Times in 0 Posts

    Transferring data to a new sheet (Win ME/OXP-SP2)

    In a workbook that has 2 worksheets, the first sheet contains a row where values are entered into several cells. There is also a cell where the date related to the values entered in the other cells is entered. All of these values change periodically; no data is retained in this sheet.

    The second worksheet is where I want to record a history of data entered in the first worksheet automatically. The 2nd sheet has a column (A) listing dates that data will be entered for the remainder of the year. Other cells that are to receive data have an =IF statement that compares the date column from Sheet1 (A) to the date in column A in Sheet2 and equates Sheet1 cells (B,C,D.) to Sheet2 cells(B,C,D) when dates are equal (ie, future dates) [EX. =IF($A2=SHEET1,$A$2,SHEET1!B$2," "] . Placing " " after the final comma fill cells for dates that haven't occurred yet with blanks, of course.

    I want the cells B,C,D in Sheet2 to record permanently the values in cells B,C,D from Sheet1; but, as it stands, previous values revert to zeroes when new data with new dates are entered because the test on dates is no longer true. If, after putting the new data in the row in Sheet2, there were a way to copy the data then PasteSpecial/Values back into the same range automatically, this would solve the problem; however, I haven't found a way to do this automatically. Recording a macro would work, but the user would have to remember to execute it or the data would be lost. VBA is beyond me. I don't want users to be able to enter data in Sheet2 so that the data integrity as entered in Sheet1is maintained.

    This may be an inappropriate solution to the problem. If so, please suggest an alternative. If not, please suggest how to modify what I have so far.

    All suggestions greatly appreciated and gratefully considered.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    South Carolina, USA
    Thanked 0 Times in 0 Posts

    Re: Transferring data to a new sheet (Win ME/OXP-SP2)

    The VBA code below, placed in the Worksheet Change Event routine of the worksheet where the data is being entered should save the values entered in that sheet to the appropriate row on Sheet2. You did not say which row the data was being entered into so the attached code assumes row 3. If you are using a different row, then the two places where ActiveSheet.Range("B33") appears change both 3's to the row you are using. You should also remove all of the formulas from Sheet2.

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    Dim lLastRow As Long, I As Long
    If Intersect(Target, ActiveSheet.Range("B33")) Is Nothing Then Exit Sub
    With Worksheets("Sheet2")
    lLastRow = .UsedRange.Row + .UsedRange.Rows.Count - 2
    End With
    For Each oCell In Intersect(Target, ActiveSheet.Range("B33"))
    If oCell.Value <> "" And ActiveSheet.Range("A" & oCell.Row).Value <> "" Then
    For I = 0 To lLastRow
    If Int(ActiveSheet.Range("A" & oCell.Row).Value) = _
    Int(Worksheets("Sheet2").Range("A1").Offset(I, 0).Value) Then
    Worksheets("Sheet2").Range("A1").Offset(I, oCell.Column - 1).Value = oCell.Value
    Exit For
    End If
    Next I
    End If
    Next oCell
    End Sub

    Legare Coleman

Posting Permissions

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