在表格之间排列excel列 [英] Sort excel columns across sheets
本文介绍了在表格之间排列excel列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
解决方案
确保在运行宏之前保存整个工作簿。没有撤消。
希望这个帮助:
Sub ColumnRoustruct ()
'Horaciux 2014-06-23
Dim nextLabel As String
Dim currentLabel As String
Dim TotalPages As Integer
Dim TotalColumns As Integer
TotalPages = 10
TotalColumns = 200
'在每个页面中插入一个空白栏
对于p = 2到TotalPages
表格(p )。选择
列(A:A)。选择
Selection.Insert Shift:= xlToRight,CopyOrigin:= xlFormatFromLeftOrAbove
范围(B1)。选择
下一步
对于c = TotalColumns到1 Step -1
表格(1)。选择
'Debug.Print - &细胞(1,c)。 - & Str(c)
nextLabel =单元格(1,c).Text
表格(2)。选择
对于oldCulumn = 2到TotalColumns + 1
'调试打印单元格(1,oldCulumn).Text& - & Str(oldCulumn)
currentLabel = Cells(1,oldCulumn).Text
如果currentLabel = nextLabel Then
'Debug.Print currentLabel& - & Str(oldCulumn)
退出
结束如果
下一个
对于p = 2到TotalPages
表格(p)。选择
列(oldCulumn)。选择
Selection.Cut
列(A:A)。选择
Selection.Insert Shift:= xlToRight
下一个
下一个
对于p = 2到TotalPages
表格(p)。选择
范围(A1)。选择
下一个
End Sub
I have an excel file with 10 sheets. The sheets all contain the same column headers but different data. I sorted the first sheet manually and now I want all the columns in the other sheets to match the first sheet's order, I can't do them all manually because it would take me forever. How can I make all the columns across the workbook in the same order based on the first sheet order? I know little about VBA so looking for some help.
解决方案
Make sure you save the entire workbook before running a macro. There is no undo.
Hope this help:
Sub ColumnRearrangement()
'Horaciux 2014-06-23
Dim nextLabel As String
Dim currentLabel As String
Dim TotalPages As Integer
Dim TotalColumns As Integer
TotalPages = 10
TotalColumns = 200
'Insert a blank column in each page
For p = 2 To TotalPages
Sheets(p).Select
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Select
Next
For c = TotalColumns To 1 Step -1
Sheets(1).Select
'Debug.Print "-" & Cells(1, c).Text & "-" & Str(c)
nextLabel = Cells(1, c).Text
Sheets(2).Select
For oldCulumn = 2 To TotalColumns + 1
'Debug.Print Cells(1, oldCulumn).Text & "-" & Str(oldCulumn)
currentLabel = Cells(1, oldCulumn).Text
If currentLabel = nextLabel Then
'Debug.Print currentLabel & "-" & Str(oldCulumn)
Exit For
End If
Next
For p = 2 To TotalPages
Sheets(p).Select
Columns(oldCulumn).Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Next
Next
For p = 2 To TotalPages
Sheets(p).Select
Range("A1").Select
Next
End Sub
这篇关于在表格之间排列excel列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文