EXCEL 2000 VBA Append and Update

[jadams0173]

Good day all!

Using Excel 2000 I was doing what I thought was going to be within my excel vba skills. Well, I was wrong. Here is what I need to do and what I've already got.

What's needed:
Append or update Data from source sheet to destination sheet. If the data already exists in the destination sheet then I need to update one of the cells to make sure it is current. If it doesn't exist then I need to add the 3 cells to the sheet. I can do the filter and copy the cells from the source to the destination, but I'm not sure how to update if the already exist. (My mind is clouded with SQL statemnents but I'm not sure that's the right way to go.)

Sample Source Data
BU Fmly SO Item Open Comments
one 002 30458 64854A 8 Comment 1
Two 354 30564 45897C 60 Comment 2
three 508 30659 82032C 43
Four 965 30963 45879Q 5 Comment 4

Destination only holds the SO, Item and Comments and is a static sheet. I need to either update the comments for a given SO and Item or append all 3 fields to the sheet if they don't exist.
I can copy them to the sheet but the update and append if they aren't there is where I'm stuck. I'm doing this on the before close event of the workbook becuase the source sheet is recreated every day and we need to keep the comments in tack.

Part 2 of the delima is updating the comment cells in the source sheet from the destination sheet on workbook open if the SO and Item exist in the source sheet.

Here is how I do the copy. I haven't even gotten close enough on the update and append to get something to "almost" work. I really don't even know where to begin.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'This should take all the MO #'s with comments and move them to the new tab

Dim uCol    As Long
Dim uRow    As Long
Dim lRow    As Long
Dim myRange As Range

'this turns the filter on
With Sheets("WipAgeDetails")
    With .Cells(1, 1)
        .AutoFilter
        .AutoFilter Field:=12, Criteria1:="<>"
    End With


'this is a function call to copy MO and Item to comments tab
CopyMOItemToCommentsTab
'this is a function call to copy the comments to the comments tab
CopyCommentsToCommentTab
'TURN OFF THE AUTOFILTER
    .[A1].AutoFilter
End With
Sheets(1).Select
ActiveWorkbook.Save

End Sub

Function CopyMOItemToCommentsTab()
Dim uCol    As Long
Dim uRow    As Long
Dim lRow    As Long
Dim myRange As Range

With Sheets("WipAgeDetails")
    With .[A1].CurrentRegion
        uCol = .Columns.Count
        uRow = .Rows.Count
    End With
    'now copy the filtered data
    'this will copy the mo and item number
    .Range(.Cells(2, 3), .Cells(uRow, 4)).Copy
End With
'now COPY column C(MO),D(item) to the new tab
With Sheets("WipAgeComments")
    lRow = .[A1].CurrentRegion.Rows.Count + 1
    .Cells(lRow, 1).PasteSpecial xlPasteValues
End With
End Function
Function CopyCommentsToCommentTab()
Dim uCol    As Long
Dim uRow    As Long
Dim lRow    As Long
Dim myRange As Range

With Sheets("WipAgeDetails")
    With .[A1].CurrentRegion
        uCol = .Columns.Count
        uRow = .Rows.Count
    End With
    'now copy the filtered data
    'this will copy the comment
    .Range(.Cells(2, 12), .Cells(uRow, 12)).Copy
End With
'now move column L(comment) to the new tab
With Sheets("WipAgeComments")
    lRow = .[A1].CurrentRegion.Rows.Count + 1
    .Cells(2, 3).PasteSpecial xlPasteValues
End With
End Function

 

[SkipVought]

I'd simply use MS Query to return those two columns. Once added to your sheet, all you have to do is Data > Refresh.

You could also use the PivotTable wizard and hide the aggregation column.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jadams0173]

I'll give MS Query a shot Skip. I've never used it though.

 

[SkipVought]

I use alot! Great tool!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jadams0173]

I can't seem to find it. I went to office tools but I don't see it. I went to add/remove programs and it shows that it is installed.

 

[SkipVought]

Data > Import External data...

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jadams0173]

If all of my data is contained in my workbook, do I still have to setup a connection? I don't want to have to setup a connection on the users machines.

 

[SkipVought]

Set up a connection and query.

Then turn on your macro recorder and record EDITING the query. From the Query Editor, File > Return data to Excel.

Copy your recorded code and paste back for help in customizing to work on ANY user's PC.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}