Hi Paul Thanks for reply, still I have a problem in it I copy the code u suggested but it updated the sheet "JANUARY" only. why please check once more.
Regards Rajesh kainikkara On 2/14/11, Paul Schreiner <schreiner_p...@att.net> wrote: > This isn't EXACTLY the same problem. > In your original post, you were selecting a range from "EMP REG" > and then selecting a monthly sheet and running an external macro called > Import_fromregister. > > In THIS file, all you're doing is copying range B2-O200 of sheet "ITEM > MASTER" > to each sheet. > > This should do it: > > Sub Update_sheets_All() > Dim M, ShtName > For M = 1 To 12 > ShtName = Format(M & "/1/2011", "mmmm") > If Sheets(ShtName).Range("b5") = 0 Then > Application.ScreenUpdating = False > Sheets("ITEM MASTER").Select > Range("B2:O200").Select > Selection.Copy > Sheets(ShtName).Select > Range("F2").Select > ActiveSheet.Paste > End If > Next M > End Sub > > Paul > > > > ________________________________ > From: Rajesh K R <rajeshkainikk...@gmail.com> > To: excel-macros@googlegroups.com > Sent: Mon, February 14, 2011 9:29:32 AM > Subject: Re: $$Excel-Macros$$ reduce the size of code > > Hi Paul, > Thank you very much for your quick replies .Due to my lack of > knowledge in vba I can't understand the code well .So I am attaching a > project what I am supposed to make which have the same problem. please > check the attached file and make necessary change. > > Regards & thanks > Rajesh kainikkara > > > > On 2/14/11, Paul Schreiner <schreiner_p...@att.net> wrote: >> I'm not sure what your Import_fromregister macro is doing, >> so I'm not sure whether the sheet name needs to be a public variable. >> >> But this should loop through the months: >> >> >> Sub Update_sheets_All() >> Dim M, ShtName >> For M = 1 To 12 >> ShtName = Format(M & "/1/2011", "mmmm") >> If Sheets(ShtName).Range("b5") = 0 Then >> Application.ScreenUpdating = False >> Sheets("EMP REG").Select >> Range("B2:O200").Select >> Selection.Copy >> Sheets(ShtName).Select >> Application.Run "Compare.XLS!Module2.Import_fromregister" >> End If >> Next M >> End Sub >> >> Paul >> >> >> >> >> ________________________________ >> From: Rajesh Kainikkara <rajeshkainikk...@gmail.com> >> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com> >> Sent: Sun, February 13, 2011 11:17:50 AM >> Subject: $$Excel-Macros$$ reduce the size of code >> >> Hi >> >> I have a workbook which contain 15 sheets ,sheet1 is the data of >> January, sheet2 is February and so on, the last 3 sheet have some data >> for comparison.I am using a macro code to copy the data like this >> Sub Update_sheet_April() >> If Sheets("APRIL").Range("b5") = 0 Then >> Application.ScreenUpdating = False >> Sheets("EMP REG").Select >> Range("B2:O200").Select >> Selection.Copy >> Sheets("APRIL").Select >> Application.Run "Compare.XLS!Module2.Import_fromregister" >> End If >> End Sub >> Sub Update_sheet_May() >> If Sheets("MAY").Range("b5") = 0 Then >> Application.ScreenUpdating = False >> Sheets("EMP REG").Select >> Range("B2:O200").Select >> Selection.Copy >> Sheets("MAY").Select >> Application.Run "Compare.XLS!Module2.Import_fromregister" >> End If >> If it is possible the macro can remember the sheet which give command >> and come back to the page after copying data I can reduce the size of >> code.If possible pls give me the code >> >> Regards >> Rajesh >> >> -- >>---------------------------------------------------------------------------------- >>- >> >> Some important links for excel users: >> 1. Follow us on TWITTER for tips tricks and links : >> http://twitter.com/exceldailytip >> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >> 3. Excel tutorials at http://www.excel-macros.blogspot.com >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >> >> To post to this group, send email to excel-macros@googlegroups.com >> >> <><><><><><><><><><><><><><><><><><><><><><> >> Like our page on facebook , Just follow below link >> http://www.facebook.com/discussexcel >> >> -- >>---------------------------------------------------------------------------------- >>- >> Some important links for excel users: >> 1. Follow us on TWITTER for tips tricks and links : >> http://twitter.com/exceldailytip >> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >> 3. Excel tutorials at http://www.excel-macros.blogspot.com >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >> >> To post to this group, send email to excel-macros@googlegroups.com >> >> <><><><><><><><><><><><><><><><><><><><><><> >> Like our page on facebook , Just follow below link >> http://www.facebook.com/discussexcel >> > > -- > ---------------------------------------------------------------------------------- > > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > > -- > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel