Microsoft Office Forums - View Single Post - Problem with copying data over from different spreadsheet

Javi78503 · 07-31-2024, 10:22 AM

Hello everyone, this is going to be a bit of a long post, but i'm not sure how to fix this problem. To start i'm a vba beginner so, please don't be too harsh when reviewing. I'll try to organize this as best I can.

I have two seperate excel sheets, one is data provided from our finance dept (the one with redacted columns which i'll call Ledger) and i take information from that and import only needed information into a different spreadsheet which i'll refer to as FS (final sheet)

my process is as follows:
1. in FS I clear all existing data from the sheet, leaving only rows of names
2. in Ledger, find the row with "Project ID" since not all sheets start at row 1
3. in ledger, find the column with Employee Names
2. use each name in FS to sort thru names column in the Ledger until it matches with a name.
3. if a match is made it inserts a row in FS under the name header.
4. copy data over from Ledger into FS
5. delete the entire column of data in Ledger once it's copied.
6. rise and repeat until it finishes finding all matches to that name.
7. moves on to next name in the list on FS
8. goes back to #3. and continues until all the data in the Ledger "should" have been read, copied, and deleted.

my issue is, it always leaves 1 or 2 rows of data in Ledger from the final name on my list and i'm not sure why.

Code:

For Each cell In Worksheets("G4010").Range("A1:A" & Worksheets("G4010").Cells(Rows.Count, "A").End(xlUp).Row)
    If cell.Value = "Project ID" Then
        Set startPoint = cell
        Exit For
     End If
Next cell
    
If Not startPoint Is Nothing Then ' if no starting point found then do nothing
    For Each cell In Worksheets("G4010").Range(startPoint, startPoint.End(xlToRight))
        If InStr(1, cell.Value, "Employee Name", vbTextCompare) > 0 Then 'finding employee name column range
            For Each empCell In Range(cell, cell.End(xlDown))
                goodText = cleanUp(empCell.Value) 'cleaning up name value for comparison
                If InStr(1, goodText, name, vbTextCompare) > 0 Then 'comparing name vs name provided in call
                    If LCase(empCell.Offset(0, 3).Value) = "x" Or empCell.Offset(0, 11).Value = 0 Then 'don't post enc colums or $0 value columns
                        empCell.EntireRow.Delete
                    Else
                        ThisWorkbook.Worksheets("SALARIES").Range(location).Offset(1, 0).EntireRow.Insert 'inserting row
                        ThisWorkbook.Worksheets("SALARIES").Range(location).Offset(1, 0).EntireRow.Interior.Color = xlNone 'removing formatting
                        ThisWorkbook.Worksheets("SALARIES").Range(location).Offset(1, 0).Value = name 'inserting name
                        ThisWorkbook.Worksheets("SALARIES").Range(location).Offset(1, -1).Value = empCell.Offset(1, -3).Value2 'inserting date
                        ThisWorkbook.Worksheets("SALARIES").Range(location).Offset(1, 3).Value = UCase(MonthName(Month(empCell.Offset(0, -3).Value2), True)) 'inserting month
                        ThisWorkbook.Worksheets("SALARIES").Range(location).Offset(1, 5).Value = empCell.Offset(0, 11).Value 'inserting monetary value
                        empCell.EntireRow.Delete
                    End If
                End If
            Next empCell
            Exit For
        End If
    Next cell
End If

i've tried unmerging all cells in sheet, just in case they're throwing it off. i've cleared all cells under the table of data just in case there's something i'm not seeing. i'm kind of at a loss here...

any help is appreciated and if there's anything else you need to see, please let me know.