Copying data from one sheet to another on a specific date

[Guest]

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.

 

[Carl Hartness]

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

 

[Guest]

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.

 

[cbhartness]

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.