VBA Macro. I Think I'm Close But Something Is Not Right

[zunebuggy]

I have an Excel sheet that has date in this format:

Group ID	Complete Path	Filename
0	C:\Folder1\R0CVNM6.pdf	R0CVNM6.pdf
0	J:\FolderA\f303919232.pdf	f303919232.pdf
1	C:\Folder1\R0YC44P.pdf	R0YC44P.pdf
1	J:\FolderA\c34563.pdf	c34563.pdf
1	H:\FolderZ\t342332.pdf	t342332.pdf
1	K:\FolderS\j73838a.pdf	j73838a.pdf

etc...

The Group ID goes up to 51374 but there are 115590 rows in this spreadsheet with the Group ID having a minimum of 2 identical numbers (i.e. two 0's, four 1's, etc.). The maximum count having the same Group ID is 21.

I would like my script to go through each row 0 through 115590 and take the 1st row of each Group ID and move that file to a folder and all the rest with the same Group ID, move it to a different folder. Then move on to the next Group ID and do the same. Repeat...

Here is my first attempt, but it only copied 1 file and then skipped the rest.

Sub MoveFiles()    
zz = 2
    For j = 0 To 20 '51374
        aa = Range("A" & zz)
        bb = Range("B" & zz)
        cc = Range("C" & zz)
        If j = aa And fth = 0 Then
            fth = 1
            Name bb As "J:\kfolder\" & cc
        End If
        If j = aa And fth = 1 Then
            Name bb As "J:\ffolder\" & cc
        End If
        zz = zz + 1
    Next j
End Sub

There has to be a better approach to this.

Also, some of these filenames (very few) have special characters and Excel errors on these. I would like Excel to just ignore and maybe flag or highlight these.

I do know how to use Kill to delete the final files, but I want to get the code working first before deleting a bunch of files.

Thank you,
Zune

[Aussiebear]

Welcome to VBAX Zunebuggy. What does the variable fth stand for?

[zunebuggy]

Welcome to VBAX Zunebuggy. What does the variable fth stand for?

In my mind, "First Time Here". It's just a flag.

[zunebuggy]

I was trying to use that so it only moves the first unique Group ID to the kfolder and the rest with that same Group ID to the ffolder.

[zunebuggy]

I see that I need to reset this back to zero every time the Group ID number changes. My Excel sheet is sorted by Group ID if that makes it easier. I did check and there are 2 or more of rows with the same Group ID. Many have 2 of the same Group ID, some have 3, some have 4, some have 5, all the way up to the maximum count is 21. I guess if they each just had two of the same Group IDs it would be easy. I could just For Next it sending Odd numbered rows to one folder and the even numbered rows to another, but the sheet is more complicated than that.

[Paul_Hossler]

I think that you're over thinking it

Option Explicit


Sub MoveFiles()
    Dim rFiles As Range
    Dim r As Long
    
    Set rFiles = ActiveSheet.Cells(1, 1).CurrentRegion


    With rFiles
        For r = 2 To .Rows.Count
            If .Cells(r, 1).Value = .Cells(r - 1, 1).Value Then '   same group
                '  Name CStr(.Cells(r, 2).Value) As "J:\kfolder\" & .Cells(r, 3).Value
                Debug.Print "Group = " & .Cells(r, 1).Value & " -- Move " & CStr(.Cells(r, 2).Value); " to " & "J:\kfolder\" & .Cells(r, 3).Value
            Else
                '   Name CStr(.Cells(r, 2).Value) As "J:\ffolder\" & .Cells(r, 3).Value '   row r is one group and row r-1 is another
                Debug.Print "Group = " & .Cells(r, 1).Value & " -- Move " & CStr(.Cells(r, 2).Value); " to " & "J:\ffolder\" & .Cells(r, 3).Value
            End If
        Next r
    End With
 End Sub

Debug output

Group = 0 -- Move C:\Folder1\R0CVNM6.pdf to J:\ffolder\R0CVNM6.pdf
Group = 0 -- Move J:\FolderA\f303919232.pdf to J:\kfolder\f303919232.pdf
Group = 1 -- Move C:\Folder1\R0YC44P.pdf to J:\ffolder\R0YC44P.pdf
Group = 1 -- Move J:\FolderA\c34563.pdf to J:\kfolder\c34563.pdf
Group = 1 -- Move H:\FolderZ\t342332.pdf to J:\kfolder\t342332.pdf
Group = 1 -- Move K:\FolderS\j73838a.pdf to J:\kfolder\j73838a.pdf

[zunebuggy]

Thank you. That worked very well.