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
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