I have this bit of code that works for copy and pasting an autofiltered range. I just wanted to see if anyone knew a more efficient way to do so as it does take quite a while to complete it the way I have it now. I looked into trying to do so with a copying into an array but did not seem to work so I'm happy to hear some of your suggestions! Also I have to be sure that the range I am copying and pasting is not the full data range and only the range after the filter was applied.
Sub FullWork()
Dim shWorkBook As Worksheet
Dim shPivotTable As Worksheet
Dim shCarrierRates As Worksheet
Dim shWholesaleLocation As Worksheet
Dim shPWLocation As Worksheet
Dim wkb As Workbook
Dim shSegmentation As Worksheet
Dim MilageMatrix As Worksheet
Dim FullYearData As Worksheet
Dim StartForm As Worksheet
Set wkb = ThisWorkbook
With wkb
Set shWorkBook = .Sheets("Workbook")
Set shPivotTable = .Sheets("PivotTable")
Set shCarrierRates = .Sheets("CarrierRates")
Set shWholesaleLocation = .Sheets("WholesalerLocation")
Set shPWLocation = .Sheets("PWLocation")
Set shSegmentation = .Sheets("Segmentation")
Set shMilageMatrix = .Sheets("MilageMatrix")
Set shFullYearData = .Sheets("FullYearData")
Set shStartForm = .Sheets("StartForm")
End With
Dim DataRange As Range
Dim PWSelection As Variant
Dim LastRow As Long
Dim LastColumn As Long
Dim LocationZip As Variant
Dim dTime As Double
dTime = Timer
Call TurnOffCalc
Set StartCell = shFullYearData.Range("A1")
'Find Last Row and Column
LastRow = shFullYearData.Cells(shFullYearData.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = shFullYearData.Cells(StartCell.Row, shFullYearData.Columns.Count).End(xlToLeft).Column
shFullYearData.Activate
shFullYearData.Range(StartCell, shFullYearData.Cells(LastRow, LastColumn)).Select
PWSelection = shStartForm.Cells(1, 1).Value
shFullYearData.Range(shFullYearData.Cells(1, 1), shFullYearData.Cells(1, LastColumn)).AutoFilter
If PWSelection <> "All" Then
shFullYearData.Range(StartCell, shFullYearData.Cells(LastRow, LastColumn)).AutoFilter Field:=12, Criteria1:=PWSelection
End If
shFullYearData.Range(StartCell, shFullYearData.Cells(LastRow, LastColumn)).AutoFilter Field:=26, Criteria1:=">=1"
shFullYearData.Range(StartCell, shFullYearData.Cells(LastRow, LastColumn)).AutoFilter Field:=3, Operator:= _
xlFilterValues, Criteria2:=Array(0, "12/31/2019")
shFullYearData.Range(StartCell, shFullYearData.Cells(LastRow, LastColumn)).Sort Key1:=Range("G1"), Order1:=xlAscending, Header:=xlYes, Key2:=Range("B1"), Header:=xlYes
''''''Line I need looked at
shFullYearData.Range(StartCell, shFullYearData.Cells(LastRow, LastColumn)).Copy Destination:=shWorkBook.Range("A1")
call TurnOnCalc
end sub