Turning VBA Macro into Function

[emack1230]

I have the following, thank you Davesexcel. Now I am needing to figure out how to turn this into a function that will run based on a certain cell having a text value on a different tab within the workbook.

Sub OpenWorkbook()

Dim wb As Workbook, bk As Workbook

Set bk = Workbooks("destination file name.xlsm")
'assumed open

Set wb = Workbooks.Open("copied from file.xlsx") 'you would need the full address and name of workbook


wb.Worksheets("tab name").Range("A1:I500").Copy bk.Worksheets("destination tab").Range("A1")


wb.Close SaveChanges:=True


End Sub

 

[Biz]

Try Code bellow. Does it help?

Function CopyDataToWorkbook(wb As Workbook, bk As Workbook, wsSource As Worksheet, wsDestination As Worksheet, rngSource As Range, rngDestination As Range)

wb.wsSource.wsSource.Copy bk.wsDestination.rngDestination

wb.Close SaveChanges:=True

End Function

 

[emack1230]

I am getting a Run time '438': Object doesn't support this property or method

I will send the code to you privately since our company name is in the file name for security.

 

[Biz]

I'm sorry. Does the code open file from SharePoint?

 

[emack1230]

I'm sorry. Does the code open file from SharePoint?

It acted like it was going to and that may be the piece I am missing. Should I build that in from the original I posted so it will open the Sharepoint file?

 

[Biz]

Maybe you need to rebuilt to open files from SharePoint

Sub OpenSharePointFile()
    Dim sharePointLink As String
    Dim fileName As String
    Dim fileURL As String

    ' Set the SharePoint link and file name
    sharePointLink = "https://your-sharepoint-site-url/"
    fileName = "example.xlsx"

    ' Construct the file URL
    fileURL = sharePointLink & "/" & fileName

    ' Open the file
    Workbooks.Open fileURL

End Sub

 

[emack1230]

Maybe you need to rebuilt to open files from SharePoint

Sub OpenSharePointFile()
    Dim sharePointLink As String
    Dim fileName As String
    Dim fileURL As String

    ' Set the SharePoint link and file name
    sharePointLink = "https://your-sharepoint-site-url/"
    fileName = "example.xlsx"

    ' Construct the file URL
    fileURL = sharePointLink & "/" & fileName

    ' Open the file
    Workbooks.Open fileURL

End Sub

Thanks will give it a whirl, most likely tomorrow when I can update and work with my partner.