PDA

View Full Version : Solved: Data From Previous Months file



sujittalukde
05-30-2007, 05:06 AM
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
05-30-2007, 09:35 PM
Please help!!!!!!!!!!!!!!!!111

zv735
05-30-2007, 10:33 PM
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
05-30-2007, 11:18 PM
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
05-31-2007, 01:12 AM
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
05-31-2007, 01:30 AM
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
05-31-2007, 02:10 AM
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
05-31-2007, 02:16 AM
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
05-31-2007, 11:40 AM
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
05-31-2007, 11:16 PM
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
06-01-2007, 03:26 AM
see att file
but your file must save in Full name of month
such as

January
February
March
.
.
.

sujittalukde
06-01-2007, 05:07 AM
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
06-01-2007, 07:45 AM
this att file is not auto-fill pre-data

until you pressing the button

Thank you

sujittalukde
06-01-2007, 09:29 PM
Great work . Thanks ! Again thanks!!!!