Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total costing
Hello
I am working on a payment file The first sheet called costing contains a list of all items that we might purchase A5 : List of Items B5: month 31/12/2008 C5: 1/1/2009 etc each month i entered the item and the total price of what we bought each month has his own sheet Sheet 31-12-2008 " B10=List of Items C10= Total amount Sheet 1-1-2009 " B10=List of Items C10= Total amount What i need is to have the total of each item for each month N.B : each month i copy from the first sheet only the item that i used not the same list in each sheet Waiting for your reply Thank you in advance |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total costing
Hi Tia,
If I didn't misunderstood you have the same problem as me in the past and nice people from the comunity helped me with the code as per below, I this was helpful please say yes, thanks 'Add a worksheet with the name "RDBMergeSheet" Set DestSh = ActiveWorkbook.Worksheets.Add DestSh.Name = "RDBMergeSheet" 'Fill in the start row StartRow = 5 'loop through all worksheets and copy the data to the DestSh For Each sh In ActiveWorkbook.Worksheets If sh.Name < DestSh.Name And sh.Visible = True Then 'Find the last row with data on the DestSh and sh Last = LastRow(DestSh) shLast = LastRow(sh) 'If sh is not empty and if the last row = StartRow copy the CopyRng If shLast 0 And shLast = StartRow Then 'Set the range that you want to copy Set CopyRng = sh.Range(sh.Range("A" & StartRow), sh.Range("IV" & shLast)) 'Test if there enough rows in the DestSh to copy all the data If Last + CopyRng.Rows.Count DestSh.Rows.Count Then MsgBox "There are not enough rows in the Destsh" GoTo ExitTheSub End If 'This example copies values/formats, if you only want to copy the 'values or want to copy everything look below example 1 on this page CopyRng.Copy With DestSh.Cells(Last + 1, "A") .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False End With 'Optional: This will copy the sheet name in the A column 'DestSh.Cells(Last + 1, "P").Resize(CopyRng.Rows.Count).Value = sh.Name End If End If Next ExitTheSub: Application.Goto DestSh.Cells(1) 'AutoFit the column width in the DestSh sheet DestSh.Columns.AutoFit With Application .ScreenUpdating = True .EnableEvents = True End With Sheets("Menu").Visible = True Sheets("Menu").Select ActiveCell.Range("A1").Select End Sub "Tia" wrote: Hello I am working on a payment file The first sheet called costing contains a list of all items that we might purchase A5 : List of Items B5: month 31/12/2008 C5: 1/1/2009 etc each month i entered the item and the total price of what we bought each month has his own sheet Sheet 31-12-2008 " B10=List of Items C10= Total amount Sheet 1-1-2009 " B10=List of Items C10= Total amount What i need is to have the total of each item for each month N.B : each month i copy from the first sheet only the item that i used not the same list in each sheet Waiting for your reply Thank you in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
costing values | Excel Discussion (Misc queries) | |||
costing | Excel Discussion (Misc queries) | |||
Product costing | Excel Worksheet Functions | |||
Product costing | Excel Worksheet Functions | |||
job costing worksheet | Excel Worksheet Functions |