Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying data from one sheet to another on a specific date
Hi,
I posted this in the worksheet functions forum but no response...so I thought that I would try here.. I want to be able to automatically copy data from one sheet to another sheet on the last day of the month (the sheets are in the same workbook) when the user opens or saves it (either way would be fine) On sheet 1 there is a running total say cell C3 and on sheet 2 the destination cells Jan, Feb, March etc. Is there a way to have my running total populate the correct months figures while keeping the previous months total. Just now the user manually inputs the figures but if I can automate this task it would save time for the user. I hope that I have explained the problem clearly enough..... Any help you can provide would be appreciated. Gav. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying data from one sheet to another on a specific date
Use the year as the column index and month as the row index to the
cell on Sheet2, something like: Sub SaveMonthlySubtotal() Dim yr As Integer, mon As Integer mon = Month(Now) yr = Year(Now) - 2005 Sheets("Sheet1").Range("A3").Copy _ Destination:=Sheets("Sheet2").Cells(mon, yr) End Sub Adjust the mon and yr variables to get the value into the cell you want. HTH Carl On May 2, 3:21 am, Gav123 wrote: Hi, I posted this in the worksheet functions forum but no response...so I thought that I would try here.. I want to be able to automatically copy data from one sheet to another sheet on the last day of the month (the sheets are in the same workbook) when the user opens or saves it (either way would be fine) On sheet 1 there is a running total say cell C3 and on sheet 2 the destination cells Jan, Feb, March etc. Is there a way to have my running total populate the correct months figures while keeping the previous months total. Just now the user manually inputs the figures but if I can automate this task it would save time for the user. I hope that I have explained the problem clearly enough..... Any help you can provide would be appreciated. Gav. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying data from one sheet to another on a specific date
Hi Carl,
Thanks for your response... The macro is almost working perfectly, there is one slight problem I need it to keep the integrity of the previous month data for example.. Say April was 20% and May is 30% when the data is transferred to sheet called "Monthly Graph" it updates both April and May to 30% and I need April to remain as 20% Sub SaveMonthlySubtotal() Dim yr As Integer, mon As Integer mon = Month(Now) yr = Year(Now) - 2005 Sheets("Overall").Range("D5").Copy _ Destination:=Sheets("Monthly Graph").Cells(mon, yr) End Sub Is it possible to have the months update but keep the previous months totals? Your help is appreciated.. Gav. "Carl Hartness" wrote: Use the year as the column index and month as the row index to the cell on Sheet2, something like: Sub SaveMonthlySubtotal() Dim yr As Integer, mon As Integer mon = Month(Now) yr = Year(Now) - 2005 Sheets("Sheet1").Range("A3").Copy _ Destination:=Sheets("Sheet2").Cells(mon, yr) End Sub Adjust the mon and yr variables to get the value into the cell you want. HTH Carl On May 2, 3:21 am, Gav123 wrote: Hi, I posted this in the worksheet functions forum but no response...so I thought that I would try here.. I want to be able to automatically copy data from one sheet to another sheet on the last day of the month (the sheets are in the same workbook) when the user opens or saves it (either way would be fine) On sheet 1 there is a running total say cell C3 and on sheet 2 the destination cells Jan, Feb, March etc. Is there a way to have my running total populate the correct months figures while keeping the previous months total. Just now the user manually inputs the figures but if I can automate this task it would save time for the user. I hope that I have explained the problem clearly enough..... Any help you can provide would be appreciated. Gav. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying data from one sheet to another on a specific date
I set up a new workbook with two sheets and your cells and macro, and
it works perfectly. You must have something else going on. Take another look. Maybe a formula or merged cells? Want me to take a look at it? Carl. On May 4, 4:41 am, Gav123 wrote: Hi Carl, Thanks for your response... The macro is almost working perfectly, there is one slight problem I need it to keep the integrity of the previous month data for example.. Say April was 20% and May is 30% when the data is transferred to sheet called "Monthly Graph" it updates both April and May to 30% and I need April to remain as 20% Sub SaveMonthlySubtotal() Dim yr As Integer, mon As Integer mon = Month(Now) yr = Year(Now) - 2005 Sheets("Overall").Range("D5").Copy _ Destination:=Sheets("Monthly Graph").Cells(mon, yr) End Sub Is it possible to have the months update but keep the previous months totals? Your help is appreciated.. Gav. "Carl Hartness" wrote: Use the year as the column index and month as the row index to the cell on Sheet2, something like: Sub SaveMonthlySubtotal() Dim yr As Integer, mon As Integer mon = Month(Now) yr = Year(Now) - 2005 Sheets("Sheet1").Range("A3").Copy _ Destination:=Sheets("Sheet2").Cells(mon, yr) End Sub Adjust the mon and yr variables to get the value into the cell you want. HTH Carl On May 2, 3:21 am, Gav123 wrote: Hi, I posted this in the worksheet functions forum but no response...so I thought that I would try here.. I want to be able to automatically copy data from one sheet to another sheet on the last day of the month (the sheets are in the same workbook) when the user opens or saves it (either way would be fine) On sheet 1 there is a running total say cell C3 and on sheet 2 the destination cells Jan, Feb, March etc. Is there a way to have my running total populate the correct months figures while keeping the previous months total. Just now the user manually inputs the figures but if I can automate this task it would save time for the user. I hope that I have explained the problem clearly enough..... Any help you can provide would be appreciated. Gav.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying Data from one sheet to another sheet on a specific day | Excel Worksheet Functions | |||
Problem copying to another sheet with specific criteria | Excel Programming | |||
Copying specific cells from a sheet to another | Excel Discussion (Misc queries) | |||
Copying Specific Values From Sheet | Excel Discussion (Misc queries) | |||
Copying specific rows from one sheet to another | Excel Programming |