VBA Excel - How to transfer Values of Named Ranges

Question

Thank you in advance for any help. My knowledge is Beginner Level. I can read code but struggle with writing.

Also, I assume there is always a better (more efficient) way to write code.

The solution is a combination of several objectives:
1. Command button with assigned macro (finished)
2. Error handling (have a little code for this)
3. Identify second Workbook to transfer from (have code for this)
4. Copy & Paste values of 90+ Named Ranges into Workbook A (caveman code using macro recorder)
5. Copy & Paste objects (pictures) of 5 Name Ranges into Workbook A (haven't gotten this far)
6. User Feedback (Transfer Successful or Transfer Failed with error message)

Code: (skipping objective 1)

Sub Button_Transfer_FromOlderVersion()

' Start of Error Handling
    On Error GoTo Errorcatch

' Declare string variable and use current open workbook filename as value
    Dim WorkbookNameNew As String
    WorkbookNameNew = ThisWorkbook.Name

' Declare string variable for 2nd workbook not yet identified
    Dim WorkbookNameOld As String

' Find out the name of the 2nd workbook
' Declare string variable for finding and separating the filename from the path
    Dim sFileName As String

' Show the open dialog and pass the selected file name to the string variable "sFileName"
    sFileName = Application.GetOpenFilename

' If the user cancels finding the workbook file then exit subroutine
    If sFileName = "False" Then Exit Sub

' Troubleshooting: Show me the filename with path of Workbook B  
    MsgBox sFileName

' Troubleshooting: Show me the filename of Workbook A  
    MsgBox WorkbookNameNew

' Open Workbook B which the user just selected
    Workbooks.Open Filename:=sFileName

' Separate the filename from the path for Workbook B
    WorkbookNameOld = Dir(sFileName)

' Troubleshooting: Show me the filename of Workbook B 
    MsgBox WorkbookNameOld

' Make sure Workbook B is the active workbook
    Windows(WorkbookNameOld).Activate

' Make sure the correct worksheet is active
    Worksheets("WorksheetName").Activate

' Select and copy the value of the first Named Range
    Range("NamedRange01").Select
    Selection.Copy

' Make Workbook A the active workbook
    Windows(WorkbookNameNew).Activate

' Select the corresponding Named Range in Workbook A
    Range("NamedRange01").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

' User Feedback of successful transfer and name of Workbook B
    MsgBox ("TRANSFER COMPLETED FROM:" & " " & WorkbookNameOld)


Exit Sub


' Finish Error Handling
Errorcatch:
MsgBox Err.Description

End Sub

My apologies if the spacing, indenting, and commenting are not optimized for reading. I'm still learning best practices.

Please note: some name ranges are spelled different and I need to map them so that the copy/paste is accurate.

Also, instead of using copy/paste wouldn't it be better to list all the Named Ranges in an Array with associated variables? And wouldn't it be better to copy all the values and objects into the Array and then switch to Workbook A and paste all the content?

Thanks again for your help.

Answer 1

You don't need to bother with activating the workbook or sheets before copying or pasting. That just slows things down. In addition you can turn of screen updating and calcluation to speed things up even more.

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Windows(WorkbookNameOld).Worksheets("WorksheetName").Range("NamedRange01").Copy
Windows(WorkbookNameNew).ActiveSheet.Range("NamedRange01").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic