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
Bookmarks