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:
- Opens up each csv file in "File_Path" (Do note I changed the name for confidentiality).
- Reformats the contents in the workbook
- 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