excel - 如何使用 VBA 将代码应用于文件夹中的多个文件？

Question

我有一堆 excel 文件，我需要应用一些功能并从每个文件中提取两个新的 excel 文件。应用该功能后，我最终得到了两张工作表，每张工作表上有两列要保存为单独的 Excel 文件。作为一个完全的初学者，我已经在打开的工作簿上编写了完成工作的代码（我称之为“函数”），但是，当我将它应用到文件夹中的所有文件时，我迷失了。请原谅我不完整的代码，请参见下文。

Sub wB_temp()

Columns(1).EntireColumn.Delete
Columns(2).EntireColumn.Delete

    Dim arr0() As Variant, arr1 As Variant, arr2 As Variant
        
        arr0 = Range("A1").CurrentRegion.Value
        Sheets(1).Range("D1:E4723").Value = arr0

Rows(1).Insert
Range("B1") = "=AVERAGE(B3:B4724)"
Range("D1") = "prumer"
Range("E2") = Range("B2").Value + "-prum ku SD"
Range("E1") = ""

'applying a simple formula on each cell with a value in
    Dim c As Range
    Dim Lastrow As Long
    Dim Avr As Integer

        Lastrow = Cells(Rows.Count, "D").End(xlUp).row
        Avr = Range("B1").Value
    
    For Each c In Range("E3:E" & Lastrow)
            If c.Value <> "" Then c.Value = c.Value - Avr
    Next c

'plotting a scatter graph
ActiveSheet.Shapes.AddChart2(240, xlXYScatterLinesNoMarkers).Select
ActiveChart.SetSourceData Source:=Range("D3:E4724")
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveChart.FullSeriesCollection(1).Name = Range("E2").Value
    
ActiveChart.Parent.Cut
Range("I4").Select
ActiveSheet.Paste

        arr1 = Range("D2:E4724").Value

'here is where the first sheet is done

'add new sheet on the same workbook

Sheets.Add.Name = "Sheet2"

         ActiveSheet.Range("A1:B4723").Value = arr1
         
         arr2 = Range("A1").CurrentRegion.Value
         Sheets("Sheet2").Range("D1:E4723").Value = arr2
         
Rows(1).Insert
Range("B1") = "=STDEVP(B3:B4724)"
Range("E2") = Range("B2").Value
Range("E1") = ""

'applying a formula on each cell with a value in
    Dim c1 As Range
    Dim Lastrow1 As Long
    Dim Std As Integer

        Lastrow1 = Cells(Rows.Count, "D").End(xlUp).row
        Std = Range("B1").Value
    
    For Each c1 In Range("E3:E" & Lastrow1)
            If c1.Value <> "" Then c1.Value = c1.Value / Std
    Next c1

'plotting a scatter graph on the second sheet
ActiveSheet.Shapes.AddChart2(240, xlXYScatterLinesNoMarkers).Select
ActiveChart.SetSourceData Source:=Range("D3:E4724")
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveChart.FullSeriesCollection(1).Name = Range("E2").Value
    
ActiveChart.Parent.Cut
Range("I4").Select
ActiveSheet.Paste

End Sub

标签： excelvba