PDA

View Full Version : Filter/Sort looping issue



Pancakes1032
11-27-2014, 01:47 PM
I'm having an issue with this code that is suppsed to get data from another workbook and specific worksheet. It's supposed to sort and filter out data that is in Column C when I enter a specific data that I enter on my workbook. The code is working up until the sort and filter part and it seems like it's duplicating the data on my workbook instead of just filtering and pulling the data needed. Here is the code I will separate it in parts for better understanding.
Beginning of code that opens the other workbook:



Sub getdata()
Dim mastername As String
Dim count As Long
Dim match As Long
Dim repeat As Long
Dim path As String
Dim status As String
Dim name As String
Dim mpath As String
Dim cpath As String
Dim LastRow As Long
Dim LastCol As Integer
Dim mbank As String
Dim mname As String

mpath = Sheets("Master log").Cells(14, "Y").Value
mname = Sheets("Master log").Cells(15, "Y").Value
msheet = Sheets("Master log").Cells(16, "Y").Value
Sheets("MGPR1").Range("A1:AA50000").ClearContents

name = Application.ActiveWorkbook.name
cpath = Application.ActiveWorkbook.path & "\"

Windows(name).Activate

'--open Management report workbook if not already open
If CheckFileIsOpen(mname) = False Then
Workbooks.Open mpath & mname EndIf

This is the filtering part:


Windows(mname).Activate
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
If ws.Visible = True Then
End If
Next ws
Sheets(msheet).Select
'select full data
With ActiveSheet
LastRow = .Cells(.Rows.count, "A").End(xlUp).Row
End With
With ActiveSheet
' LastCol = .Cells(1, .Columns.count).End(xlToLeft).Column
LastCol = 22
End With

'--------------------- put filter

Range(Cells(1, 1), Cells(LastRow, LastCol)).Select
Selection.AutoFilter
Range("K2").Select

ActiveWorkbook.Worksheets(msheet).AutoFilter.Sort.SortFields. _
Add Key:=Range("C1:C" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets(msheet).AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Range("A1").Select
Range(Cells(1, 1), Cells(LastRow, LastCol)).Select
Selection.AutoFilter
Range("H5").Select

And this is where it pulls the filtered data to my workbook and where I am getting the error. It's saying that the paste area isn't big enough, but that is because it's duplicating the data that I am requesting:


'--------------identify bank # starting row Workbooks(mname).Sheets(msheet).Range(Cells(1, 1), Cells(1, LastCol)).Copy Destination:=Workbooks("Master_RRR.xlsm").Sheets("MGPR1").Range("A1")
I = 17
Do While Workbooks("Master_RRR.xlsm").Sheets("Master log").Cells(17, "Y").Value <> ""
mbank = Workbooks("Master_RRR.xlsm").Sheets("Master log").Cells(17, "Y").Value
match = Application.WorksheetFunction.match(mbank, Workbooks(mname).Sheets(msheet).Range("C1:C" & LastRow), 0)
repeat = Application.WorksheetFunction.CountIf(Workbooks(mname).Sheets(msheet).Range ("C1:C" & LastRow), mbank)
till = Application.WorksheetFunction.CountA(Workbooks("Master_RRR.xlsm").Sheets("MGPR1").Range("C:C"))
Workbooks(mname).Sheets(msheet).Range(Cells(match, "C"), Cells(match + repeat - 1, LastCol)).Copy Destination:=Workbooks("Master_RRR.xlsm").Sheets("MGPR1").Range("A" & till + 1)
I = I + 1
Loop