I'm trying to make a "function" which accepts a source worksheet, target worksheet, and a unique "autofilter" value. The goal is to use the source worksheet and different target worksheets with different autofilter values to cut and paste the data seperated by the "unique" value. I can't get the function to work properly, it seems to error out saying "Run-time error '1004'" (Microsoft Office Excel cannot create or use the data range reference because it is too complex." The sheet has more than 65,000 lines (probably in the range of 68,000) and it will dynamically increase in the future as it is pulled from another data source. How can I make the data copy after column "G" is autofiltered? Column A:G from source sheet will go to A3,B3,C3,...G3 for only the visible data after the autofilter? I am using Excel 2007.

Function DivideAndConquer(sourcews As String, targetws As String, secid As Integer) As Boolean 
    Dim Rng As Range 
    Dim firstrow, Lastrow As Long 
    Dim ws As Worksheet 
     
    Set ws = Sheets(sourcews) 
     
     'clear everything before copying in sheet Results
    Sheets(targetws).Range("A3:G67000").ClearContents 
     
    With ws 
        Set Rng = .Range("A1").CurrentRegion 
        Rng.MergeCells = False 
         
        ActiveSheet.Range("$A$1:$Z$1").AutoFilter Field:=7, Criteria1:=secid 
        Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Cut _ 
        Sheets(targetws).Range("A3") 
        ActiveSheet.Paste 
         
         
        ActiveSheet.Range("$A$1:$Z$1").AutoFilter Field:=7, Criteria1:=secid 
        Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Cut _ 
        Sheets(targetws).Select 
        ActiveSheet.Range("B3").Select 
        ActiveSheet.Paste 
         
         
         'copy Day
        Sheets(sourcews).Select 
        .AutoFilter.Range.Offset(1, 0).Resize(.AutoFilter.Range.Rows.Count - 1) _ 
        .SpecialCells (xlCellTypeVisible) 
        Selection.Cut 
        Sheets(targetws).Select 
        ActiveSheet.Range("A3").Select 
        ActiveSheet.Paste 
         
         'copy Hour
        Sheets(sourcews).Select 
        Set Rng = .Range("A1").CurrentRegion 
        Rng.MergeCells = False 
        Rng.AutoFilter Field:=7, Criteria1:=secid 
        Rng.Resize(Rng.Rows.Count - 1, 1).Offset(1, 1).Select 
        Selection.Cut 
        Sheets(targetws).Select 
        ActiveSheet.Range("B3").Select 
        ActiveSheet.Paste 
         

         'copy Market
        Sheets(sourcews).Select 
        Set Rng = .Range("A1").CurrentRegion 
        Rng.MergeCells = False 
        Rng.AutoFilter Field:=7, Criteria1:=secid 
        Rng.Resize(Rng.Rows.Count - 1, 1).Offset(1, 2).Select 
        Selection.Cut 
        Sheets(targetws).Select 
        ActiveSheet.Range("C3").Select 
        ActiveSheet.Paste 
          
         'copy Market_Desc
        Sheets(sourcews).Select 
        Set Rng = .Range("A1").CurrentRegion 
        Rng.MergeCells = False 
        Rng.AutoFilter Field:=7, Criteria1:=secid 
        Rng.Resize(Rng.Rows.Count - 1, 1).Offset(1, 3).Select 
        Selection.Cut 
        Sheets(targetws).Select 
        ActiveSheet.Range("D3").Select 
        ActiveSheet.Paste 
         
         'copy SName
        Sheets(sourcews).Select 
        Set Rng = .Range("A1").CurrentRegion 
        Rng.MergeCells = False 
        Rng.AutoFilter Field:=7, Criteria1:=secid 
        Rng.Resize(Rng.Rows.Count - 1, 1).Offset(1, 4).Select 
        Selection.Cut 
        Sheets(targetws).Select 
        ActiveSheet.Range("E3").Select 
        ActiveSheet.Paste 
         
         'copy eNB
        Sheets(sourcews).Select 
        Set Rng = .Range("A1").CurrentRegion 
        Rng.MergeCells = False 
        Rng.AutoFilter Field:=7, Criteria1:=secid 
        Rng.Resize(Rng.Rows.Count - 1, 1).Offset(1, 5).Select 
        Selection.Cut 
        Sheets(targetws).Select 
        ActiveSheet.Range("F3").Select 
        ActiveSheet.Paste 
         
         'copy eTC
        Sheets(sourcews).Select 
        Set Rng = .Range("A1").CurrentRegion 
        Rng.MergeCells = False 
        Rng.AutoFilter Field:=7, Criteria1:=secid 
        Rng.Resize(Rng.Rows.Count - 1, 1).Offset(1, 6).Select 
        Selection.Cut 
        Sheets(targetws).Select 
        ActiveSheet.Range("G3").Select 
        ActiveSheet.Paste 
         
         'copy RBG1
        Sheets(sourcews).Select 
        Rng.Resize(Rng.Rows.Count - 1, 1).Offset(1, 7).Select 
        Selection.Cut 
        Sheets(targetws).Select 
        ActiveSheet.Range("I3").Select 
        ActiveSheet.Paste 
         
         'copy RBG2
        Sheets(sourcews).Select 
        Rng.Resize(Rng.Rows.Count - 1, 1).Offset(1, 8).Select 
        Selection.Cut 
        Sheets(targetws).Select 
        ActiveSheet.Range("M3").Select 
        ActiveSheet.Paste 
           
         'copy RBG3
        Sheets(sourcews).Select 
        Rng.Resize(Rng.Rows.Count - 1, 1).Offset(1, 9).Select 
        Selection.Cut 
        Sheets(targetws).Select 
        ActiveSheet.Range("Q3").Select 
        ActiveSheet.Paste 
         
         'copy RBG4
        Sheets(sourcews).Select 
        Rng.Resize(Rng.Rows.Count - 1, 1).Offset(1, 10).Select 
        Selection.Cut 
        Sheets(targetws).Select 
        ActiveSheet.Range("U3").Select 
        ActiveSheet.Paste 
         
         'copy RBG5
        Sheets(sourcews).Select 
        Rng.Resize(Rng.Rows.Count - 1, 1).Offset(1, 11).Select 
        Selection.Cut 
        Sheets(targetws).Select 
        ActiveSheet.Range("Y3").Select 
        ActiveSheet.Paste 
         
         'copy RBG6
        Sheets(sourcews).Select 
        Rng.Resize(Rng.Rows.Count - 1, 1).Offset(1, 12).Select 
        Selection.Cut 
        Sheets(targetws).Select 
        ActiveSheet.Range("AC3").Select 
        ActiveSheet.Paste 
         
         'copy RBG7
        Sheets(sourcews).Select 
        Rng.Resize(Rng.Rows.Count - 1, 1).Offset(1, 13).Select 
        Selection.Cut 
        Sheets(targetws).Select 
        ActiveSheet.Range("AG3").Select 
        ActiveSheet.Paste 
         
         'copy RBG8
        Sheets(sourcews).Select 
        Rng.Resize(Rng.Rows.Count - 1, 1).Offset(1, 14).Select 
        Selection.Cut 
        Sheets(targetws).Select 
        ActiveSheet.Range("AK3").Select 
        ActiveSheet.Paste 
         
         'copy RBG9
        Sheets(sourcews).Select 
        Rng.Resize(Rng.Rows.Count - 1, 1).Offset(1, 15).Select 
        Selection.Cut 
        Sheets(targetws).Select 
        ActiveSheet.Range("AO3").Select 
        ActiveSheet.Paste 
  
         'copy RBG10
        Sheets(sourcews).Select 
        Rng.Resize(Rng.Rows.Count - 1, 1).Offset(1, 16).Select 
        Selection.Cut 
        Sheets(targetws).Select 
        ActiveSheet.Range("AS3").Select 
        ActiveSheet.Paste 
         
         'copy RBG11
        Sheets(sourcews).Select 
        Rng.Resize(Rng.Rows.Count - 1, 1).Offset(1, 17).Select 
        Selection.Cut 
        Sheets(targetws).Select 
        ActiveSheet.Range("AW3").Select 
        ActiveSheet.Paste  
        
         'copy RBG12
        Sheets(sourcews).Select 
        Rng.Resize(Rng.Rows.Count - 1, 1).Offset(1, 18).Select 
        Selection.Cut 
        Sheets(targetws).Select 
        ActiveSheet.Range("BA3").Select 
        ActiveSheet.Paste 
    
         'copy RBG13
        Sheets(sourcews).Select 
        Rng.Resize(Rng.Rows.Count - 1, 1).Offset(1, 19).Select 
        Selection.Cut 
        Sheets(targetws).Select 
        ActiveSheet.Range("BE3").Select 
        ActiveSheet.Paste 
         
    End With 
End Function