Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 65
  • Last Modified:

Macro to build error report in Excel from data in the same workbook

I want a separate worksheet in Excel to build an error report from another worksheet in the same workbook.  The data would be a list of employee positions such as full time, part time, temps, seasonal etc...and ONLY full time positions should have insurance costs listed in a particular field.  I want the report to search the data and if it finds insurance expense in that field for any other position than a full time position, copy the entire row of information and add it to the error list.   The problem I am having is having the report build the list so that there are no empty rows on the list.  I don't know a simple way with a formula to do this and assume I need a macro to build it so that as it finds the data, it puts it on the next available line on the report.   Once I have a macro to do it, I'd like to set up a button and assign the macro to it so I can run it when I want.  I have attached a very simple sample.
EE-Example-Error-Report.xlsx
0
Johnette Connelley
Asked:
Johnette Connelley
1 Solution
 
Rgonzo1971Commented:
Hi,

pls try
Sub macro()
Sheets("Data File").Activate
For Each c In Range(Range("A3"), Range("A" & Rows.Count).End(xlUp))
    If c <> "FULL TIME" Then
        c.Resize(, 2).Copy Destination:=Sheets("Error Report").Range("A" & Rows.Count).End(xlUp).Offset(1)
    End If
Next
End Sub

Open in new window

Regards
1
 
Johnette ConnelleyBudget Analyst / ProjectsAuthor Commented:
Worked beautifully!  I am new to Macros and this is excellent. Thanks!
0
 
Roy CoxGroup Finance ManagerCommented:
Duplicating data to another sheet is not the best way to work with data. I would simply use Conditional Formatting to highlight the cells, in the newer versions of Excel you can always filter by colour and print the list if required.
EE-Example-Error-Report.xlsx
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now