Excel VBA autofilter, copy and paste to named sheet

Question

I have written a bit of code to autofilter values in filter_range based on the filter_val set from another sheet. The |Result I want is a tab named after each filter_val in the cust_DMA with the values filtered for this value.

Whilst looping through the list of 'filter_val' I am unhappy with this section of the code

' filter_val = .Cells(i, 1).Value
 filter_range.AutoFilter Field:=8, Criteria1:=filter_val ''''autofilter field should be 8 as h is column 8
Billed_sheet.Range("a:v").copy
cust_DMA.Sheets.Add.Name = filter_val
ActiveSheet.Paste ''sometimes breaks here;

As although it produces the results I want, I do not like using Activesheet.Paste and occasionally this line of the code fails.

Can anyone recommend a better solution for this? I have tried setting a range based on the filtered cells, but when I use this range to add values to the Cust_DMA sheet, they whole range is copied, rather than just the filtered values.

Code below,

Cheers

Sub filter_DMA_debugged_23_03_15(filter_val As String, filter_range As Range, Lrow As Long, LBox As Object, List_row As Long, DMA_sht As Worksheet, DMA_wb As Workbook, cust_DMA As Workbook, FPath As String, FName As String, list_val As String, i As Integer) 'working
'''works in stepthrough/runtime but the activesheet paste is a bit volatile - find a solution
Application.ScreenUpdating = False

    Set DMA_wb = Workbooks("DMA_metered_tool_v11_SICTEST.xlsm")
    Set DMA_sht = DMA_wb.Worksheets("DMA list")
    FPath = DMA_sht.Range("c8").Text
    FName = ("DMA_customers_SICTEST.xlsx")
    Workbooks.Add.SaveAs FileName:=FPath & "\" & FName ''''
    Set cust_DMA = Workbooks("DMA_customers_SICTEST.xlsx")
    Set Billed_sheet = Workbooks("Billed_customers_SICTEST.xls").Sheets("Non Household Metered Users")

            With Billed_sheet

                .AutoFilterMode = False ' clear any existing filter to get accurate row count
                Lrow = .Range("a" & .Rows.count).End(xlUp).row
                Set filter_range = .Range("a1:v" & Lrow) '''try changing to a:v to avoid missing anything

            End With

                With DMA_sht

                    List_row = .Range("a" & .Rows.count).End(xlUp).row

                        For i = 2 To List_row '- 1 removed '-1 as it was missing the last value, starting at 2 already accounts for list_row having more items in it than needed.

                            filter_val = .Cells(i, 1).Value
                            filter_range.AutoFilter Field:=8, Criteria1:=filter_val ''''autofilter field should be 8 as h is column 8
                            Billed_sheet.Range("a:v").copy
                            cust_DMA.Sheets.Add.Name = filter_val
                            ActiveSheet.Paste ''sometimes breaks here

                        Next i

                End With

Application.ScreenUpdating = True

End Sub

Answer 1

Thanks to the information I was directed to here, I have a working version below, comments in the code. I am sure it could be made more elegant if anyone would like to suggest anything. Thanks for the input.

Dim CopyFrom As Range
Application.ScreenUpdating = False
Set DMA_wb = Workbooks("DMA_metered_tool_v12_SICTEST.xlsm")
Set DMA_sht = DMA_wb.Worksheets("DMA list")
FPath = DMA_sht.Range("c8").Text
FName = ("DMA_customers_SICTEST.xlsx")
Workbooks.Add.SaveAs FileName:=FPath & "\" & FName
Set cust_DMA = Workbooks("DMA_customers_SICTEST.xlsx")
Set Billed_sheet = Workbooks("Billed_customers_SICTEST.xls").Sheets("Non Household Metered Users")

        With Billed_sheet

            .AutoFilterMode = False ' clear any existing filter to get accurate row count
            Lrow = .Range("a" & .Rows.count).End(xlUp).row
            Set filter_range = .Range("a1:v" & Lrow) '''try changing to a:v to avoid missing anything

        End With

            With DMA_sht

                List_row = .Range("a" & .Rows.count).End(xlUp).row

                    For i = 2 To List_row '- c1 removed '-1 as it was missing the last value, starting at 2 already accounts for list_row having more items in it than needed.

                        filter_val = .Cells(i, 1).Value
                        filter_range.AutoFilter Field:=8, Criteria1:=filter_val ''''autofilter field should be 8 as h is column 8
                        cust_DMA.Sheets.Add.Name = filter_val
                        Set CopyFrom = Billed_sheet.Range("a1:v" & Lrow).SpecialCells(xlCellTypeVisible) ' set range as filtered values only
                        CopyFrom.copy 'copy filtered values
                        .AutoFilterMode = False 'remove filters
                        cust_DMA.Sheets(filter_val).Range("a1").PasteSpecial xlPasteValues
                    Next i

            Application.ScreenUpdating = True
            End With

Answer 2

I've done something like this before, please test the following and see if it works for your needs.

' filter_val = .Cells(i, 1).Value
filter_range.AutoFilter Field:=8, Criteria1:=filter_val 
cust_DMA.Sheets.Add.Name = filter_val
'ActiveSheet.Paste ''sometimes breaks here;
With ActiveSheet.AutoFilter.Range. 
    .Copy  Sheets(filter_val).Range("A1") 'may need to change target
    .Clear 
End With