Newbie report questions

Posted by John on April 09, 2001 10:55 AM

Hello,

I am just beginning to learn about macros and was given an assignment at work. This site is great and I would appreciate any help with this report.

I have exported a large amount of data from a database to sheet 4 of my workbook. I have set up sheet 3 to pull data for a specific provider from sheet 4 when I enter the provider's name in a cell in sheet 3. Sheet 3 contains a formula to identify cases that we would like to review. Here is where I am stuck. I am trying to use macros to only print the cases we would like to review. My questions are:

1) I have set up a formula in column O of sheet 3. I want to copy the rows from sheet 3 where the result in column O is greater than 10,000 to the first available rows in sheet 2. Is there a macro that would do this? Will there be a problem with running and printing a report on one provider and then entering the next providers name (changing the data in sheet 3) and running the macro again?

2) I have a formatted report (final report for printing) on sheet 1. I was going to just assign cells on the final report (sheet 1) to cells from sheet 2 (ex. b2 of sheet 1 would be =Sheet2!A1). My problem is some providers will have a ten page final report while others will have just one. Is there a way to have the print area set to just print the cells with data in them?

Thanks again for any help!

Posted by John on April 09, 2001 12:46 PM

Instead of code, have you tried the filter capabilities of Excel? This will allow you to set criteria for customer and your column O and display only those rows that fullfill the criteria. Just check help for Advanced Filter.

Posted by Dave Hawley on April 10, 2001 12:39 AM

Hi Jim

Here is some code that will copy the range A1:Owhatever that is filtered down by Column O to a number greater the value in range P1.

Sub CopyByCriteria()

'Written by OzGrid Business Applications
'www.ozgrid.com
Dim ThisSheet As Worksheet
Dim CopyToSheets As Worksheet
Dim SCriteria As String

Application.ScreenUpdating = False
Set ThisSheet = ActiveSheet
Set CopyToSheets = Sheets("Sheet2")

With ThisSheet
SCriteria = .Range("P1")
'Turn off AutoFilters
.AutoFilterMode = False
'Turn on Autofilter if needed
.Rows(1).AutoFilter
.Rows(1).AutoFilter Field:=15, Criteria1:=">" & SCriteria
'Copy filtered cells to 1 row down of last cell in Column A
.Columns("A:0").SpecialCells(xlVisible).Copy _
Destination:=CopyToSheets.Range("A65536").End(xlUp).Offset(1, 0)
End With

Set ThisSheet = Nothing
Set CopyToSheets = Nothing
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Excel will only print the used range of a Worksheet by default

Dave

OzGrid Business Applications

Posted by Dave Hawley on April 10, 2001 12:41 AM

I have no idea how this got here!

Field:=15, Criteria1:=">" & SCriteria

OzGrid Business Applications

Posted by John on April 10, 2001 4:03 PM

Thanks - sorts then returns error

Field:=15, Criteria1:=">" & SCriteria

Thanks. This does the sort but returns an error. A "microsoft visual basic" box pops up with this message....Run-Time error '13': Type mismatch.

When I go to the debugger it has the following highlighted in yellow:

.Columns("A:0").SpecialCells(xlVisible).Copy _
Destination:=CopyToSheets.Range("A65536").End(xlUp).Offset(1, 0)

To the left of the word "Destination" is a yellow arrow.

Thanks again for all your help!

Posted by John on April 10, 2001 4:03 PM

Thanks - sorts then returns error

Field:=15, Criteria1:=">" & SCriteria

Thanks. This does the sort but returns an error. A "microsoft visual basic" box pops up with this message....Run-Time error '13': Type mismatch.

When I go to the debugger it has the following highlighted in yellow:

.Columns("A:0").SpecialCells(xlVisible).Copy _
Destination:=CopyToSheets.Range("A65536").End(xlUp).Offset(1, 0)

To the left of the word "Destination" is a yellow arrow.

Thanks again for all your help!

Posted by John on April 10, 2001 4:03 PM

Thanks - sorts then returns error

Field:=15, Criteria1:=">" & SCriteria

Thanks. This does the sort but returns an error. A "microsoft visual basic" box pops up with this message....Run-Time error '13': Type mismatch.

When I go to the debugger it has the following highlighted in yellow:

.Columns("A:0").SpecialCells(xlVisible).Copy _
Destination:=CopyToSheets.Range("A65536").End(xlUp).Offset(1, 0)

To the left of the word "Destination" is a yellow arrow.

Thanks again for all your help!

Posted by Dave Hawley on April 10, 2001 7:53 PM

Re: Thanks - sorts then returns error

Hi John

I see what you mean, this may have been my fault (Or it happened pasting here). The type mismatch is because the "O" is actually a zero. simply replace the 0(zero) with the letter O

DaveOzGrid Business Applications