Macro Code Help

[zach9208]

I am trying to set my macro file to remove formats from columns that have spaces in them (See CRMCleanup) I tried just adding a Call CRMCleanup piece of code to my UpdateHeadersCRM code but I keep getting an error saying the sheet is still protected. I need to cleanup or merge my code for the CRMCleanup code into the UpdateHeadersCRM code. Any ideas?

Sub DoWork(wb As Workbook)    UpdateHeadersAspirational wb.Sheets("Apsirational") 'The template files have the tab "Aspirational" mispelled as "Apsirational"
    UpdateHeadersCRM wb.Sheets("CRM")


End Sub

Sub ProcessFiles()Dim wb As Workbook
Dim Filename As String, Pathname As String


    Pathname = "C:\Users\c755748\Desktop\TEST2\" 'This is the file path the templates need saved to in order for the macro to clean the file formats up in prep for the database
    Filename = Dir(Pathname & "*.xlsx")


    Do While Filename <> ""
        Set wb = Workbooks.Open(Pathname & Filename)
        DoWork wb
        wb.Close SaveChanges:=True
        Filename = Dir()
    Loop


End Sub

Sub UpdateHeadersCRM(ws As Worksheet)

    With ws
        .Application.ScreenUpdating = False
        
        .Unprotect 'Unprotects the workbook
        .Range("A1:W3").Delete Shift:=xlUp 'Need to adjust range if a new column is added or removed
        
        'Re-names row 1 with the column names for the database. Update these fields if a new column is added or removed.
        .Range("A1").Value = "WGTD_SLS_CAL_AMT"
        .Range("B1").Value = "EST_RVNU_AMT"
        .Range("C1").Value = "EST_BPS_AMT"
        .Range("D1").Value = "WT_PRC"
        .Range("E1").Value = "AVG_BPS_AMT"
        .Range("F1").Value = "EST_FDNG_QTR_NM"
        .Range("G1").Value = "OPTY_NMBR"
        .Range("H1").Value = "CO_NM"
        .Range("I1").Value = "CNSLT_NM"
        .Range("J1").Value = "PRMY_PRDCT_NM"
        .Range("K1").Value = "BTQ_PRMY_PRDCT_TYP_NM"
        .Range("L1").Value = "INV_VHCL_2_NM"
        .Range("M1").Value = "PLNE_STP_NM"
        .Range("N1").Value = "RNKG_TYP_NM"
        .Range("O1").Value = "CRNCY_NM"
        .Range("P1").Value = "EST_MNDT_AMT"
        .Range("Q1").Value = "EST_MNDT_USD_AMT"
        .Range("R1").Value = "EST_DCSN_DT"
        .Range("S1").Value = "TM_MBR_NM"
        .Range("T1").Value = "RGN_4_NM"
        .Range("U1").Value = "OPTY_TYP_NM"
        .Range("V1").Value = "MOD_DT"
        .Range("W1").Value = "STAT_UPDT_TXT"


      Call CRMCleanup
    End With
    
End Sub

Sub CRMCleanup() 'Need to adjust range if a new column is added or removed. This is needed in order to cleanup excess blank columns    
    Sheets("CRM").Unprotect
    Columns("X:AWY").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Clear
    
End Sub

 

[zach9208]

It takes me to J31. Let me explain how this file is formatted. Columns A-F have formatting applied. In this example file, this color formatting is from A1 to D103. column J is a column that will always be a good indicator if a row has data since data will always be populated in this column if their is truly a record.

Your macro leaves behind this formatting in columns A thru D. It is my theory that my access macro is bringing in rows that have any values or formatting. I think this since I can manually select the range A32:W1048576 and use the "Clear All" function in excel and then run my access macro and there are no extra lines.

If you select J1 & do Ctrl + down arrow does that take you to the last row of data?

 

[Fluff]

Ok, try this instead

Sub UpdateHeadersCRM2(Ws As Worksheet)


    Dim Hdrs() As Variant
    Dim NxtRw As Long
    Dim Cnt As Long
    
    Hdrs = Array("WGTD_SLS_CAL_AMT", "EST_RVNU_AMT", "EST_BPS_AMT", "WT_PRC" _
            , "AVG_BPS_AMT", "EST_FDNG_QTR_NM", "OPTY_NMBR", "CO_NM", "CNSLT_NM" _
            , "PRMY_PRDCT_NM", "BTQ_PRMY_PRDCT_TYP_NM", "INV_VHCL_2_NM", "PLNE_STP_NM" _
            , "RNKG_TYP_NM", "CRNCY_NM", "EST_MNDT_AMT", "EST_MNDT_USD_AMT" _
            , "EST_DCSN_DT", "TM_MBR_NM", "RGN_4_NM", "OPTY_TYP_NM", "MOD_DT", "STAT_UPDT_TXT")
    
    With Ws
        .Application.ScreenUpdating = False
        
        .Unprotect 'Unprotects the workbook


        .Range("A1").Resize(3, CInt(UBound(Hdrs)) + 1).Delete Shift:=xlUp
        
        For Cnt = 0 To UBound(Hdrs)
            .Cells(1, Cnt + 1) = Hdrs(Cnt)
         Next Cnt

        .Columns(UBound(Hdrs) + 2).Resize(, 16383 - UBound(Hdrs)).Clear
        [COLOR=#0000ff]NxtRw = .Range("J1").End(xlDown).Row[/COLOR]
        .Range("A" & NxtRw, "A" & Rows.Count).Clear
    End With
    
End Sub

 

[zach9208]

Just tested this new code out and here was the result. It looks like the macro cleared out the formatting starting 1 row early and only cleared this from column A (data is populating in column J thru row 28). Columns C-F still had the formatting showing after line 28.

 

[Fluff]

Ok, make these changes

        NxtRw = .Range("J1").End(xlDown)[COLOR=#0000ff].offset(1)[/COLOR].Row
        .Range("A" & NxtRw, "A" & Rows.Count)[COLOR=#0000ff].EntireRow[/COLOR].Clear
    End With

 

[zach9208]

OMG, It appears to be working!!!! Thank you so much for your patience, Fluff! You're a rockstar!

Ok, make these changes

        NxtRw = .Range("J1").End(xlDown)[COLOR=#0000ff].offset(1)[/COLOR].Row
        .Range("A" & NxtRw, "A" & Rows.Count)[COLOR=#0000ff].EntireRow[/COLOR].Clear
    End With

 

[Fluff]

Glad to help & thanks for the feedback.