Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy rows based on conditions and insert them into another sheet...
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy rows based on conditions and insert them into anothersheet...
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy rows based on conditions and insert them into another sheet...
goto a vba moduletype cellstouch 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 wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy rows from one Data sheet to another sheet based on cell conte | Excel Discussion (Misc queries) | |||
Need Macro- Insert rows based on dates and copy info from that row | Excel Discussion (Misc queries) | |||
Search for rows in one sheet and copy into another sheet based on customer id | Excel Worksheet Functions | |||
Help: auto-copy entire rows from 1 sheet (based on cell criteria) to another sheet. | Excel Programming | |||
copy cells in two columns if in bold to another sheet and insert rows | Excel Programming |