Copy and Replace Named Range Excel VBA

0
votes

I am trying to copy a named range from an external workbook to replace the named range in my active workbook.
I am getting a type mismatch error which I figure maybe because I did not call my source a range. Could you please help me out with my syntax? Code below:

    Sub ImportLogInfo()
    Dim wkbCrntWorkBook As Workbook
    Dim wkbSourceBook As Workbook
    Dim rngSourceRange As Range
    Dim rngDestination As Range

    Set wkbCrntWorkBook = ActiveWorkbook

            Set wkbSourceBook = Workbooks.Open(Filename:="C:\Users\Documents\Proposal Log Feeder\Log Feeder A.xlsb", ReadOnly:=True)

            Set rngSourceRange = wkbSourceBook.Sheets("Log").Range("DBrange")
            wkbCrntWorkBook.Activate
            Set rngDestination = wkbCrntWorkBook.Sheets("Log").Range("DBrange")
            rngSourceRange.Copy rngDestination
            rngDestination.CurrentRegion.EntireColumn.AutoFit
            wkbSourceBook.Close False
End Sub

Ranges have the same name, DBrange. If possible, I would also only like copy columns from A:AD.
If this is not possible, that's fine. Thanks in advance.

2
vbaexcel
What line creates the error? - jonyfries
Set rngDestination = wkbCrntWorkbook.Sheets("Log").Range("DBrange") - Remi
@jonyfries In fact, I would like to get rid of the msoFileDialogOpen and just have the a button click open the external file, get the information needed and close the file without user input. See my edit. - Remi

2 Answers

1
votes

Declare value of DBrange

dim DBrange as string
    DBrange = "A:AD"

Declare workbook

dim wkbSourceBook as workbook
    wkbSourceBook = Workbook("NameOfWorkBook")
1
votes

Make sure your destination worksheet is named Log and has a range named DBrange.

The code below successfully copied ranges like you asked, as long as both the source and destination worksheets were setup correctly. The only way I could get an error similar to what you are experiencing is if I either changed the name of the destination worksheet or deleted the named range DBrange from the destination worksheet using Name Manager.

Copy/paste this code into the ThisWorkbook module of your destination workbook, and run it. The code assumes that DBrange starts in column A.

Sub ImportLogInfo()
'copy this code into ThisWorkbook
    Dim wbCur As Workbook
    Dim wbSrc As Workbook
    Dim rngSrc As Range
    Dim rngDest As Range
    Dim strSrcFName As String

    'change this value to suit your source
    strSrcFName = "C:\Users\Documents\Proposal Log Feeder\Log Feeder A.xlsb"

    Set wbCur = Me
    Set wbSrc = Workbooks.Open(Filename:=strSrcFName, ReadOnly:=True)
    Set rngSrc = wbSrc.Sheets("Log").Range("DBrange")

    'reset the source range to subset columns A:AD of "DBrange" (30 = column AD)
    Set rngSrc = rngSrc.Range(Cells(1, 1), Cells(rngSrc.Rows.Count, 30))

    Set rngDest = wbCur.Sheets("Log").Range("DBrange")
    rngSrc.Copy rngDest
    rngDest.CurrentRegion.EntireColumn.AutoFit
    wbSrc.Close False
End Sub