Copying a template workbook, renaming, and editing as part of a loop

CodePest

New Member
Joined
Jan 16, 2018
Messages
13
Hello all,

I've run into a problem with some code I'm writing. I can't seem to get my code to copy and rename a file consistently. It will do it once, but then it doesn't work after the first name. Essentially, I have a table of employee names in a workbook. I've written macro that will rename a template based on the information in the list, here's an example:
ABCD
1NameUserIDPositionStatus
2Alice Calous1101LeadActive
3Joe Geronimo2202AgentActive
4Krissi Prissy3303AgentActive
5Larry Glarey4404AgentActive
6dMonthdYear
7February2018

<tbody>
</tbody>

Here is my code:

Code:
Sub DirectoryCheck()


' Prevents screen flashing during hide/unhide
    Application.ScreenUpdating = False
        
' Define Folder attributes
    Dim dMonth As String
        dMonth = ActiveSheet.Range("C7").Value
    Dim dYear As String
        dYear = ActiveSheet.Range("D7").Value
    
' Define Counters
    Dim FolderCount As Integer
        FolderCount = 0


    Dim TrackPath As String
        TrackPath = "C:\SC\Hub" & dYear
    
    
' Check if File directory for dYear exists


    Dim NewYearFolder As Object


    Set NewYearFolder = CreateObject("Scripting.FileSystemObject")
        
        If NewYearFolder.FolderExists(TrackPath) Then
            
            ' MsgBox ("Folder already exists for " & dYear & ".")
            
            ' If folder exists then create Agent Folders
             Call MakeSheets.CreateFolder(dMonth, dYear, TrackPath, FolderCount)
                
        Else
            
            ' If folder does not exist, create year folder and then create agent folders
            NewYearFolder.CreateFolder (TrackPath)
            MsgBox ("New folder created for " & dYear & " Loans Hub.")
            Call MakeSheets.CreateFolder(dMonth, dYear, TrackPath, FolderCount)
        
        End If


' Prevents screen flashing during hide/unhide
    Application.ScreenUpdating = True
    
MsgBox (FolderCount & " new folders were added.")
ActiveWorkbook.FollowHyperlink TrackPath
    
End Sub



Sub CreateFolder(dMonth As String, dYear As String, TrackPath As String, FolderCount As Integer)


' Define range of agents
    Dim aStart As Integer
        aStart = 2
    Dim aEnd As Integer
        aEnd = 5


    Dim UserID As String
    Dim AgentName As String
    Dim Position As String
    Dim AgentPath As String
    Dim FilePath As String
    
 ' Template folder location
    Dim TemplatePath As String
        TemplatePath = "I:\SC\Hub\TEMPLATE"
   


For i = aStart To aEnd


    ' Define new folder terms
    AgentName = ActiveSheet.Range("D" & i).Value
    UserID = ActiveSheet.Range("E" & i).Value
    Position = ActiveSheet.Range("F" & i).Value
    AgentPath = TrackPath & "" & AgentName
    FilePath = AgentPath & "" & AgentName & ", " & UserID & " - " & dMonth & ", " & dYear & ".xlsm"

' AgentFolder is new file object to be created
    Dim AgentFolder As Object
        Set AgentFolder = CreateObject("Scripting.FileSystemObject")
    
' Defines sheet that will be created
    Dim NewFile As Object
        Set NewFile = CreateObject("Scripting.FileSystemObject")

    
    ' Stop when list ends
    If AgentName = "" Then
        Exit For
        Else
            ' If folder exists then create Tracking Sheets
            If AgentFolder.FolderExists(AgentPath) Then
                
                ' Check if sheet exists, if not make sheet
                    If NewFile.FileExists(FilePath) Then
                    
                        MsgBox ("A tracking sheet already exists for " & AgentName & " for " & dMonth & ", " & dYear)
                    
                    Else
                            MsgBox (FilePath & " will be created.")
                            Set NewSheet = CreateObject("Scripting.FileSystemObject")
                            
                            NewFile.CopyFile TemplatePath & "\Agent App Tracker.xlsm", FilePath
                            
                            Set NewSheet = Nothing
                    
                    End If
                
            ' if not, create agent folder
            Else
            
                AgentFolder.CreateFolder (AgentPath)
                MsgBox (FilePath & " will be created.")
                Set NewSheet = CreateObject("Scripting.FileSystemObject")
                            
                NewFile.CopyFile TemplatePath & "\Agent App Tracker.xlsm", FilePath
                            
                Set NewSheet = Nothing
                FolderCount = FolderCount + 1
            
            End If
        End If
    
Next i


End Sub

The frustrating thing is the code will create the folders, but it will not copy the file. I get an error message that says path does not exist, but for some reason it works for the first name and not the others. I've removed the filecopy code to verify that it will create folders, but when I add the filecopy code back in, it will work for the first name but not the others.

After I get the file copy code working, I also need to open that book and add the AgentName, UserID, dMonth, and dYear onto sheet1 of the new workbook, then close and save it.

I know my code is messy so if there is anything I can do differently, I'm all ears!

Thank you!

Cheers!
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

CodePest

New Member
Joined
Jan 16, 2018
Messages
13
I did just note that I had NewSheet written where I should have had NewFile, I made that correction, but still the same problem.
 

CodePest

New Member
Joined
Jan 16, 2018
Messages
13
I'm getting runtime error 76, but why does it work with the first name and not the second???
 

CodePest

New Member
Joined
Jan 16, 2018
Messages
13
OMG!!!!! I just went through my employee list reference and found spaces on the end of the two names I happened to be having problems with! :banghead:
 

Watch MrExcel Video

Forum statistics

Threads
1,101,800
Messages
5,482,970
Members
407,371
Latest member
gdjenkins80

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top