+ Reply to Thread
Results 1 to 1 of 1

Modify Macros due to changes in spreadsheet format

  1. #1
    Forum Contributor
    Join Date
    03-22-2016
    Location
    United States
    MS-Off Ver
    2013
    Posts
    104

    Modify Macros due to changes in spreadsheet format

    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
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macros: Modify extracted data by removing columns and adding fields.
    By olga6542 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-16-2018, 10:48 PM
  2. Modify/delete cell styles using macros
    By denmark18 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-02-2017, 08:47 AM
  3. [SOLVED] Protect spreadsheet with macros but allow people to only modify pivot table.
    By mahbub182004 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-08-2015, 05:18 PM
  4. How can I modify spreadsheet cells from within a function or subroutine?
    By RogeratCCCC in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 04-14-2012, 10:58 AM
  5. Modify Pivot Table Layout & Field Settings with Macros
    By G0liath01 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-16-2009, 08:32 PM
  6. Modify Imported Data in Spreadsheet
    By kbahfner in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-12-2007, 04:18 PM
  7. Replies: 1
    Last Post: 05-16-2005, 05:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1