copy records, apply formating, delete rows

[jarich]

Hello,

I have a new hosted application that only outputs data as raw data in excel. I am a sql developer not an excel developer and am having a hard time writing the macros. (I have been searching this forum and other internet sites for 2 weeks and read the first 200 pages of excel power programming w/ VBA and nothing is clicking yet.)

Example of data on Sheet1:
Facility Supervisor Employee Date Type
Orlando Jim Super Joe Emp 6/17/2011 Incident Only-Injury
Tampa Jim Super2 Joe Emp2 7/12/2011 Recordable-Injury
Miami 7/13/2011

Sheet 2:
Facility Supervisor Employee Date Type
='Sheet1'!A2 ='Sheet1'!B2 ='Sheet1'!C2 ='Sheet1'!D2 ='Sheet1'!E2
='Sheet1'!A3 ='Sheet1'!B3 ='Sheet1'!C3 ='Sheet1'!D3 ='Sheet1'!E3
='Sheet1'!A4 ='Sheet1'!B4 ='Sheet1'!C4 ='Sheet1'!D4 ='Sheet1'!E4

I have some conditional formatting applied to columns to highlight certain cells.

Here are the issues I am encountering:
1. The number of rows on sheet 1 changes on a daily basis. Currently, I have the formulas pasted down a few hundred rows to try and capture all the data. It makes the report very ugly having all the zeros at the bottom. How can I autofill the formula on sheet 2 to remove all the zeros at the bottom?

2. I only want to display rows from the current month on sheet 2.

3. I only want to display a row on sheet 2 if the employee column has a value on sheet 1.


Please let me know the best way to accomplish this task and recommend a different book than the one I am using. (It appears to be geared toward creating applications in excel whereas I need to be creating some reports on data that already exists.)

 

[AlphaFrog]

• Select all your data on Sheet1
• Select from the menu Data\ Filter\ Autofilter
• This will put a drop-down list in the top cell in each column
• Select the top cell in the Employee column
• Select from that drop-down list ; Non Blanks
• Select the top cell in the Date column
• Select Custom in the drop-down list
• On the Custom Autofilter dialog, select the date range you want to filter on e.g.; the current month
• Select all the filtered rows and copy\paste them to Sheet2

You can record these steps as a macro. It will have to be modified to make it run seamless each month, but it's a good place to start. If you post that code here in the forum, I'm sure you'll get help to clean it up.

 

[jarich]

That works very well. I do have an issue with setting the Date Filter. MS says the xlThisMonth filter was added in version 2007. I have users with older versions.

here is the line of code to change for the date filter:
ActiveSheet.ListObjects("Table24").Range.AutoFilter Field:=4, Criteria1:= _
"=7*", Operator:=xlAnd

I need the criteria to somehow say
Criteria1 = "=(text(today(),mm)*"

I need to find the month based on Today and use that in the auto filter. If that makes sense at all.

Regards,
J

 

[AlphaFrog]

Autofilter on the current month...

ActiveSheet.ListObjects("Table24").Range.AutoFilter _
                                         Field:=4, _
                                         Criteria1:=">=" & DateSerial(Year(Date), Month(Date), 1), _
                                         Operator:=xlAnd, _
                                         Criteria2:="<" & DateSerial(Year(Date), Month(Date) + 1, 1)