excel - 从多个工作簿中提取数据

Question

我想使用通配符和结束名称（以蓝色突出显示）从多个工作簿中的选项卡名称（以黄色突出显示）下的列名（以绿色突出显示）下的红色框中提取数据。创建工作簿后，我的代码停止了。（下面的代码）并对其进行格式化。请帮助我如何获取所有数据，直到最后一个数据

Private Sub CommandButton3_Click()

Dim JCN As String, Path As String

UserForm1.Hide

JCN = TextBox3.Value

ReportName = JCN & " PANEL NESTING REPORT"

UserName = Environ$("Username")

Path = "C:\Users\" & UserName & "\Desktop\"

Workbooks.Add
ActiveSheet.Name = "NESTING REPORT"

'Column Header
Range("B1").Value = "WBS Code"
Range("C1").Value = "Airline Code"
Range("D1").Value = "JCN"
Range("E1").Value = "'3000LVL"
Range("F1").Value = "MBOM"
Range("G1").Value = "Make Part"
Range("H1").Value = "LAV"
Range("I1").Value = "LC Number"
Range("J1").Value = "Rev"
Range("K1").Value = "Size"
Range("L1").Value = "Part Number"
Range("M1").Value = "Rev"
Range("N1").Value = "Qty"
Range("O1").Value = "Classification"
Range("P1").Value = "Type"
Range("Q1").Value = "Thickness"
Range("R1").Value = "Rawmat"
Range("S1").Value = "Remarks"
Range("T1").Value = "'.400 Code"
Range("U1").Value = "W.O."

'Format Column Header
Range("B1:U1").Select
Selection.Font.Bold = True
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlTop
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorLight2
    .TintAndShade = -0.499984740745262
    .PatternTintAndShade = 0
End With
With Selection.Font
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = 0
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
Columns("B:U").EntireColumn.AutoFit
ActiveWindow.DisplayGridlines = False

'Extract Data





'Save Workbook
ActiveWorkbook.SaveAs Filename:=Path & ReportName & ".xlsx"

'Close Workbook
ActiveWorkbook.Close

End Sub

标签： excelvba