asked on

Format on all excel files withing a folder

I recorded this macro and i have to perfom this on all excel files within the folder and on all worksheets within each file save and close. i know i may be asking for alot but could i get a VBA that can do this and all i will have to do is insert the following macro into the VBA. I'm not very good a VBA but i'm trying to get better.

Sub Macro6()
'
'Whatever

' Macro6 Macro
'

'
Windows("8-2012 - SRF-08 Cat Code 7_Nathan Dorie V1.xlsx").Activate
Range("F269").Select
ActiveWindow.ScrollRow = 255
ActiveWindow.ScrollRow = 251
ActiveWindow.ScrollRow = 240
ActiveWindow.ScrollRow = 209
ActiveWindow.ScrollRow = 171
ActiveWindow.ScrollRow = 149
ActiveWindow.ScrollRow = 105
ActiveWindow.ScrollRow = 86
ActiveWindow.ScrollRow = 77
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
Range("F8").Select
With ActiveWindow
.Width = 1053
.Height = 431.25
End With
Range("F8,F8:P10,F12:P12,F14:P15,F17:P17,F20:P26").Select
Range("F20").Activate
ActiveWindow.SmallScroll Down:=15
Range("F8,F8:P10,F12:P12,F14:P15,F17:P17,F20:P26,F34:P37").Select
Range("F34").Activate
ActiveWindow.SmallScroll Down:=12
Range( _
"F8,F8:P10,F12:P12,F14:P15,F17:P17,F20:P26,F34:P37,F40:P43,F46:P49,F52:P55,F70:P73" _
).Select
Range("F70").Activate
ActiveWindow.SmallScroll Down:=9
Range( _
"F8,F8:P10,F12:P12,F14:P15,F17:P17,F20:P26,F34:P37,F40:P43,F46:P49,F52:P55,F70:P73,F76:P79" _
).Select
Range("F76").Activate
ActiveWindow.SmallScroll Down:=12
Range( _
"F8,F8:P10,F12:P12,F14:P15,F17:P17,F20:P26,F34:P37,F40:P43,F46:P49,F52:P55,F70:P73,F76:P79,F82:P85" _
).Select
Range("F82").Activate
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.SmallScroll Down:=6
Range( _
"F8,F8:P10,F12:P12,F14:P15,F17:P17,F20:P26,F34:P37,F40:P43,F46:P49,F52:P55,F70:P73,F76:P79,F82:P85,F88:P91,F106:P109,F154:P157" _
).Select
Range("F154").Activate
ActiveWindow.SmallScroll Down:=18
Range( _
"F8,F8:P10,F12:P12,F14:P15,F17:P17,F20:P26,F34:P37,F40:P43,F46:P49,F52:P55,F70:P73,F76:P79,F82:P85,F88:P91,F106:P109,F154:P157,F178:P181" _
).Select
Range("F178").Activate
ActiveWindow.SmallScroll Down:=6
Range( _
"F8,F8:P10,F12:P12,F14:P15,F17:P17,F20:P26,F34:P37,F40:P43,F46:P49,F52:P55,F70:P73,F76:P79,F82:P85,F88:P91,F106:P109,F154:P157,F178:P181,F214:P219" _
).Select
Range("F214").Activate
ActiveWindow.SmallScroll Down:=12
Range( _
"F8,F8:P10,F12:P12,F14:P15,F17:P17,F20:P26,F34:P37,F40:P43,F46:P49,F52:P55,F70:P73,F76:P79,F82:P85,F88:P91,F106:P109,F154:P157,F178:P181,F214:P219,F223:P229,F230:P230" _
).Select
Range("F230").Activate
ActiveWindow.SmallScroll Down:=12
Range( _
"F8,F8:P10,F12:P12,F14:P15,F17:P17,F20:P26,F34:P37,F40:P43,F46:P49,F52:P55,F70:P73,F76:P79,F82:P85,F88:P91,F106:P109,F154:P157,F178:P181,F214:P219,F223:P229,F230:P230,F233:P241" _
).Select
Range("F233").Activate
ActiveWindow.SmallScroll Down:=12
Range( _
"F8,F8:P10,F12:P12,F14:P15,F17:P17,F20:P26,F34:P37,F40:P43,F46:P49,F52:P55,F70:P73,F76:P79,F82:P85,F88:P91,F106:P109,F154:P157,F178:P181,F214:P219,F223:P229,F230:P230,F233:P241,F244:P258" _
).Select
Range("F244").Activate
ActiveWindow.SmallScroll Down:=12
Range( _
"F8,F8:P10,F12:P12,F14:P15,F17:P17,F20:P26,F34:P37,F40:P43,F46:P49,F52:P55,F70:P73,F76:P79,F82:P85,F88:P91,F106:P109,F154:P157,F178:P181,F214:P219,F223:P229,F230:P230,F233:P241,F244:P258,F261:P277" _
).Select
Range("F261").Activate
ActiveWindow.SmallScroll Down:=9
Range( _
"F8,F8:P10,F12:P12,F14:P15,F17:P17,F20:P26,F34:P37,F40:P43,F46:P49,F52:P55,F70:P73,F76:P79,F82:P85,F88:P91,F106:P109,F154:P157,F178:P181,F214:P219,F223:P229,F230:P230,F233:P241,F244:P258,F261:P277,F280:P280,F284:P284" _
).Select
Range("F284").Activate
Selection.NumberFormat = "_(* #,##0_);[Red]_(* (#,##0);_(* ""-""??_);_(@_)"
Range("G290").Select
End Sub
Sub FUCCK()
'
' FUCCK Macro
'
' Keyboard Shortcut: Ctrl+z
'
Windows("8-2012 - SRF-08 Cat Code 7_Nathan Dorie V1.xlsx").Activate
ActiveWindow.ScrollRow = 270
ActiveWindow.ScrollRow = 255
ActiveWindow.ScrollRow = 243
ActiveWindow.ScrollRow = 236
ActiveWindow.ScrollRow = 231
ActiveWindow.ScrollRow = 226
ActiveWindow.ScrollRow = 221
ActiveWindow.ScrollRow = 216
ActiveWindow.ScrollRow = 211
ActiveWindow.ScrollRow = 183
ActiveWindow.ScrollRow = 175
ActiveWindow.ScrollRow = 158
ActiveWindow.ScrollRow = 152
ActiveWindow.ScrollRow = 107
ActiveWindow.ScrollRow = 100
ActiveWindow.ScrollRow = 88
ActiveWindow.ScrollRow = 83
ActiveWindow.ScrollRow = 76
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 55
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
Range("F276,F8:P9,F12:P12,F14:P15,F17:P17").Select
Range("F17").Activate
ActiveWindow.SmallScroll Down:=6
Range("F276,F8:P9,F12:P12,F14:P15,F17:P17,F20:P27,P27").Select
Range("P27").Activate
ActiveWindow.SmallScroll Down:=9
Range("F276,F8:P9,F12:P12,F14:P15,F17:P17,F20:P27,P27,F34:P37").Select
Range("F34").Activate
ActiveWindow.SmallScroll Down:=12
Range( _
"F276,F8:P9,F12:P12,F14:P15,F17:P17,F20:P27,P27,F34:P37,F40:P43,F46:P49,F52:P55" _
).Select
Range("F52").Activate
ActiveWindow.SmallScroll Down:=12
Range( _
"F276,F8:P9,F12:P12,F14:P15,F17:P17,F20:P27,P27,F34:P37,F40:P43,F46:P49,F52:P55,F70:P73,F76:P79,F82:P85" _
).Select
Range("F82").Activate
ActiveWindow.SmallScroll Down:=15
Range( _
"F276,F8:P9,F12:P12,F14:P15,F17:P17,F20:P27,P27,F34:P37,F40:P43,F46:P49,F52:P55,F70:P73,F76:P79,F82:P85,F88:P91,F106:P109" _
).Select
Range("F106").Activate
ActiveWindow.SmallScroll Down:=9
Range( _
"F276,F8:P9,F12:P12,F14:P15,F17:P17,F20:P27,P27,F34:P37,F40:P43,F46:P49,F52:P55,F70:P73,F76:P79,F82:P85,F88:P91,F106:P109,F154:P157,F178:P181" _
).Select
Range("F178").Activate
ActiveWindow.SmallScroll Down:=12
Range( _
"F276,F8:P9,F12:P12,F14:P15,F17:P17,F20:P27,P27,F34:P37,F40:P43,F46:P49,F52:P55,F70:P73,F76:P79,F82:P85,F88:P91,F106:P109,F154:P157,F178:P181,F214:P214,F215:P219" _
).Select
Range("F215").Activate
ActiveWindow.SmallScroll Down:=9
Range( _
"F276,F8:P9,F12:P12,F14:P15,F17:P17,F20:P27,P27,F34:P37,F40:P43,F46:P49,F52:P55,F70:P73,F76:P79,F82:P85,F88:P91,F106:P109,F154:P157,F178:P181,F214:P214,F215:P219,F223:P230" _
).Select
Range("F223").Activate
ActiveWindow.SmallScroll Down:=15
Range( _
"F276,F8:P9,F12:P12,F14:P15,F17:P17,F20:P27,P27,F34:P37,F40:P43,F46:P49,F52:P55,F70:P73,F76:P79,F82:P85,F88:P91,F106:P109,F154:P157,F178:P181,F214:P214,F215:P219,F223:P230,F233:P241" _
).Select
Range("F233").Activate
ActiveWindow.SmallScroll Down:=12
Range( _
"F276,F8:P9,F12:P12,F14:P15,F17:P17,F20:P27,P27,F34:P37,F40:P43,F46:P49,F52:P55,F70:P73,F76:P79,F82:P85,F88:P91,F106:P109,F154:P157,F178:P181,F214:P214,F215:P219,F223:P230,F233:P241,F244:P258" _
).Select
Range("F244").Activate
ActiveWindow.SmallScroll Down:=15
Range( _
"F276,F8:P9,F12:P12,F14:P15,F17:P17,F20:P27,P27,F34:P37,F40:P43,F46:P49,F52:P55,F70:P73,F76:P79,F82:P85,F88:P91,F106:P109,F154:P157,F178:P181,F214:P214,F215:P219,F223:P230,F233:P241,F244:P258,F262:P277,F280:P280" _
).Select
Range("F280").Activate
ActiveWindow.SmallScroll Down:=9
Range( _
"F276,F8:P9,F12:P12,F14:P15,F17:P17,F20:P27,P27,F34:P37,F40:P43,F46:P49,F52:P55,F70:P73,F76:P79,F82:P85,F88:P91,F106:P109,F154:P157,F178:P181,F214:P214,F215:P219,F223:P230,F233:P241,F244:P258,F262:P277,F280:P280,F284:Q284,Q284" _
).Select
Range("Q284").Activate
Windows("FOR AUG DELETE Margin CPL Reveiwer.xlsx").Activate
With ActiveWindow
.Top = 2.5
.Left = 71.5
End With
Sheets("Expenses").Select
Range("E22").Select
Windows("8-2012 - SRF-08 Cat Code 7_Nathan Dorie V1.xlsx").Activate
End Sub

Norie

Which sub is it you want to run on all the worksheets in all the workbooks in a folder?

Is it Macro6 or Fucck?

The 2nd one doesn't actually appear to do anything apart from scroll about, selecting/activating cells here and there.

Jenedge73

ASKER

Just Macro6

SANTABABY

Could u please describe what do you want the above macro to do for u? I think that can help to get a better understanding about your needs.

Norie

Is this all you really need to do?

Sub Macro6()
    
    Range( _
        "F8,F8:P10,F12:P12,F14:P15,F17:P17,F20:P26,F34:P37,F40:P43,F46:P49,F52:P55,F70:P73,F76:P79,F82:P85,F88:P91,F106:P109,F154:P157,F178:P181,F214:P219,F223:P229,F230:P230,F233:P241,F244:P258,F261:P277,F280:P280,F284:P284" _
        ).NumberFormat = "_(* #,##0_);[Red]_(* (#,##0);_(* ""-""??_);_(@_)"
    
End Sub

Open in new window

Here's code that goes through all the workbooks in a folder and applies that formatting to each worksheet.

Sub LoopFiles()
Dim wb As Workbook
Dim ws As Worksheet
Dim strExt As String
Dim strPath As String    ' Change folder name to the folder with the files in it
Dim strFilename As String

    strExt = "*.xls*"

    strPath = "C:\MyFolder\"

    strFilename = Dir(strPath & strExt)

    While Len(strFilename) > 0

        Set wb = Workbooks.Open(Filename:=strFilename)

        For Each ws In wb.Worksheets
            ws.Range( _
                    "F8,F8:P10,F12:P12,F14:P15,F17:P17,F20:P26,F34:P37,F40:P43,F46:P49,F52:P55,F70:P73,F76:P79,F82:P85,F88:P91,F106:P109,F154:P157,F178:P181,F214:P219,F223:P229,F230:P230,F233:P241,F244:P258,F261:P277,F280:P280,F284:P284" _
                    ).NumberFormat = "_(* #,##0_);[Red]_(* (#,##0);_(* ""-""??_);_(@_)"
        Next ws

        wb.Close SaveChanges:=False

        strFilename = Dir
    Wend

End Sub

Open in new window

Jenedge73

ASKER

It keeps trying to debug here

Sub LoopFiles()
Dim wb As Workbook
Dim ws As Worksheet
Dim strExt As String
Dim strPath As String ' Change folder name to the folder with the files in it
Dim strFilename As String

strExt = "*.xls*"

strPath = "H:\Commercial West Financials\August 2012\Prairie\Regional Managers Cat Code 6 Level\"

strFilename = Dir(strPath & strExt)

While Len(strFilename) > 0

Set wb = Workbooks.Open(Filename:=strFilename) (((((It keeps getting stuck here))))
For Each ws In wb.Worksheets
ws.Range( _
"F8,F8:P10,F12:P12,F14:P15,F17:P17,F20:P26,F34:P37,F40:P43,F46:P49,F52:P55,F70:P73,F76:P79,F82:P85,F88:P91,F106:P109,F154:P157,F178:P181,F214:P219,F223:P229,F230:P230,F233:P241,F244:P258,F261:P277,F280:P280,F284:P284" _
).NumberFormat = "_(* #,##0_);[Red]_(* (#,##0);_(* ""-""??_);_(@_)"
Next ws

wb.Close SaveChanges:=False

strFilename = Dir
Wend

Norie

Where does it error and what's the message?

Jenedge73

ASKER

compile error:
Named argument not found

Jenedge73

ASKER

Set wb = Workbooks.Open(Filename:=strFilename)

Norie

Try it without Filename.

Set wb = Workbooks.Open(strFilename)

Open in new window

Jenedge73

ASKER

the debugger starts and Set wb = Workbooks.Open(Filename:=strFilename) is highlighted in yellow

Jenedge73

ASKER

Sub LoopFiles()
Dim wb As Workbook
Dim ws As Worksheet
Dim strExt As String
Dim strPath As String ' Change folder name to the folder with the files in it
Dim strFilename As String

strExt = "*.xls*"

strPath = "H:\Commercial West Financials\August 2012\Prairie\Regional Managers Cat Code 6 Level\"

strFilename = Dir(strPath & strExt)

While Len(strFilename) > 0

Set wb = Workbooks.Open(strFilename)

For Each ws In wb.Worksheets
ws.Range( _
"F8,F8:P10,F12:P12,F14:P15,F17:P17,F20:P26,F34:P37,F40:P43,F46:P49,F52:P55,F70:P73,F76:P79,F82:P85,F88:P91,F106:P109,F154:P157,F178:P181,F214:P219,F223:P229,F230:P230,F233:P241,F244:P258,F261:P277,F280:P280,F284:P284" _
).NumberFormat = "_(* #,##0_);[Red]_(* (#,##0);_(* ""-""??_);_(@_)"
Next ws

wb.Close SaveChanges:=False

strFilename = Dir
Wend

End Sub

Did i put the folder name in the correct spot?

Norie

Yes as far as I can see.

Is it still not working?

PS It's quite hard to read the code, try putting code tags around it - there's a button on the toolbar for it.

Jenedge73

ASKER

ya did it again tried both

Set wb = Workbooks.Open(strFilename)

Open in new window

Set wb = Workbooks.Open(Filename:=strFilename)

Open in new window

I keep getting the same debugging error
i ave attached a image of it. its the same regaurdless of the code i put in

Norie

Do you have any other code in the workbook you are running this code from?

Jenedge73

ASKER

No. I also tried on a different computer, and with a new workbook

Norie

Can you attach the workbook?

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jenedge73

ASKER

Thanks, its working, but it isn't cycling through all worksheets, only through the one it opens on.

Norie

The code does loop through all the worksheets.

' open workbook
Set wb = Workbooks.Open(strFilename)

'loop through all the worksheets in the workbook just opened
For Each ws In wb.Worksheets
            ws.Range( _
                    "F8,F8:P10,F12:P12,F14:P15,F17:P17,F20:P26,F34:P37,F40:P43,F46:P49,F52:P55,F70:P73,F76:P79,F82:P85,F88:P91,F106:P109,F154:P157,F178:P181,F214:P219,F223:P229,F230:P230,F233:P241,F244:P258,F261:P277,F280:P280,F284:P284" _
                    ).NumberFormat = "_(* #,##0_);[Red]_(* (#,##0);_(* ""-""??_);_(@_)"
Next ws

Open in new window

Jenedge73

ASKER

Thanks this saved me lots of time