Problem with copying data over from different spreadsheet

Javi78503 · #1 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.

p45cal · #2 08-01-2024, 03:39 AM

As an exercise, set up a new sheet with values 1 to 10 in cells C5 and down:
2024-08-01_110939.jpg

Here's the code to copy/paste:

Code:

Sub blah()
For Each empCell In Range(Range("C5"), Range("C5").End(xlDown))
  empCell.Select
  empCell.EntireRow.Delete
Next empCell
End Sub

Now use F8 on the keyboard to step through the code, paying particular attention to what's selected (to indicate what's going to be acted upon). The second iteration of the loop doesn't select what you expect; it selects the 3 instead of the 2! This is because the rows have all shifted up when a row is deleted.

Let the macro run to its finish and you're left with:
2024-08-01_111844.jpg

There are several ways to get around this, one of them being to iterate from the bottom of the range up:

Code:

Sub blah2()
Set rngEmpNames = Range(Range("C5"), Range("C5").End(xlDown))
For i = rngEmpNames.Rows.Count To 1 Step -1
  rngEmpNames.Cells(i).Select
  rngEmpNames.Cells(i).EntireRow.Delete
Next i
End Sub

but this means you end up pasting things in a different order on the destination sheet.

Another way is to iterate as you have done but do no deleting. Instead, mark the rows for deletion later. This doesn't involve actual marking, instead you add the cells, whose rows you want to delete, to a range variable in the code, and when all the copying has been completed, delete those rows.
For example:

Code:

Sub blah3()
Dim rngToDelete As Range
For Each empCell In Range(Range("C5"), Range("C5").End(xlDown))
  empCell.Select  'you can lose this line later
  If Application.WorksheetFunction.IsEven(empCell.Row) Then 'just a way of not deleting all rows
    If rngToDelete Is Nothing Then Set rngToDelete = empCell Else Set rngToDelete = Union(rngToDelete, empCell)
  End If
Next empCell
'rngToDelete.Select ' you can include this line if you're stepping through the code with F8 just to show you what rngToDelete is.
If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete
End Sub

Javi78503 · #3 08-01-2024, 08:11 AM

P45Cal, thank you very much for your awesome explanation, it really helped me understand where my issue was coming from! I think the easiest thing for me to implement would be iterating from the bottom up, and am going to give that a go right now.

Edit: it seems to have worked perfectly! Thanks again, so much!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Copying data from one spreadsheet to another	muna	Excel	1	12-11-2017 05:23 AM
calculate spreadsheet data	ewso	Excel Programming	3	07-30-2017 07:25 AM
Copying rows from one spreadsheet to another	Colonel Biggs	Excel	3	02-15-2015 08:23 AM
Copying an Excel spreadsheet into Word with gridlines and a page break	Cheryl	Word	2	09-04-2014 06:24 AM
Creating Invoices using data from a spreadsheet	tjcusick	Excel Programming	3	12-05-2013 08:36 AM