VBA将工作表作为参数传递给宏

Zanam 发表于 Dev

扎南

我有一个excelSub如下

Sub btnToCsvASIN()
    Dim Wb As Workbook
    Dim Ds, Ws As Worksheet
    Set Ds = Sheet2
    
    Set Wb = Workbooks.Add
    Set Ws = Wb.ActiveSheet
    
    Ds.Range("A2:I100").Copy Ws.Range("A1")
    
    Dim pKey, pId As String       
    
    Application.DisplayAlerts = False
    Wb.SaveAs ThisWorkbook.Path & "\Uploader.csv", FileFormat:=xlCSV
    Wb.Close
End Sub

我想使此函数通用，以便无论工作表编号是什么，它都可以工作。

因此，我也尝试了以下和其他语法：

Function btnToCsvASIN(SheetNum as String)
    Dim Wb As Workbook
    Dim Ds, Ws As Worksheet
    Set Ds = SheetNum
    
......
End Sub

我的目标是进行以下通用函数调用：

btnToCsvASIN(Sheet1)
btnToCsvASIN(Sheet23)

我一直无法完成这项工作。

我Sub现在从另一个宏中调用它。

VBasic2008

将范围复制到新工作簿

Option Explicit

Sub TESTbtnToCsvASIN()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")
    btnToCsvASIN ws
    ' Or using code name:
    'btnToCsvASIN Sheet1
End Sub

Sub btnToCsvASIN(ws As Worksheet)
    If Not ws Is Nothing Then
        Application.ScreenUpdating = False
        With Workbooks.Add
            ws.Range("A2:I100").Copy .ActiveSheet.Range("A1")
            Application.DisplayAlerts = False
            .SaveAs ThisWorkbook.Path & "\" & "Uploader", FileFormat:=xlCSV
            Application.DisplayAlerts = True
            '.FollowHyperlink .Path
            .Close SaveChanges:=False
        End With
        Application.ScreenUpdating = True
    End If
End Sub

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。