Re: $$Excel-Macros$$ reduce the size of code
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, ) 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
Re: $$Excel-Macros$$ reduce the size of code
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, ) 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, ) 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
Re: $$Excel-Macros$$ reduce the size of code
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, ) 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, ) 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
