CODEWITHSUNDEEP
excelvba
MetaTag
MetaTag

Reputation: 55

Copy named ranges from one workbook to another?

I have a Workbook A which has three named ranges Alpha, Beta, Charlie.
I would like to copy values of those name ranges to Workbook B which is stored in the SharePoint location. Workbook B also has three named ranges Alpha, Beta, Charlie.
Workbook A is not stored in the SharePoint and it comes in named differently each time so I cannot predefine it's name whereas Workbook B always has the same name.

I tried below example.

Sub CopyPaste() 
    Dim sourceWb As Workbook
    Dim openWb As Workbook
        
    Set openWb = ThisWorkbook
    Set sourceWb = Workbooks.Open(Filename:="https://SharePointLocationToWorkbookB.xlsm ", ReadOnly:=False, UpdateLinks:=False)
    sourceWb.Range("Alpha").Value = openWb("sheet1").Range("Alpha").Value
    sourceWb.Range("Beta").Value = openWb("sheet1").Range("Beta").Value
    sourceWb.Range("Charlie").Value = openWb("sheet1").Range("Charlie").Value
End Sub

Upvotes: 0

Views: 101

Answers (1)

Tim Williams
Tim Williams

Reputation: 166241

Your sourceWb is confusingly-named... Maybe try something like this:

Sub CopyPaste()

    Dim sourceWs As Worksheet, destWs As Worksheet, rn
    
    Set sourceWs = ThisWorkbook.Worksheets("Sheet1")
    Set destWs = Workbooks.Open(Filename:="https://SharePointLocationToWorkbookB.xlsm", _
                    ReadOnly:=False, UpdateLinks:=False).Worksheets("Sheet1") 'for example
    
    For Each rn In Array("Alpha", "Beta", "Charlie")
        destWs.Range(rn).Value = sourceWs.Range(rn).Value
    Next rn
    
    destWs.Parent.Close True 'save changes
    
End Sub

Upvotes: 1

Related Questions