Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
update a sheet automatically with changing values from another she
Hello,
I would like to create a macro so that when I close my workbook, my "total days" value and "total cost" value (which are in my "Cost Tracking Summary" Sheet) are added to a new row of a table on a different worksheet, only if the "Total Days" has changed. These values (Total Days and Total Cost) are collated from other worksheets. If anyone can assist me I would greatly appreicate it. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
update a sheet automatically with changing values from another she
I am assuming (probably incorrectly) that your Total Days is one cell located
at A2, if not, change it below on line total_days = ... You should be able to copy this directly into you VB Editor. Sub Auto_Close() 'For it to run after you close the workbook, the macro needs to be called "Auto_Close" Dim total_days As Integer 'Stores the number of total days for today Dim this_sheet As Worksheet 'The current sheet you are on when you start the macro Set this_sheet = ActiveSheet Sheets("Cost Tracking Summary").Select total_days = Range("A2").Value 'Retrieve the number of total days from cell A2 Range("A2:B2").Select 'Copy the contents of A2 (Total Days) and B2 (Total Cost) Selection.Copy Sheets("different worksheet").Select 'Move to the different worksheet (need to change name to what the worksheet is really called) Count = 1 While Len(Cells(Count, 1).Value) < 0 'Find the first unoccupied row in column A Count = Count + 1 Wend If Cells(Count - 1, 1).Value < total_days Then 'If the last occupied row in Column A does not contain the same value as today's total days, then paste the values in the first unoccupied row Range(Cells(Count, 1), Cells(Count, 1)).Select ActiveSheet.Paste Else End If this_sheet.Select 'Select the sheet the user was on before running the macro End Sub Hope this helps, Jim "QP1" wrote: Hello, I would like to create a macro so that when I close my workbook, my "total days" value and "total cost" value (which are in my "Cost Tracking Summary" Sheet) are added to a new row of a table on a different worksheet, only if the "Total Days" has changed. These values (Total Days and Total Cost) are collated from other worksheets. If anyone can assist me I would greatly appreicate it. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update Summary Sheet Automatically | Excel Discussion (Misc queries) | |||
How can I get data to automatically update using a master sheet? | Excel Worksheet Functions | |||
how do I update a sheet automatically with the info from another? | New Users to Excel | |||
Copying (Master) column to another sheet to automatically update | Excel Worksheet Functions | |||
How do I set my webpage to automatically update a changing spread. | Excel Discussion (Misc queries) |