[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 670
  • Last Modified:

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
0
Jenedge73
Asked:
Jenedge73
  • 10
  • 9
1 Solution
 
NorieCommented:
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.
0
 
Jenedge73Author Commented:
Just Macro6
0
 
SANTABABYCommented:
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
NorieCommented:
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

0
 
Jenedge73Author Commented:
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
0
 
NorieCommented:
Where does it error and what's the message?
0
 
Jenedge73Author Commented:
compile error:
Named argument not found
0
 
Jenedge73Author Commented:
Set wb = Workbooks.Open(Filename:=strFilename)
0
 
NorieCommented:
Try it without Filename.
Set wb = Workbooks.Open(strFilename) 

Open in new window

0
 
Jenedge73Author Commented:
the debugger starts and Set wb = Workbooks.Open(Filename:=strFilename) is highlighted in yellow
0
 
Jenedge73Author Commented:
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?
0
 
NorieCommented:
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.
0
 
Jenedge73Author Commented:
desya 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
0
 
NorieCommented:
Do you have any other code in the workbook you are running this code from?
0
 
Jenedge73Author Commented:
No.  I also tried on a different computer, and with a new workbook
0
 
NorieCommented:
Can you attach the workbook?
0
 
NorieCommented:
Right, I think I know what the problem could be as there is a slight mistake in the code.

However the mistake wouldn't have caused the errors you've described.

Anyway, replace this line.
     Set wb = Workbooks.Open(Filename:=strFilename)

Open in new window

with this.
     Set wb = Workbooks.Open(Filename:=strPath & strFilename)

Open in new window

The original line of code was likely to cause a file not found error because we were telling VBA to look for the file in the current directory rather than the actual directory, strPath, the file is in.
0
 
Jenedge73Author Commented:
Thanks, its working, but it isn't cycling through all worksheets, only through the one it opens on.
0
 
NorieCommented:
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

0
 
Jenedge73Author Commented:
Thanks this saved me lots of time
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 10
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now