Loop for all Worksheets

**RAparente** · 12-08-2008, 11:25 AM

hELP ME SO i CAN run THIS codeS to all worksheets in the activeworkbook AT ONE TIME,(e.i. Oct 2008). I record this macro IN my Personal macros as 'Macro5.'

Code:

   Windows("IDF Clearing Template REVISED.xls").Activate
    Rows("1:2").Select
    Range("BE1").Activate
    Selection.Copy
    Windows("Oct 2008.xls").Activate
    Rows("1:1").Select
    Range("BG1").Activate
    Selection.Insert Shift:=xlDown
    Windows("IDF Clearing Template REVISED.xls").Activate
    Columns("BM:BQ").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Oct 2008.xls").Activate
    Application.WindowState = xlMinimized
    Columns("BM:BM").Select
    ActiveSheet.Paste
    Range("BN2").Select
    Application.Run "'IDF Clearing Template REVISED.xls'!Macro1"

Thanks and best regards

**Bob Phillips** · 12-08-2008, 11:35 AM

Is this what you want?

Code:

Dim sh As Worksheet
Dim target As Worksheet

    Set target = Workbooks("Oct 2008.xls").Worksheets(1)
    For Each sh In Workbooks("IDF Clearing Template REVISED.xls")
    
        sh.Range("BE1").Copy
        target.Range("BG1").Insert Shift:=xlDown
        sh.Columns("BM:BQ").Copy target.Columns("BM:BM")
        SHRange("BN2").Select
        Application.Run "'IDF Clearing Template REVISED.xls'!Macro1"
    Next sh

**royUK** · 12-08-2008, 11:40 AM

Your post does not comply with Rule 3 of our Forum Rules. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here http://www.excelforum.com/misc.php?do=bbcode#code

Note: I will add them this time only!

**RAparente** · 12-08-2008, 11:44 AM

HI.

my working file is the 'Oct 2008.xls'. In each Worksheet, I want to run the the 'macro5' located in my "Personal Macros.bas".

Thanks

**royUK** · 12-08-2008, 11:55 AM

Is this whay ytou mean, place the code in the workbook you are using

Code:

 Dim ws     As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        Application.Run "'Personal.xls'!Macro5"
    Next ws

**RAparente** · 12-09-2008, 03:09 AM

Originally Posted by royUK

Is this whay ytou mean, place the code in the workbook you are using

Code:

 Dim ws     As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        Application.Run "'Personal.xls'!Macro5"
    Next ws

Hi. The Macro5 codes only works for active worksheet not for the rest of all non-empty worksheets.

Thanks.

**royUK** · 12-09-2008, 07:39 AM

Then you either need to amend macro5 to work efficiently to suit your needs or amend my code to actually select each sheet, which will of course be slower & less efficientr

Thread: Loop for all Worksheets

Thread Tools

Display

Loop for all Worksheets

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions