重新排列所有工作表上的列 [英] Rearrange columns on all worksheets

问题描述

我下面粘贴的代码适用于工作簿中的一个工作表，但我不知道如何在工作簿中循环它，以便将其遍历到每个工作表.

I've got the code pasted below working for one worksheet in the workbook but I can't figure out how to loop it through the workbook so it does it to every sheet.

有人可以解释一下如何使用此代码的循环功能吗?:)

Can someone explain how to use to loop function for this code please? :)

Sub Rearrange_Columns()
Dim arrColOrder As Variant, ndx As Integer
Dim Found As Range, counter As Integer
arrColOrder = Array("Company", "First Name", "Last Name", "Email", "Category", "Address", "Suite or Unit?", "Suite/Unit", "City", "Province", "Postal Code", "Phone", "Fax", _
"Website", "Service Areas", "Logo", "CONCAT")
counter = 1
Application.ScreenUpdating = False
For ndx = LBound(arrColOrder) To UBound(arrColOrder)
    Set Found = Rows("1:1").Find(arrColOrder(ndx), LookIn:=xlValues, LookAt:=xlWhole,SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
    If Not Found Is Nothing Then
        If Found.Column <> counter Then
         Found.EntireColumn.Cut
         Columns(counter).Insert Shift:=xlToRight
         Application.CutCopyMode = False
        End If
        counter = counter + 1
     End If
Next ndx
End Sub

推荐答案

您所需要的只是遍历工作表，并为每个 Rows ， Columns ，范围等

What you need is just a loop through the worksheets, and specify the worksheet for each Rows, Columns, Range, etc

For Each ws In ThisWorkbook.Worksheets
    ws.Rows(…) 'specify the worksheet
Next ws

例如

Option Explicit

Sub RearrangeColumnsInAllWorksheets()
    Dim arrColOrder As Variant
    arrColOrder = Array("Company", "First Name", "Last Name", "Email", "Category", "Address", "Suite or Unit?", "Suite/Unit", "City", "Province", "Postal Code", "Phone", "Fax", "Website", "Service Areas", "Logo", "CONCAT")

    Dim ndx As Long
    Dim Found As Range

    Dim Counter As Long
    Application.ScreenUpdating = False

    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets 'loop through all worksheets
        Counter = 1
        For ndx = LBound(arrColOrder) To UBound(arrColOrder)
            Set Found = ws.Rows("1:1").Find(arrColOrder(ndx), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
            If Not Found Is Nothing Then
                If Found.Column <> Counter Then
                    Found.EntireColumn.Cut
                    ws.Columns(Counter).Insert Shift:=xlToRight
                    Application.CutCopyMode = False
                End If
                Counter = Counter + 1
             End If
        Next ndx
    Next ws

    Application.ScreenUpdating = True 'don't forget to turn it on again
End Sub

这篇关于重新排列所有工作表上的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！