CODEWITHSUNDEEP
excelvba
Zanam
Zanam

Reputation: 4807

VBA passing sheets as parameter to macro

I have an excel Sub as follows

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

I want to make this function generic so that no matter what the Sheet number is, it works.

So, I tried the following and other syntax also:

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

My goal is to make following kind of generic function call:

btnToCsvASIN(Sheet1)
btnToCsvASIN(Sheet23)

I have been unable to make this work.

I call this Sub currently from within another macro.

Upvotes: 0

Views: 106

Answers (1)

VBasic2008
VBasic2008

Reputation: 54863

Copy Range To New Workbook

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

Upvotes: 1

Related Questions