+ Reply to Thread
Results 1 to 3 of 3

Last two rows not formatted, but are copied over correctly, with no reason why

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    11-11-2007
    Posts
    263

    Last two rows not formatted, but are copied over correctly, with no reason why

    For some reason when this script exports to the PO Tracking tab the last two rows are left unformated... I don't know why as far as I can tell the code should take that into consideration. If someone can take a peek at the code below and show me where I need to adjust it be greatly appreciated.

    
    Option Explicit
    
    Sub PO_Tracking()
    
    Dim wsPOD As Worksheet
    Dim wsPOT As Worksheet
    Dim wsPOA As Worksheet
    Dim cel As Range
    Dim lastrow As Long, i As Long, Er As Long
    
    Set wsPOD = Sheets("PO Data")
    Set wsPOT = Sheets("PO Tracking")
    Set wsPOA = Sheets("PO Archive")
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
    
    With wsPOD
        'first bring columns F:G up to match their line
        For Each cel In Intersect(.UsedRange, .UsedRange.Offset(5), .Columns(6))
    
            If cel = vbNullString And cel.Offset(, -2) <> vbNullString Then
                .Range(cel.Offset(1), cel.Offset(1, 1)).Copy cel
                cel.Offset(1).EntireRow.Delete
            End If
    
        Next
    
        'now fil columns A:D to match PO Date and PO#
        For Each cel In Intersect(.UsedRange, .UsedRange.Offset(5), .Columns(1))
    
            If cel = vbNullString And cel.Offset(, 5) <> vbNullString Then
                .Range(cel.Offset(-1), cel.Offset(-1, 3)).Copy cel
            End If
        Next
    'Blow away rows that are useless
        lastrow = wsPOD.Range("A6").End(xlDown).Row
        wsPOD.Range("M5:P5").Copy wsPOD.Range("M6:P" & lastrow)
        Calculate
    
        With Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns("N"))
            .AutoFilter 1, "<>Different"
            .SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With
    
        With Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns("P"))
            .AutoFilter 1, "<>"
            .SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With
        lastrow = wsPOD.Range("A6").End(xlDown).Row
        wsPOD.UsedRange.Copy Sheets.Add.Range("A1")
    
    
    'Final Adjustments before transfering over to new sheet.
        With ActiveSheet
            .AutoFilterMode = False
            Intersect(.UsedRange, .Columns("A")).Cut .Range("Q1")
            Intersect(.UsedRange, .Columns("D")).Cut .Range("R1")
            Intersect(.UsedRange, .Columns("C")).Cut .Range("S1")
            Intersect(.UsedRange, .Columns("B")).Cut .Range("T1")
            Intersect(.UsedRange, .Columns("G")).Cut .Range("U1")
            Intersect(.UsedRange, .Columns("F")).Cut .Range("V1")
            Intersect(.UsedRange, .Range("Q:V")).Copy wsPOT.Cells(Rows.Count, "B").End(xlUp).Offset(1)
            .Delete
        End With
    
        lastrow = wsPOD.Cells(Rows.Count, "B").End(xlUp).Row
        wsPOT.Range("R1:X1").Copy
        wsPOT.Range("B3:H" & lastrow).PasteSpecial xlPasteFormats
        wsPOT.Range("N2:O2").Copy wsPOT.Range("N3:O" & lastrow)
        wsPOT.Range("P1:Q1").Copy wsPOT.Range("I3:J" & lastrow)
        wsPOT.Range("K3:K" & lastrow).Borders.Weight = xlThin
    End With
    
    
    
    Application.CutCopyMode = False
    
    End Sub
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Last two rows not formatted, but are copied over correctly, with no reason why

    hi Cyverpawz, change this line:

    lastrow = wsPOD.Cells(Rows.Count, "B").End(xlUp).Row
    to this

    lastrow = wsPOD.Cells(Rows.Count, "B").End(xlUp).Row+2

  3. #3
    Forum Contributor
    Join Date
    11-11-2007
    Posts
    263

    Re: Last two rows not formatted, but are copied over correctly, with no reason why

    Thanks, I actually figured out why it wasn't working...
    lastrow = wsPOD.Cells(Rows.Count, "B").End(xlUp).Row
    Should of been.

    lastrow = wsPOT.Cells(Rows.Count, "B").End(xlUp).Row

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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