Replace that code with this code as mention in trailmail
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Simran Singh Sent: Thursday, August 11, 2011 7:30 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Macro copy filter data in a new sheet Hi Rajan, It is not considering the country in any sheet while filtering the data. Rest of the macro is fine and is working properly as I want. On Wed, Aug 10, 2011 at 8:15 PM, Rajan_Verma <rajanverma1...@gmail.com> wrote: Please Ignore Previous Mail.. See if it helps Option Base 1 Sub GetData() Application.ScreenUpdating = False Dim EmpSh As Worksheet Dim MappingSheet As Worksheet Dim EmpName As Range Dim EmpNameRange As Range Dim EmpDesigNation As String Dim Fields As Range Dim Field As Range Dim Group() As Variant Dim i_Counter As Integer Dim Dbsheet As Worksheet Dim sumTotalHour As Integer Dim SumActualHour As Integer Dim cell As Range Dim Outputsheet As Worksheet Dim Country As String Set EmpSh = Sheets("Employee Name") Set EmpNameRange = EmpSh.Range("A2:A" & EmpSh.UsedRange.Rows.Count) Set Dbsheet = Sheets("Database") Set MappingSheet = Sheets("Mapping") Set Outputsheet = Sheets("Output") EmpSh.Activate Set Fields = MappingSheet.Range("A1").Resize(1, MappingSheet.UsedRange.Columns.Count) For Each EmpName In EmpNameRange EmpDesigNation = EmpName.Offset(0, 1).Value Country = EmpName.Offset(0, 2).Value MappingSheet.Activate For Each Field In Fields If Field.Value = EmpDesigNation Then MappingSheet.Range("A1").Activate Selection.AutoFilter ActiveSheet.Range("$A$1:$E$46").AutoFilter Field:=Field.Column, Criteria1:=EmpName ActiveSheet.Range("$A$1:$E$46").AutoFilter Field:=1, Criteria1:=Country MappingSheet.Range("B2:B" & Range("b2").End(xlDown).Row).Select Selection.SpecialCells(xlCellTypeVisible).Select Selection.Copy MappingSheet.Range("B" & MappingSheet.UsedRange.Rows.Count + 10).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Selection.RemoveDuplicates 1 ReDim Group(Selection.Cells.Count) For i_Counter = 1 To UBound(Group) Group(i_Counter) = Selection.Cells(i_Counter, 1).Value Next Selection.EntireRow.Delete Dbsheet.Activate sumTotalHour = 0 SumActualHour = 0 For i_Counter = 1 To UBound(Group) ActiveSheet.Range("$A$1:$D$46").AutoFilter Field:=2, Criteria1:=Group(i_Counter) ActiveSheet.Range("$A$1:$D$46").AutoFilter Field:=1, Criteria1:=Country Set rngtotalhour = Dbsheet.Range("C2:C" & Range("C2").End(xlDown).Row).Cells.SpecialCells(xlCellTypeVisible) Set RngactualHour = Dbsheet.Range("D2:D" & Range("D2").End(xlDown).Row).Cells.SpecialCells(xlCellTypeVisible) sumTotalHour = sumTotalHour + WorksheetFunction.Sum(rngtotalhour) SumActualHour = SumActualHour + WorksheetFunction.Sum(RngactualHour) Next Outputsheet.Activate Outputsheet.Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1).Select ActiveCell.Value = EmpName ActiveCell.Offset(0, 1).Value = sumTotalHour ActiveCell.Offset(0, 2).Value = SumActualHour End If Next Next Application.ScreenUpdating = True End Sub From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Simran Singh Sent: Wednesday, August 10, 2011 8:39 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Macro copy filter data in a new sheet Hello EXPERTS I have a workbook wherein there are thee sheets (Database, Mapping & Employee Name). In the Database sheet, I pull out the data from the tool and put that data in Database sheet. The mapping sheet consist a list of employee who are basically associated with one group or more than one groups. And in the last sheet Employee Name is the data for which i have to take out data from the Database sheet. So what I want basically is :- 1. Macro to go in the sheet Employee Name and check for the name for which it has to filter the data. 2. After taking the details from the Employee Name sheet it comes to the Mapping sheet and check for the group or groups a employee is associated with. 3. After this macro should come to the Database sheet and filter the data according to group or groups which it has got in the Mapping sheet. 4. After filter it should copy the Total Hours and Actual Hours data from there and paste in the Output sheet. Sorry I cannot give you the actual data, but I am attaching the dummy data for your reference. Thanks in advance Regards, Simran Singh -- ---------------------------------------------------------------------------- ------ Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- ---------------------------------------------------------------------------- ------ Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- ---------------------------------------------------------------------------- ------ Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
