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
