Hello All,
Wish you happy new year
I have a VBA code which I have gotten from this forum with the help of you guys Thank you for your support.
The code was working fine, but suddenly it started taking wrong data.
EX: I put a Auto filter (manually) and the count (from row 2) is 228,but when MACRO does the Auto filtering it takes the count (from row 2) as 229 (1 extra). I believe its Auto filter issue that takes hidden data ??(Not sure). Could you please review and help me out on this.
Sub DMANSQueries()
Dim wb As Workbook
On Error Resume Next
Set wb = Workbooks.Open(Application.GetOpenFilename("Excel Files (*.xlsx*), *.xlsx"))
On Error GoTo 0
If wb Is Nothing Then
MsgBox "File selection was cancelled." & vbCrLf & vbCrLf & "Exiting...", , "No File Selected"
Exit Sub
End If
ThisWorkbook.Worksheets("DM Ans Query ").Rows("2:" & Rows.Count).ClearContents
With wb.Worksheets(1).Cells(2, 1).Resize(wb.Worksheets(1).UsedRange.Rows.Count, 14)
.AutoFilter field:=7, Criteria1:="Answered"
.Columns(3).SpecialCells(xlCellTypeVisible).Copy
ThisWorkbook.Worksheets("DM Ans Query ").Cells(2, 1).PasteSpecial xlPasteValues
End With
With ThisWorkbook.Worksheets("DM Ans Query ").Columns(1)
.NumberFormat = "General"
.Value = .Value
End With
wb.Close False
End Sub
What I want is, once the macro does the auto filter it should copy and paste the data (as per the existing macro) and it should count from Row2. If the auto filter have zero values (nothing matching the filter criteria) then it should show a message to user as "No data"
EDIT-1:
1:I have 2 Workbook.
A. Query Backlog Report - Open and Answered Queries (I want to put filter for Column7 and 11 (see the VB codes in the Clean Patient Tracker_24Dec2020 already there )
B:Clean Patient Tracker_24Dec2020: It has multiple sheets . However I want to Copy the filtered data (for Criterias in the VBA) from Query Backlog Report. Once Copied, I want to paste the data in this workbook and in the sheet named "Open query" "DM ANS query" and so on.
2: I will keep the "Cleanpatienttracker" open. And I will enable the macro button in this sheet, hence it should ask me to choose the file from a folder (as the name could be different sometime)
In the Query Backlog Report, I want filter for
.AutoFilter field:=7, Criteria1:="Opened"
, then copy and paste the data in the respective sheet of "Clean Patient Tracker". Now here I am facing the issue. As it copy 1 extra row which is not correct.
Similarly I want to put filter for below and copy paste data.
.AutoFilter field:=7, Criteria1:="Answered"
.AutoFilter field:=11, Criteria1:="Monitor"
.AutoFilter field:=7, Criteria1:="Answered"
.AutoFilter field:=11, Criteria1:="GPS"
.AutoFilter field:=7, Criteria1:="Answered"
.AutoFilter field:=11, Criteria1:=Array("Data Manager", "AutoQuery RG"), Operator:=xlFilterValues
I hope, am not confusing with lot of things.
Thank you for looking into it.
Bookmarks