Date Update

[drums4monty]

Hi all

I have a Stock spreadsheet for each month of the year, I use this formula to get the ending balance from say July 07 and put the totals into the opening balacne of Aug 07. No problem there. However, I have to change the date in the formula manually and do this for all 42 rows. Is there a way to change the date automatically, a user box or something like that?

='C:\Documents and Settings\abeaumont\[Stationery Stock Levels July 07.xls]Summary'!$G$5

Alan

[Charlize]

Maybe something like this : [VBA]Dim vmonth As String
vmonth = InputBox("Give month ...", "Provide month", "July 07")
Range("A1").Formula = _
"='C:\Documents and Settings\abeaumont\[Stationery Stock Levels " & _
vmonth & ".xls]Summary'!$G$5"[/VBA]

[Bob Phillips]

=DATE(YEAR('C:\Documents and Settings\abeaumont\[Stationery Stock Levels July 07.xls]Summary'!$G$5),
MONTH(='C:\Documents and Settings\abeaumont\[Stationery Stock Levels July 07.xls]Summary'!$G$5)+1,
DAY(='C:\Documents and Settings\abeaumont\[Stationery Stock Levels July 07.xls]Summary'!$G$5))

[drums4monty]

Thanks, two good solutions but how do I use them, the first one gets one total on put it in the top row, I need to fill from D5 to D44 the information I get from the previous months sheet G5 to G44 (I hope that this makes sense). I just dont know how to use the second one, I copied and pasted the formula in to cell D5 but all I got was the formula as text.

[drums4monty]

In case it is not clear, I need to get the ending totals from the current months sheet be it Jan, Feb, Mar etc. and put them into the next months sheet, e.g. end totals from Jan into start total for Feb, then at the end of Feb, end balance from Feb into start total for Mar, and so on.

[drums4monty]

In case it is not clear, I need to get the ending totals from the current months sheet be it Jan, Feb, Mar etc. and put them into the next months sheet, e.g. end totals from Jan into start total for Feb, then at the end of Feb, end balance from Feb into start total for Mar, and so on.

[Charlize]

[VBA]Sub Get_Previous_month()
Dim vmonth As String
Dim vloop As Long
vmonth = InputBox("Give month ...", "Provide month", "July 07")
For vloop = 5 To 44
Range("G" & vloop).Formula = _
"='C:\Documents and Settings\abeaumont\[Stationery Stock Levels " & _
vmonth & ".xls]Summary'!$G$" & vloop
Next vloop
End Sub[/VBA]

[Bob Phillips]

In mine, replace your formula with mine.

[drums4monty]

Thanks that works, I had to change the range to "D" as it was putting the new totals in G which is the old totals for the current month (if you follow). When I run the macro it keeps opening a dialog box every entry and I have to choose the month I want the totals from (i.e. if I am doing Aug I have to keep choosing Jul) is there a way it can just go into the last months worksheet e.g. Jan, and copy G5 - G44 and paste the data into the current month e.g. Feb D5 - D44

[drums4monty]

I did that xld, but it just appeared as text in the sheet

[Bob Phillips]

Is the cell formatted as text? Change it to General.

[drums4monty]

It has a formula in there at the moment and when I paste yours on top in appeas as text over 3 rows, do I need to do anything special to paste the code in to the cell?

[Charlize]

[VBA]Sub Get_Previous_month()
Dim vmonth As String
vmonth = InputBox("Give month ...", "Provide month", "July 07")
CWRIR "C:\Documents and Settings\abeaumont\", _
"Stationery Stock Levels " & vmonth & ".xls", _
"Summary", "G5:G44", "D5"
End Sub
Sub CWRIR(fPath As String, fName As String, sName As String, _
rng As String, destRngUpperLeftCell As String)
'CWRIR is short for ClosedWorkbookRangeIntoRange
Dim sRow As Integer
Dim sColumn As Integer
Dim sRows As Integer
Dim sColumns As Integer
Dim vrow As Integer
Dim vcol As Integer
Dim fpStr As String
Dim cArr()
Dim cwa
Dim destRange
On Error GoTo NoArr
If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
If Dir(fPath & fName) = "" Then
cwa = CVErr(xlErrValue)
MsgBox "Error with file :" & vbCrLf & fName, vbCritical
Exit Sub
End If
sRow = Range(rng).Row
sColumn = Range(rng).Column
sRows = Range(rng).Rows.Count
sColumns = Range(rng).Columns.Count
ReDim cArr(sRows, sColumns)
Set destRange = ActiveSheet.Range(destRngUpperLeftCell)
For vrow = 1 To sRows
For vcol = 1 To sColumns
fpStr = "'" & fPath & "[" & fName & "]" & sName & "'!" & _
"r" & sRow + vrow - 1 & "c" & sColumn + vcol - 1
destRange.Offset(vrow - 1, vcol - 1) = ExecuteExcel4Macro(fpStr)
Next
Next
NoArr:
End Sub
[/VBA]

[drums4monty]

Thanks Charlize that has done it, brill, all I have to do now is take a 0 out of certain rows as there is an empty row between certain rows, but I will try to do it myself first.

Thanks everyone for your time and help.

Regards

Alan

[Bob Phillips]

Don't paste it into the cell, select the cell and paste it into the formula bar.

[drums4monty]

This is what I came up with to take data out of certain cells:

[VBA]
Sub removezero()
'
Range("D6").Select
Selection.ClearContents
Range("G6").Select
Selection.ClearContents
Range("D8").Select
Selection.ClearContents
Range("G8").Select
Selection.ClearContents
Range("D10").Select
Selection.ClearContents
Range("G10").Select
Selection.ClearContents
Range("D12").Select
Selection.ClearContents
Range("G12").Select
Selection.ClearContents
Range("D14").Select
Selection.ClearContents
Range("G14").Select
Selection.ClearContents
Range("D16").Select
Selection.ClearContents
Range("G16").Select
Selection.ClearContents
Range("D18").Select
Selection.ClearContents
Range("G18").Select
Selection.ClearContents
Range("D20").Select
Selection.ClearContents
Range("G20").Select
Selection.ClearContents
Range("D24").Select
Selection.ClearContents
Range("G24").Select
Selection.ClearContents
Range("D30").Select
Selection.ClearContents
Range("G30").Select
Selection.ClearContents
Range("D32").Select
Selection.ClearContents
Range("G32").Select
Selection.ClearContents
Range("D34").Select
Selection.ClearContents
Range("G34").Select
Selection.ClearContents
Range("D35").Select
Selection.ClearContents
Range("D36").Select
Selection.ClearContents
Range("D37").Select
Selection.ClearContents
Range("D38").Select
Selection.ClearContents
Range("D39").Select
Selection.ClearContents
Range("D40").Select
Selection.ClearContents
Range("D41").Select
Selection.ClearContents
Range("G41").Select
Selection.ClearContents
Range("D42").Select
Selection.ClearContents
Range("G42").Select
Selection.ClearContents
Range("D43").Select
Selection.ClearContents
Range("G43").Select
Selection.ClearContents
End Sub
[/VBA]

[Charlize]

[VBA]Range("D6,G6,D8,G8,D10,G10,D12,G12,D14,G14,D16,G16").ClearContents[/VBA]

[drums4monty]

Thanks Charlize