I have this macros that is exporting spreadsheet into files and zeros out if qty is less than 3 or #N/A. Due to recent format changes on one of the spreadsheet (FL_Ihome Studio csv), the file exporting did not provide expected result. The new file format on one of the spreadsheet is attached. Initially column L is used. The new format is at two column P and Q. Meaning, column P and Q should show similar result. This changes only affected FL_Ihome Studio csv spreadsheet. Will greatly appreciate if any one of you can solved this. Thank you
Sub WICreateBooks2()
Dim ws As Worksheet, stWsName As String, stcol As String, stFPath As String, lRows As Long, r As Range, stFileType As String
stFPath = ActiveWorkbook.Path
For Each ws In ThisWorkbook.Sheets
On Error Resume Next
Select Case ws.Name
Case Is = "FL_Overstock csv"
stcol = "B"
stFileType = "CSV"
Case Is = "FL_Bonanza"
stcol = "H"
stFileType = "CSV"
Case Is = "FL_Ihome Studio csv"
stcol = "L"
stFileType = "CSV"
Case Is = "FL_Amazon txt"
stcol = "E"
stFileType = "TXT"
Case Is = "FL_Houzz csv"
stcol = "I"
stFileType = "CSV"
Case Is = "FL_Wayfair csv"
stcol = "H"
stFileType = "CSV"
End Select
If stcol <> "" Then
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ws.Copy
lRows = ActiveSheet.Range(stcol & Rows.Count).End(xlUp).Row
Set r = ActiveSheet.Range(stcol & "1:" & stcol & lRows)
ActiveSheet.Range(stcol & "1:" & stcol & lRows).AutoFilter Field:=1, Criteria1:="<3", Operator:=xlOr, Criteria2:="=#N/A"
ActiveSheet.Range(stcol & "2:" & stcol & lRows).SpecialCells(xlCellTypeVisible) = 0
ActiveSheet.Range(stcol & "1").AutoFilter
ActiveSheet.Cells.Copy
ActiveSheet.Range("A1").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
If Trim(UCase(ws.Name)) = "FL_WAYFAIR CSV" Or Trim(UCase(ws.Name)) = "FL_HOUZZ CSV" Then
ActiveSheet.Range("A:F").EntireColumn.Hidden = False
ActiveSheet.Range("A:E").EntireColumn.Delete
End If
If stFileType = "CSV" Then ActiveWorkbook.SaveAs stFPath & "" & ws.Name & ".csv", FileFormat:=xlCSV
If stFileType = "TXT" Then ActiveWorkbook.SaveAs stFPath & "" & ws.Name & ".txt", FileFormat:=xlUnicodeText
If stFileType = "CEL" Then ActiveWorkbook.SaveAs stFPath & "" & ws.Name & ".xlsx", FileFormat:=xlWorkbookDefault
Application.ActiveWorkbook.Saved = True
Application.ActiveWorkbook.Close
End If
Next ws
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Bookmarks