How do I run the same macro, VBA-code for 30 different excel workbooks?

Question

I tried using .xlsb file so as to execute it in all different workbooks, but keep getting Error 400, even though my Macro is working for that particular file. I wrote this simple code to delete the empty rows and columns in the workbook.

Sub RepeatTask()
    Dim xSh As Worksheet
    Application.ScreenUpdating = False
    For Each xSh In Worksheets
        xSh.Select
        Call task
    Next

    Application.ScreenUpdating = True
End Sub

Sub task()
    Dim LastColumnIndex As Integer
    Dim LastRowIndex As Integer
    Dim RowIndex As Integer
    Dim ColumnIndex As Integer
    Dim UsedRng As Range

    Set UsedRng = ActiveSheet.UsedRange
    LastRowIndex = UsedRng.Row - 1 + UsedRng.Rows.Count
    LastColumnIndex = UsedRng.Column - 1 + UsedRng.Columns.Count

    Application.ScreenUpdating = False

    For RowIndex = LastRowIndex To 1 Step -1
        If Application.CountA(Rows(RowIndex)) = 0 Then
            Rows(RowIndex).Delete
        End If
    Next RowIndex

    For ColumnIndex = LastColumnIndex To 1 Step -1
        If Application.CountA(Columns(ColumnIndex)) = 0 Then
            Columns(ColumnIndex).Delete
        End If
    Next ColumnIndex

    Application.ScreenUpdating = True

End Sub

How can we help you if you don't even show your code. Please read [ask] and how to build a [mcve]. — Pᴇʜ, Jan 25 '19 at 10:48
@Pᴇʜ I am sorry for my mistake, I'm new at this platform. Can you look into and help me with my mistakes now? — Udita Alawadhi, Jan 25 '19 at 11:06
*Which* line is throwing the error? What does the error message actually say? If your code is working for one workbook but not others -- then either your code is making an assumption about the workbook which is true in one case but not the other, or there is a bug in how you are calling the code in the other workbook. Without a [mcve], it is hard to say more than that. — John Coleman, Jan 25 '19 at 11:07
[This link](https://forums.techguy.org/threads/solved-vba-error-400-running-an-excel-macro.775340/) gives a useful tip on debugging an error 400 -- which seems like it sometimes pops up without an error description. — John Coleman, Jan 25 '19 at 11:12
You must use `Long` for row counting variables! Excel has more rows than `Integer` can handle! • I recommend [always to use Long instead of Integer](https://stackoverflow.com/a/26409520/3219613) in VBA since there is no benefit in `Integer` at all. — Pᴇʜ, Jan 25 '19 at 11:15

score 1 · Answer 1 · answered Jan 25 '19 at 11:21

I recommend not to use .Select instead give the worksheet as parameter and specify the sheet in all ws.Rows(), ws.Columns() etc.

If you make the parameter Optional you can fallback to If ws Is Nothing Then Set ws = ActiveSheet if Task is called without parameter.

Sub RepeatTask()
    Dim xSh As Worksheet
    Application.ScreenUpdating = False
    For Each xSh In Worksheets
        Task xSh 'give worksheet as parameter here instead of select!
    Next

    Application.ScreenUpdating = True
End Sub

Sub Task(Optional ws As Worksheet)
    If ws Is Nothing Then Set ws = ActiveSheet

    Dim LastColumnIndex As Long
    Dim LastRowIndex As Long
    Dim RowIndex As Long
    Dim ColumnIndex As Long
    Dim UsedRng As Range

    Set UsedRng = ws.UsedRange
    LastRowIndex = UsedRng.Row - 1 + UsedRng.Rows.Count
    LastColumnIndex = UsedRng.Column - 1 + UsedRng.Columns.Count

    Application.ScreenUpdating = False

    For RowIndex = LastRowIndex To 1 Step -1
        If Application.CountA(ws.Rows(RowIndex)) = 0 Then
            ws.Rows(RowIndex).Delete
        End If
    Next RowIndex

    For ColumnIndex = LastColumnIndex To 1 Step -1
        If Application.CountA(ws.Columns(ColumnIndex)) = 0 Then
            ws.Columns(ColumnIndex).Delete
        End If
    Next ColumnIndex

    Application.ScreenUpdating = True
End Sub

Good advice (+1) though OP hasn't really given enough information to know if this will solve the issue. Since you brought up avoiding select, it might help to link to [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/4996248) — John Coleman, Jan 25 '19 at 11:25
I need to run this code for different files, How can i do that instead of making a different vba project for each one? — Udita Alawadhi, Jan 25 '19 at 11:46
@UditaAlawadhi You need to loop through the workbooks then and then loop through the sheets of that workbook: `For Each xSh In LoopWorkbook.Worksheets`. We cannot give a more detailed answer since we don't know where your workbooks are nor how you open them. Do some research on how to loop through multiple files (there are many tutorials). Give it a try and show then edit the code in your original question and tell where you got stuck or errors. — Pᴇʜ, Jan 25 '19 at 12:16

score 0 · Answer 2 · answered Jan 28 '19 at 09:47

The way I could do this for multiple Excel workbooks is here:

Sub OpenFiles()
    Dim xStrPath As String
    Dim xFileDialog As FileDialog
    Dim xFile As String

    On Error Resume Next
    Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
    xFileDialog.AllowMultiSelect = False
    xFileDialog.Title = "Select a folder [Kutools for Excel]"
    If xFileDialog.Show = -1 Then
        xStrPath = xFileDialog.SelectedItems(1)
    End If

    If xStrPath = "" Then Exit Sub
    xFile = Dir(xStrPath & "\*.xlsm")
    Do While xFile <> ""
        Workbooks.Open xStrPath & "\" & xFile
        xFile = Dir
    Loop
    Call Optil
    Call SaveAndCloseAllWorkbooks

End Sub

Sub Optil()
Dim book As Workbook, sheet As Worksheet
Application.ScreenUpdating = False

For Each book In Workbooks
    For Each sheet In book.Worksheets
        Task sheet
    Next sheet
Next book
Application.ScreenUpdating = True

End Sub

Sub RepeatTask()
Dim xSh As Worksheet
Application.ScreenUpdating = False
For Each xSh In Worksheets
    Task xSh 'give worksheet as parameter here instead of select!
Next

Application.ScreenUpdating = True
End Sub

Sub Task(Optional ws As Worksheet)
    If ws Is Nothing Then Set ws = ActiveSheet
    Dim LastColumnIndex As Long
    Dim LastRowIndex As Long
    Dim RowIndex As Long
    Dim ColumnIndex As Long
    Dim UsedRng As Range

    Set UsedRng = ws.UsedRange
    LastRowIndex = UsedRng.Row - 1 + UsedRng.Rows.Count
    LastColumnIndex = UsedRng.Column - 1 + UsedRng.Columns.Count

    Application.ScreenUpdating = False

    For RowIndex = LastRowIndex To 1 Step -1
        If Application.CountA(ws.Rows(RowIndex)) = 0 Then
            ws.Rows(RowIndex).Delete
        End If
    Next RowIndex

    For ColumnIndex = LastColumnIndex To 1 Step -1
        If Application.CountA(ws.Columns(ColumnIndex)) = 0 Then
            ws.Columns(ColumnIndex).Delete
        End If
    Next ColumnIndex

    Application.ScreenUpdating = True
End Sub

Sub SaveAndCloseAllWorkbooks()
Dim bk As Workbook

For Each bk In Workbooks
    If Not bk Is ThisWorkbook Then
    bk.Close SaveChanges:=True
    End If
 Next bk

'If You want to save and close active workbook too 
'ThisWorkbook.Close SaveChanges:=True 

End Sub

It can also be done with a better optimised code, if anyone has ideas, please contribute. — Udita Alawadhi, Jan 28 '19 at 09:49
Remove `On Error Resume Next` This line just hides **all** error messages but errors will still occur, you just cannot see them. You cannot fix errors, that you cannot see and if you don't fix them your code might fail. • If your code works and you are looking for optimization please have a look at https://codereview.stackexchange.com/ — Pᴇʜ, Jan 28 '19 at 13:21

How do I run the same macro, VBA-code for 30 different excel workbooks?

2 Answers2