0

My VBA Code works in Debug Mode (F8) but not when I run it as normal; the program crashes. I've read through a few different fixes but nothing has solved my problem so I am going to post a new question.

My VBA Macro use to work without error but for some reason it is hitting a snag. I am posting only the portion of my code in which the error is occurring. What the following code does is:

  1. Opens up each csv file in "File_Path" (Do note I changed the name for confidentiality).
  2. Reformats the contents in the workbook
  3. Saves it as a xlsx in the File_Path_xlsx location.

Thanks!


Sub Loop_Format()




    Dim FSO, folder, Wb As Object
    Dim Path, FileName, csvPath, xlsPath  As String
    Dim LR              As Integer
    Dim Wkb             As Workbook
    Dim ActiveWB        As Workbook
    Dim ws              As Worksheet
    Dim lastRow         As Integer

    
''File Locations''

    csvPath = "File_Path"
    xlsPath = "File_Path\xlsFolder"
    
''''''''''''''''''''''''''''''
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set csvFolder = FSO.GetFolder(csvPath)
    
    If FSO.FolderExists(xlsPath) = False Then
        FSO.createFolder (xlsPath)
    End If
    
    Set xlsFolder = FSO.GetFolder(xlsPath)
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
    'for each statement to loop over each file within the folder
    For Each Wb In csvFolder.Files
        If LCase(Right(Wb.Name, 3)) = "csv" Then
            Set ActiveWB = Workbooks.Open(Wb)
            ActiveWB.SaveAs FileName:=xlsPath & "\" & Left(ActiveWB.Name, Len(ActiveWB.Name) - 3) & "xlsx", FileFormat:=xlOpenXMLWorkbook
            
       
    
            
            
            
 ' this is the formatting performed on each file
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
formsheetSheetName = "Formatted"
            unformsheetName = ActiveSheet.Name
            'ActiveSheet.Copy After:=ActiveSheet
            sheets.Add().Name = formsheetSheetName
            'ActiveSheet = "Original Data"
            sheets(unformsheetName).Activate
            With ActiveSheet
                Range("A1:Q2").Select
                Selection.Cut
                Range("R4").Select
                ActiveSheet.Paste
                Rows("1:3").Select
                Selection.Delete Shift:=xlUp
                Columns("R:R").Select
                Selection.Delete Shift:=xlToLeft
                ActiveSheet.Range("AH1") = "FileName"
                ActiveSheet.Range("AH2") = ActiveWB.Name
                Range("R2:AH2").Select
                Selection.Copy
                
                
                Range("R3").Select
                ActiveSheet.Paste
                
                LR = Range("A" & Rows.count).End(xlUp).Row
                Range("R2:AH3").Select
                
                Application.CutCopyMode = False
                If LR > 1 Then
                Selection.AutoFill Destination:=Range("R2:AH" & LR)
                End If
        Range("A1").Select
                Range(Selection, Selection.End(xlDown)).Select
                Range(Selection, Selection.End(xlToRight)).Select
                Selection.Copy
            End With
            sheets(formsheetSheetName).Activate
            ActiveSheet.Paste
            sheets(unformsheetName).Delete
            
  ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        'Turn off the annoying warning about data being copied onto the clipboard
        ActiveWB.Application.CutCopyMode = False
        'Save changes and close the workbok
        ActiveWB.Close SaveChanges:=True
        End If
    Next
    
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = False
    End With

0 Answers0