Copying Between Workbooks with Varying Names

Question

I am attempting to copy/paste values from one open workbook to another. Neither of the workbooks will have static names, so there will be no name consistency. Both of my workbooks will be open and will be the only open files.

Can someone help me fix this code to work when I don't know the file names?

Range("M7:R19").Select
Selection.Copy
Windows("new template.xlsm").Activate
Range("M7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Windows("old template.xlsm").Activate
Range("S7:AT16").Select
Application.CutCopyMode = False
Selection.Copy
Windows("new template.xlsm").Activate
Range("U7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

[this would be a good place to start](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — cybernetic.nomad, Jan 13 '20 at 21:00
Unfortunately, no pattern to names. And I do not have authority to change them. I have thousands of these to update and am totally new to VBA. — MM_1980, Jan 13 '20 at 21:05
You'll need *some* sort of logic to identify the workbooks programmatically. Is there any? Doesn't necessarily have to be file name. — BigBen, Jan 13 '20 at 21:09
How would you know from which file to copy from when you don't know anything about the two names. What makes that one file is the one you copy **from** and what makes it that the other one is the `Workbook` you paste **to**. What is there that will make them being identified as such? Any pattern in `Worksheets` names? Basically, is there anything to distinguish one from the other? — JvdV, Jan 13 '20 at 21:16
The 'old template' name will end in numbers. The 'new template' name will end in letters. — MM_1980, Jan 13 '20 at 21:17

JvdV · Answer 1 · 2020-01-13T21:33:31.150

You'll have to create two Workbook variables, to distinquish between the one that you want to copy from and where you want to paste to. So something to get you started would be (since these are the only two workbooks open at run-time):

Sub Test()

Dim ws As Workbook, wbCopy As Workbook, wsPaste As Workbook

For Each wb In Application.Workbooks
    If IsNumeric(Right(wb.Name, 1)) Then
        Set wbCopy = wb
    Else
        Set wbPaste = wb
    End If
Next wb

'Continue coding... Below is just an option:
wbPaste.Worksheets(1).Range("U7:AV16").Value = wbCopy.Worksheets(1).Range("S7:AT16").Value
'Same thing for other ranges....  

End Sub

The second part of the code is for you to consider. I do not know which sheet you refer to on either workbook, nor do I know if you really need to copy/paste. In my example I went with the Worksheet with index 1 and I assumed a simple Value transfer may be what you actually want.

But these last two things are for you to consider.

There will be multiple worksheets that will all need to be done the same way. Thank you. This will give me a place to start from. — MM_1980, Jan 13 '20 at 21:55

score 1 · Accepted Answer · answered Jan 13 '20 at 21:49

Alternative method using Like operator to test for source/destination workbooks. Also provides a way to define source/destination ranges that can be looped through for ease of debugging and updating later. Code heavily commented for clarity.

Sub tgr()

    Dim wb As Workbook
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet

    'Check if exactly 2 workbooks are currently open
    If Application.Workbooks.Count <> 2 Then
        MsgBox "ERROR - There are [" & Application.Workbooks.Count & "] workbooks open." & Chr(10) & _
               "There must be two workbooks open:" & Chr(10) & _
               "-The source workbook (old template)" & Chr(10) & _
               "-The destination workbook"
        Exit Sub
    End If

    For Each wb In Application.Workbooks
        If wb.Name Like "*#.xls?" Then
            'Workbook name ends in number(s), this is the source workbook that will be copied from
            'You'll need to specify which sheet you're working with, this example code assumes the activesheet of that workbook
            Set wsSource = wb.ActiveSheet
        Else
            'Workbook name does not end in number(s), this is the source workbook that will be pasted to
            'You'll need to specify which sheet you're working with, this example code assumes the activesheet of that workbook
            Set wsDest = wb.ActiveSheet
        End If
    Next wb

    'Check if both a source and destination were assigned
    If wsSource Is Nothing Then
        MsgBox "ERROR - Unable to find valid source workbook to copy data from"
        Exit Sub
    ElseIf wsDest Is Nothing Then
        MsgBox "ERROR - Unable to find valid destination workbook to paste data into"
        Exit Sub
    End If

    'The first dimension is for how many times you need to define source and dest ranges, the second dimension should always be 1 to 2
    Dim aFromTo(1 To 2, 1 To 2) As Range
    'Add source copy ranges here:                       'Add destination paste ranges here
    Set aFromTo(1, 1) = wsSource.Range("M7:R19"):       Set aFromTo(1, 2) = wsDest.Range("M7")
    Set aFromTo(2, 1) = wsSource.Range("S7:AT16"):      Set aFromTo(2, 2) = wsDest.Range("U7")
    'Set aFromTo(3, 1) = wsSource.Range("M21:R33"):      Set aFromTo(3, 2) = wsDest.Range("M21")    'Example of a third copy/paste range - Dim aFromTo(1 to 3, 1 to 2)
    'Set aFromTo(4, 1) = wsSource.Range("S21:AT30"):     Set aFromTo(4, 2) = wsDest.Range("U21")    'Example of a fourth copy/paste range - Dim aFromTo(1 to 4, 1 to 2)

    'This will loop through the array of specified FromTo ranges and will ensure that only values are brought over
    Dim i As Long
    For i = LBound(aFromTo, 1) To UBound(aFromTo, 1)
        aFromTo(i, 2).Resize(aFromTo(i, 1).Rows.Count, aFromTo(i, 1).Columns.Count).Value = aFromTo(i, 1).Value
    Next i

End Sub

When I use this code, I keep getting the message that there are 3 workbooks open, even when I only have 2 open. Is it also counting the personal workbook? — MM_1980, Jan 13 '20 at 22:04
@MM_1980 yes, it also counts the personal workbook. You'll need to set it to `<>3` and add another If statement for it to ignore the personal workbook so that it's not included as a candidate for source or destination. — tigeravatar, Jan 13 '20 at 22:05
This worked like a charm as soon as I made the personal workbook changes. Thanks so much! — MM_1980, Jan 13 '20 at 22:20

Copying Between Workbooks with Varying Names

2 Answers2