使用VBA重新排列多个Excel文件中的列 [英] Rearranging Columns in Multiple Excel Files using VBA

查看：71
本文介绍了使用VBA重新排列多个Excel文件中的列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我在重新排列多个Excel文件中的列时遇到问题.我有312个文件，每个文件有200列.我想按特定顺序排列各列，以便合并所有文件以创建主文件.我有重新排列一个Excel文件中的列的VBA代码.我希望代码循环遍历所有文件，重新排列列，保存并关闭工作簿.

I am having problems rearranging columns in multiple Excel files. I have 312 files with 200 columns each. I would like to arrange the columns in a particular order so that I combine all the files to create a master file. I have the VBA code that rearranges the columns in one Excel file. I would like the code to loop through all the files, rearrange the columns, save and close the workbook.

Sub MoveColumns()
strName = Dir(File_Path & "\" & "*.csv")  'CHANGE FILE EXTENSION TO OTHER  EXCEL BASED EXTENSION IF YOU WANT
Dim iRow As Long
Dim iCol As Long
Do While strName <> vbNullString
If active_workbook.Name <> strName And strName <> "" Then
    Workbooks.Open Filename:=File_Path & "\" & strName
    Set dataset_workbook = ActiveWorkbook
'Constant values
ActiveWorksheet = Sheets(1)
target_sheet = "Final Report" 'Specify the sheet to store the results
iRow = Sheets(ActiveWorksheet).UsedRange.Rows.Count 
Worksheets.Add.Name = "Final Report"

For iCol = 1 To Sheets(ActiveWorksheet).UsedRange.Columns.Count

'Sets the TargetCol to zero in order to prevent overwriting existing targetcolumns
TargetCol = 0

'Read the header of the original sheet to determine the column order
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Source" Then TargetCol = 1
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Business Name" Then TargetCol = 2
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Legal Name" Then TargetCol = 3
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Physical Address" Then TargetCol = 4
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Physical Address Number" Then TargetCol = 5
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Physical Pre Direction" Then TargetCol = 6
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Physical Address Name" Then TargetCol = 7
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Physical Address Suffix" Then TargetCol = 8
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Physical Post Direction" Then TargetCol = 9
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Physical City" Then TargetCol = 10
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Physical State" Then TargetCol = 11
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Physical ZIP" Then TargetCol = 12
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Physical ZIP 4" Then TargetCol = 13
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Key Executive Name" Then TargetCol = 14
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "First Name" Then TargetCol = 15
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Middle Initial" Then TargetCol = 16
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Last Name" Then TargetCol = 17
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Title" Then TargetCol = 18
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Gender" Then TargetCol = 19
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Location Employee Size" Then TargetCol = 20
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Corporate Employee Size" Then TargetCol = 21
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Revenue / Yr" Then TargetCol = 22
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Mailing Address" Then TargetCol = 23
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Mailing Address Number" Then TargetCol = 24
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Mailing Pre Direction" Then TargetCol = 25
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Mailing Address Name" Then TargetCol = 26
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Mailing Address Suffix" Then TargetCol = 27
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Mailing Post Direction" Then TargetCol = 28
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Mailing City" Then TargetCol = 29
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Mailing State" Then TargetCol = 30
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Mailing ZIP" Then TargetCol = 31
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Mailing ZIP 4" Then TargetCol = 32
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Phone" Then TargetCol = 33
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Fax" Then TargetCol = 34
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Toll-Free" Then TargetCol = 35
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "County Name" Then TargetCol = 36
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "County Population" Then TargetCol = 37
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Metro Area" Then TargetCol = 38
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Latitude" Then TargetCol = 39
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Longitude" Then TargetCol = 40
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "EIN" Then TargetCol = 41
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Main Line of Business" Then TargetCol = 42
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Franchise" Then TargetCol = 43
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Location Type" Then TargetCol = 44
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Importer or Exporter" Then TargetCol = 45
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Manufacturer" Then TargetCol = 46
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Primary SIC" Then TargetCol = 47
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Primary SIC Description" Then TargetCol = 48
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "SIC02" Then TargetCol = 49
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "SIC02.Description" Then TargetCol = 50
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "SIC03" Then TargetCol = 51
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "SIC03.Description" Then TargetCol = 52
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "SIC04" Then TargetCol = 53
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "SIC04.Description" Then TargetCol = 54
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "SIC05" Then TargetCol = 55
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "SIC05.Description" Then TargetCol = 56
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "SIC06" Then TargetCol = 57
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "SIC06.Description" Then TargetCol = 58
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "SIC07" Then TargetCol = 59
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "SIC07.Description" Then TargetCol = 60
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "SIC08" Then TargetCol = 61
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "SIC08.Description" Then TargetCol = 62
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "SIC09" Then TargetCol = 63
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "SIC09.Description" Then TargetCol = 64
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "SIC10" Then TargetCol = 65
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "SIC10.Description" Then TargetCol = 66
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 1" Then TargetCol = 67
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 1 Description" Then TargetCol = 68
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 2" Then TargetCol = 69
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 2 Description" Then TargetCol = 70
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 3" Then TargetCol = 71
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 3 Description" Then TargetCol = 72
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 4" Then TargetCol = 73
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 4 Description" Then TargetCol = 74
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 5" Then TargetCol = 75
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 5 Description" Then TargetCol = 76
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 6" Then TargetCol = 78
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 6 Description" Then TargetCol = 79
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 7" Then TargetCol = 80
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 7 Description" Then TargetCol = 81
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 8" Then TargetCol = 82
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 8 Description" Then TargetCol = 83
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 9" Then TargetCol = 84
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 9 Description" Then TargetCol = 85
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 10" Then TargetCol = 86
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 10 Description" Then TargetCol = 87
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Non-Profit" Then TargetCol = 88
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 2 Description" Then TargetCol = 70
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 3" Then TargetCol = 71
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 3 Description" Then TargetCol = 72
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 4" Then TargetCol = 73
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 4 Description" Then TargetCol = 74
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 5" Then TargetCol = 75
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 5 Description" Then TargetCol = 76
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 6" Then TargetCol = 78
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 6 Description" Then TargetCol = 79
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 7" Then TargetCol = 80
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 7 Description" Then TargetCol = 81
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 8" Then TargetCol = 82
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 8 Description" Then TargetCol = 83
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 9" Then TargetCol = 84
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 9 Description" Then TargetCol = 85
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 10" Then TargetCol = 86
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "NAICS 10 Description" Then TargetCol = 87
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Non-Profit" Then TargetCol = 88
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Number of PCs" Then TargetCol = 89
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Public / Private" Then TargetCol = 90
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Home Based Business" Then TargetCol = 91
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Small Business" Then TargetCol = 92
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Square Footage" Then TargetCol = 93
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Website" Then TargetCol = 94
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Women Owned" Then TargetCol = 95
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Year of Incorporation" Then TargetCol = 96
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Ticker Symbol" Then TargetCol = 97
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Stock Exchange" Then TargetCol = 98
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Fortune 1000 Ranking" Then TargetCol = 99
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Credit Score" Then TargetCol = 100
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "2014 Revenue/Yr" Then TargetCol = 101
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "2012 Revenue/Yr" Then TargetCol = 102
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "2010 Revenue/Yr" Then TargetCol = 103
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "3 Year % Sales Growth" Then TargetCol = 104
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "5 Year % Sales Growth" Then TargetCol = 105
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "2014 Employees" Then TargetCol = 106
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "2012 Employees" Then TargetCol = 107
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "2010 Employees" Then TargetCol = 108
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "3 Year % Employee Growth" Then TargetCol = 109
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "5 Year % Employee Growth" Then TargetCol = 110
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive First Name 1" Then TargetCol = 111
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Last Name 1" Then TargetCol = 112
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Title 1" Then TargetCol = 113
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Gender 1" Then TargetCol = 114
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive First Name 2" Then TargetCol = 115
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Last Name 2" Then TargetCol = 116
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Title 2" Then TargetCol = 117
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Gender 2" Then TargetCol = 118
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive First Name 3" Then TargetCol = 119
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Last Name 3" Then TargetCol = 120
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Title 3" Then TargetCol = 121
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Gender 3" Then TargetCol = 122
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive First Name 4" Then TargetCol = 123
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Last Name 4" Then TargetCol = 124
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Title 4" Then TargetCol = 125
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Gender 4" Then TargetCol = 126
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive First Name 5" Then TargetCol = 127
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Last Name 5" Then TargetCol = 128
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Title 5" Then TargetCol = 129
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Gender 5" Then TargetCol = 130
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive First Name 6" Then TargetCol = 131
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Last Name 6" Then TargetCol = 132
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Title 6" Then TargetCol = 133
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Gender 6" Then TargetCol = 134
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive First Name 7" Then TargetCol = 135
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Last Name 7" Then TargetCol = 136
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Title 7" Then TargetCol = 137
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Gender 7" Then TargetCol = 138
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive First Name 8" Then TargetCol = 139
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Last Name 8" Then TargetCol = 140
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Title 8" Then TargetCol = 141
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Gender 8" Then TargetCol = 142
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive First Name 9" Then TargetCol = 143
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Last Name 9" Then TargetCol = 144
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Title 9" Then TargetCol = 145
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Gender 9" Then TargetCol = 146
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive First Name 10" Then TargetCol = 147
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Last Name 10" Then TargetCol = 148
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Title 10" Then TargetCol = 149
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Gender 10" Then TargetCol = 150
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive First Name 11" Then TargetCol = 151
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Last Name 11" Then TargetCol = 152
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Title 11" Then TargetCol = 153
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Gender 11" Then TargetCol = 154
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive First Name 12" Then TargetCol = 155
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Last Name 12" Then TargetCol = 156
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Title 12" Then TargetCol = 157
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Gender 12" Then TargetCol = 158
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive First Name 13" Then TargetCol = 159
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Last Name 13" Then TargetCol = 160
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Title 13" Then TargetCol = 161
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Gender 13" Then TargetCol = 162
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive First Name 14" Then TargetCol = 163
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Last Name 14" Then TargetCol = 164
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Title 14" Then TargetCol = 165
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Gender 14" Then TargetCol = 166
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive First Name 15" Then TargetCol = 167
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Last Name 15" Then TargetCol = 168
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Title 15" Then TargetCol = 169
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Gender 15" Then TargetCol = 170
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive First Name 16" Then TargetCol = 171
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Last Name 16" Then TargetCol = 172
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Title 16" Then TargetCol = 173
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Gender 16" Then TargetCol = 174
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive First Name 17" Then TargetCol = 175
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Last Name 17" Then TargetCol = 176
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Title 17" Then TargetCol = 177
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Gender 17" Then TargetCol = 178
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive First Name 18" Then TargetCol = 179
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Last Name 18" Then TargetCol = 180
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Title 18" Then TargetCol = 181
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Gender 18" Then TargetCol = 182
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive First Name 19" Then TargetCol = 182
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Last Name 19" Then TargetCol = 183
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Title 19" Then TargetCol = 184
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Gender 19" Then TargetCol = 185
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive First Name 20" Then TargetCol = 186
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Last Name 20" Then TargetCol = 187
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Title 20" Then TargetCol = 188
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Executive Gender 20" Then TargetCol = 189
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Est. Accounting Annual Expense" Then TargetCol = 190
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Est. Advertising Annual Expense" Then TargetCol = 191
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Est. Business Insurance Annual Expense" Then TargetCol = 192
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Est. Legal Annual Expense" Then TargetCol = 193
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Est. Office Equipment Annual Expense" Then TargetCol = 194
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Est. Rent Annual Expense" Then TargetCol = 195
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Est. Technology Annual Expense" Then TargetCol = 196
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Est. Telecom Annual Expense" Then TargetCol = 197
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Est. Utilities Annual Expense" Then TargetCol = 198
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "Date" Then TargetCol = 199
If Sheets(ActiveWorksheet).Cells(1, iCol).Value = "AtoZ ID" Then TargetCol = 200

'If a TargetColumn was determined (based upon the header information) then copy the column to the right spot
If TargetCol <> 0 Then
    'Select the column and copy it
    Sheets(ActiveWorksheet).Range(Sheets(ActiveWorksheet).Cells(1, iCol), Sheets(ActiveWorksheet).Cells(iRow, iCol)).Copy     Destination:=Sheets(target_sheet).Cells(1, TargetCol)
End If
   Next iCol 'Move to the next column until all columns are read
   End If
   strName = Dir
   Loop
   Application.DisplayAlerts = True
   End Sub

推荐答案

这是一种非常精简的方法，用于重新排列312个文件中的列.

Here is a very streamlined method for rearranging the columns in your 312 files.

使用名为模板"的工作表创建一个新的工作簿.在模板的第一行中，按所需顺序输入所需的列标题.例如，在单元格A1中输入:Source.在单元格B1中输入:公司名称.在单元格C1中输入:合法名称.并继续按所需列的顺序填充第1行.

Create a new workbook with a worksheet called "Template". In the first row of the template enter the column headers you want... in the order you want. For example, in cell A1 enter: Source. In cell B1 enter: Business Name. In cell C1 enter: Legal Name. And continue filling row 1 with the order of your columns you want.

现在将以下例程放在模板工作簿的标准代码模块中.

Now place the following routine in a standard code module in the template workbook.:

Public Sub Jonah()

    Dim c&, sPath$, sFile$, v, rOut As Range, wbReport As Workbook

    On Error Resume Next
    sPath = "D:\List\List\"
    sFile = Dir(sPath & "*.csv")

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    With Sheets("template")
        c = .Cells(1, .Columns.Count).End(xlToLeft).Column
        v = .[a1].Resize(, c)
        .Copy
    End With
    Set wbReport = ActiveWorkbook
    wbReport.Sheets(1).Name = "Final Report"
    With wbReport.Worksheets.Add
        .Name = "Tmp"
        Set rOut = .[a1].Resize(, c)
        rOut = v
    End With
    Do
        With Workbooks.Open(sPath & sFile)
            With .Worksheets(1)
                With .[a1].CurrentRegion.Resize(, c + 1)
                    .AdvancedFilter xlFilterCopy, , rOut
                    rOut.CurrentRegion.Resize(, 1).Offset(, c) = .Parent.Name
                    v = rOut.CurrentRegion.Resize(, c + 1).Offset(1)
                    With wbReport.Sheets("Final Report")
                        .Cells(.Rows.Count, "A").End(xlUp)(2).Resize(UBound(v, 1), UBound(v, 2)) = v
                    End With
                    rOut.CurrentRegion.Resize(, c + 1).Offset(1).ClearContents
                End With
            End With
            .Close 0
        End With
        sFile = Dir
    Loop Until sFile = ""
    With wbReport.Sheets("Final Report")
        .[a1].Offset(, c) = "CSV"
        .Cells.EntireColumn.AutoFit
    End With
    wbReport.Sheets("Tmp").Delete
    Application.DisplayAlerts = False
    Application.ScreenUpdating = True

End Sub

在例程顶部编辑sPath，以便它知道您的312个CSV文件在哪里.

Edit the sPath at the top of the routine so that it knows where your 312 CSV files are.

运行例程.

就是这样.

它将使用名为最终报告"的工作表从所有文件中创建一个主文件.但列将按照您与模板建立的顺序.

It will create a master file from all the files with a worksheet named "Final Report". BUT THE COLUMNS WILL BE IN THE ORDER YOU ESTABLISHED WITH THE TEMPLATE.

原始CSV文件将保持不变.

The original CSV files will remain unaltered.

这篇关于使用VBA重新排列多个Excel文件中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆