Select dynamic range with columns being skipped

[Kemidan2014]

I am wanting to add on additional columns of data to copy and paste from a macro that currently is in use and works fine

 .Range("A2:V" & lastrowupdate).SpecialCells(xlCellTypeVisible).Copy

the above range being selected currently copies columns A to V from 2 to last row with data (it will be dynamic) i need to INCLUDE columns AL to AV (column W to AK being ignored)

I already tried

 .Range("A2:V,AL2:AV" & lastrowupdate).SpecialCells(xlCellTypeVisible).Copy

but that threw up an error.

below is the corresponding Paste code

With desWS
            .Cells(.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
            .Columns.AutoFit

any suggestions?

 

[mumps]

Try:

Intersect(ActiveSheet.AutoFilter.Range.Offset(1), Range("A:V,AL:AV")).Copy

 

[Kemidan2014]

I gave it a shot and got the same error as before. i have posted the whole code you can see where I left your code line in with ' to disable it for now.

Sub a3Audit()
'Make Declarations and define
 Worksheets("Complaints").Unprotect Password:="Secret"
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet
    Set srcWS = Sheets("Complaints")
    Set desWS = Sheets("A3 Audit")
    'LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    'lastrowlookup = ActiveSheet.Range("A1").End(xlDown).Row
    lastrowupdate = srcWS.Cells(Rows.Count, "A").End(xlUp).Row
    'Clear old data
    desWS.UsedRange.Offset(1).ClearContents
    'Insert a filtration formula in the first unused column
    With srcWS
        .Range("AX2:AX" & lastrowupdate).Formula = "=IF(G2=""Closed - LTCM Effective"",IF(AND((TODAY()-90)>=M2,ISBLANK(AW2)),""true"",""false""),""false"")"
        Rows("1:1").Select
        Selection.AutoFilter
        Range("AX1").Select
        srcWS.Range("$A:$AX").AutoFilter Field:=50, Criteria1:="true"
        
        'Copy and paste the data
        '.Cells(1).CurrentRegion.AutoFilter 46, "true"
        .Range("A2:V" & lastrowupdate).SpecialCells(xlCellTypeVisible).Copy
        'Intersect(srcWS.AutoFilter.Range.Offset(1), Range("A:V,AL:AV")).Copy
        With desWS
            .Cells(.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
            .Columns.AutoFit
        End With
        .Range("A1").AutoFilter
        .Columns("AX").Delete
    End With
    'Re-protect the workbook and save
    Application.ScreenUpdating = True
    Worksheets("Complaints").Protect Password:="Secret"
    ThisWorkbook.Save
End Sub

 

[mumps]

Try:

Intersect(.AutoFilter.Range.Offset(1), .Range("A:V,AL:AV")).Copy

 

[Kemidan2014]

That worked! I googled intersect function and found that it finds or uses the intersection row and column. im trying to work in my head logically how that line syntax yields the results.

 

[mumps]

Glad it worked out.