Results 1 to 2 of 2

Macro to loop through all sheets and delete unwanted columns

Threaded View

  1. #1
    Registered User
    Join Date
    07-10-2008
    Location
    Norwich, England
    MS-Off Ver
    Office XP
    Posts
    15

    Macro to loop through all sheets and delete unwanted columns

    I have the following macro which opens all the Excel files within a given folder, loops through them all and deletes columns G to N, then it re-saves each of the files.

    It runs without error, but when I opened the Excel files to check that the columns had been deleted correctly, I noticed that the macro had only worked for one tab per file (Sheet1). However, I need it to loop through all the tabs in each of the files to delete the unwanted columns. The tab names are not the same in all the spreadsheet files.

    I thought that the For Each Worksheet In ActiveWorkbook.Worksheets command would loop through all of the tabs within each of the open files?

    How would I fix the macro so that it works for every tab?


    Sub OpenandchangeAllFiles()
        Dim fList() As String
        Dim fName As String
        Dim fPath As String
        Dim I As Integer
        'This folder will be searched for Excel files
        fPath = "C:\My Documents"
        'build a list of the files
        fName = Dir(fPath & "*.xls")
        While fName <> ""
            'add fName To the list
            I = I + 1
            ReDim Preserve fList(1 To I)
            fList(I) = fName
            'get Next filename
            fName = Dir()
        Wend
        'see If any files were found
        If I = 0 Then
             MsgBox "No files found"
            Exit Sub
        End If
     
        'cycle through the list And Open
        For I = 1 To UBound(fList)
            Workbooks.Open fPath & fList(I)
            'select all  worksheets In turn
            For Each Worksheet In ActiveWorkbook.Worksheets
                'delete all unwanted columns
                Columns("G:N").Select
                Selection.Delete Shift:=xlToLeft
            Next 'next worksheet
            'close &  save  changes To workbook
            ActiveWorkbook.Close True
        Next 'next workbook
     
        MsgBox "All unwanted columns have now been deleted from all the spreadsheets."
    End Sub
    Last edited by LemonTwist; 02-25-2009 at 10:41 AM. Reason: Problem solved, thanks!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1