Automate process

Hi,

a small 'Close' event macro should do the trick, it will continually save A1 into C&Row()=month

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim myMonth As Integer
myMonth = Month(Now)
Sheets("Sheet1").Cells(myMonth, 3).Value = Sheets("Sheet1").Cells(1, 1).Value
End Sub

Put that in the ThisWorkBook sheet in (Alt/F11) VB Editor.

hth
---

Originally Posted by martins

many thanks - only problem is I am not too hot on macros / VB - is there no other way that data can be captured and made static or will I just have to get my macro thinking cap on?

just press Alt/F11

select ThisWorkBook ( from View, Project Explorer window - double-click -or- Rightmouse and View Code )

paste the code into that sheet.

---

Originally Posted by martins

so am I right in thinking that at present the figure from A1 will be captured in cell 2 (i.e. FEB being current month) and then on 1st march the figures will be captured in cell C3 and Cell 2 will then contain the last figure entered at end of Feb and remain static? and this process will continue through to cell C12 i.e. December?

+will it matter if cell A1 contains a formula that totals figures from another column

Many thanks for your help

Hi,

that is what is hoped for, and the .Value component does not care how the value was obtained.

---

Originally Posted by martins

could you help a bit further - I actually have 2 columns collecting figures so instead of A1, I have C226 and C229 and the corresponding monthly cells are starting B241 (jan) and C241 (jan) respectively through to b252 and c252. Could you provide the macro for that scenario please.

Hi,

for simplicity, you can repeat the line

Sheets("Sheet1").Cells(myMonth, 3).Value = Sheets("Sheet1").Cells(1, 1).Value

with different addresses, alternately you can use the form

Sheets("Sheet1").Cells(myMonth, 3).Value = Sheets("Sheet1").Cells(1, 1).Value
Sheets("Sheet1").Range("C" & myMonth).Value = Sheets("Sheet1").Range("A1").Value

both mean the same thing, the Row increment is calculated into myMonth, so to save into C241 for Jan would be

Sheets("Sheet1").Cells(240 + myMonth, 3).Value = Sheets("Sheet1").Cells(246,3).Value
Sheets("Sheet1").Range("C" & 240 + myMonth).Value = Sheets("Sheet1").Range("C246").Value

etc.
also, to save typing and because these are all sheet1

With Sheets("Sheet1")
.Cells(240 + myMonth, 3).Value = .Cells(246,3).Value
.Range("C" & 240 + myMonth).Value = .Range("C246").Value
End With

so you can just duplicate these lines and amend the addresses as required. (only one of the lines is required, they are different forms of the same instruction)

hth
---

Originally Posted by martins

To achieve the required result I am entering the code as follows and this seems to work -

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim myMonth As Integer
myMonth = Month(Now)
Sheets("Schedule2007").Cells(240 + myMonth, 2).Value = Sheets("Schedule2007").Cells(226, 3).Value
Sheets("Schedule2007").Cells(240 + myMonth, 3).Value = Sheets("Schedule2007").Cells(229, 3).Value
End Sub

This works fine however, something that I had forgotten until testing the macro is that within the work sheet I have to constantly add or delete rows therefore, my target cells references can go up or down. Is there any way around this or is it getting too complicated?

Hi,

not at all, providing there is some way to identify a row, ie, from VB you can use the Match( ) formula to find the row required for the 'from' and the 'to' rows and incorporate them into the code, also

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim myMonth As Integer
myMonth = Month(Now)
With Sheets("Schedule2007")
    .Cells(240 + myMonth, 2).Value = .Cells(226, 3).Value
    .Cells(240 + myMonth, 3).Value = .Cells(229, 3).Value
    End With
End Sub

is shorter and does the same thing.

What is the identifier for the two rows?

---