Solved: Data From Previous Months file

[sujittalukde]

The files attached are related to products where opening balance of current month shoul come from closing balnce of previous month ie to say Opening balance of May 2007 should come from closing bal of april 2007 file
For this I always save the file named D:\product\ItemledgerApril2007 and so on. These monthwise files are made from itemledger.xlt (template file)

[sujittalukde]

Please help!!!!!!!!!!!!!!!!111

[zv735]

if will autofill data when u open file
prefile depend on cell F1 and G1

 
Sub PutDataPreM()
Dim FileNamePre As String
Dim FileNameNow As String
'

'
Application.ScreenUpdating = False
Application.DisplayAlerts = False
    FileNameNow = ActiveWorkbook.Name
    FileNamePre = "itemledger" & Range("F1").Value & Range("G1").Value & ".xls"
    Workbooks.Open Filename:=ActiveWorkbook.Path & "\" & FileNamePre
    Windows(FileNameNow).Activate
    Range("C3:C6").Value = Workbooks(FileNamePre).Sheets("item Ledger").Range("C3:C6").Value
    Windows(FileNamePre).Activate
    ActiveWindow.Close
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

see att file

[sujittalukde]

Code is made in right direction just some modification needed-
Opening balance data for current month say May2007should come from closing balance data of immediate previous month say April2007 (being the immediate previous month for current month which is May2007) I have clarified the same again in the attached file in red font (to highlight the changes made from my 1st uploaded file) for ur better understanding.

[zv735]

Just edit

Range("C3:C6").Value = Workbooks(FileNamePre).Sheets("item Ledger").Range("C3:C6").Value

to

 
Range("C3:C6").Value = Workbooks(FileNamePre).Sheets("item Ledger").Range("G3:G6").Value

see att file

[sujittalukde]

This is correct but why the month name is changing from May to april in May2007.xls file in cell F1? Can this be rectified?

[zv735]

yes it can open old file depend on cell F1

such as F1 as April it be open file April(itemledgerApril2007.xls)

F1 as Jan it be open File Jan(itemledgerJan2007.xls),if file is exact

are Codeing by

 
FileNamePre = "itemledger" & Range("F1").Value & Range("G1").Value & ".xls"

[sujittalukde]

Thanks zv735, but my qwery is why the month name in cell F1 for the file may2007.xls is changing to April from May after running the code?

[zv735]

sorry for my confused that cell F1 is pre-month

i edit it already but it not auto-change when u change month
you will edit by yourself

see att file

[sujittalukde]

One thing - I have saved a new file itemledgerJune2007.xls in the same directory , the data in Column C of this file should come from column G of itemledgerMay2007.xls, but the data is coming column G of itemledgerApril2007.xls. Any solution??????????????

[zv735]

see att file
but your file must save in Full name of month
such as

January
February
March
.
.
.

[sujittalukde]

Superb! Thanks a ton to you!
But one qwery, when I am pressing the button to get pre data say for July 2007 month Why other files (April2007,May2007,June2007) automatically open & ask for Whether you want to save changes? Why this does not happen automatically at background?

[zv735]

this att file is not auto-fill pre-data

until you pressing the button

Thank you

[sujittalukde]

Great work . Thanks ! Again thanks!!!!