copy rows based on conditions and insert them into another sheet...

[bramnizzle]

Here's a new one...

I have an accounting workbook with some hidden sheets based on months.
All month sheets have a Fixed heading with some accounting information
below it and further down the sheet, a Unpaid From Last Month heading
with some information. What I need is that if there are lines under
the Fixed heading that have negative balances, I want that entire row
inserted under the Unpaid From Last Month.

For example...

within the "November" worksheet...

A B C
D ... L
9 Fixed
10 Bill Frequency
Amount Yet To Pay
11 ( blank
cells )
12 Account
1
(4100.00)
13 Account
2
3300.00
14 Account
3
(4333.00)
15
16
17
18
19
20
21
22
23 Unpaid From Last Month
24 ( blank
cells )
25



On "December"...

Rows 12 and 14 should be copied from "November" and placed in
"December" under Unpaid From Last Month.

The problem I'm having is that for "December" 'Unpaid From Last Month'
might start on row 21 or 20. And for any given month, there might be
a lot more accounts than accoutn 1, 2, or 3.

So basically, the user clicks a button to create a new month sheet
will be placed at the very end. I need the macro to access the
previous sheet (which should be the previous month), search the rows
only under the "Fixed" heading (which could be any number of rows) and
find a negative number in column L. If there is a negative number,
copy that entire row and place it on the new sheet under the heading
"Unpaid From Last Month".

I'm envisioning loops, but I'm still very new to Excel VBA. Any help
would be greatly appreciated.

 

[Don Guillett]

Look in the vba help index for FINDNEXT. There is a good example. After the
find you will want to copy to the next available row in the destination
sheet. You can use something like:

lastrow=cells(rows.count, "a").end(xlup).row+1
c.entirerow.copy cells(lastrow,"a")

 

[bramnizzle]

in your line of code
lastrow=cells(rows.count, "a").end(xlup).row+1

what does "a" represent? The column A? or some text I'm supposed to
enter?

 

[Don Guillett]

goto a vba module>type cells>touch f1 key

Cells Property
See AlsoApplies ToExampleSpecifics
Cells Property as it applies to the Application object.

Returns a Range object that represents all the cells on the active
worksheet. If the active document isn't a worksheet, this property fails.
Read-only.

expression.Cells

expression Required. An expression that returns an Application object.

Cells Property as it applies to the Range object.

Returns a Range object that represents the cells in the specified range.
Read-only.

expression.Cells

expression Required. An expression that returns a Range object.

Cells Property as it applies to the Worksheet object.

Returns a Range object that represents all the cells on the worksheet (not
just the cells that are currently in use). Read-only.

expression.Cells

expression Required. An expression that returns a Worksheet object.

Remarks
Because the Item property is the default property for the Range object, you
can specify the row and column index immediately after the Cells keyword.
For more information, see the Item property and the examples for this topic.

Using this property without an object qualifier returns a Range object that
represents all the cells on the active worksheet.

Example
This example sets the font size for cell C5 on Sheet1 to 14 points.

Worksheets("Sheet1").Cells(5, 3).Font.Size = 14
This example clears the formula in cell one on Sheet1.

Worksheets("Sheet1").Cells(1).ClearContents
-- Don GuillettMicrosoft MVP ExcelSalesAid