Copying and pasting from one workbook to another doesn t work

Question

I'm attempting a straightforward task. The following code is meant to copy specified ranges from one worksheet to another, however when I run it, nothing happens; nothing is copied. (Copying occurs in the final section of Sub.) I think there might be an issue with worksheets or workbooks, but I'm fairly new to VBA, so I can't be sure.

Function getHeaderRange(searched As String, ws As Worksheet) As Range
    Dim colNum
    Dim cellLength
    colNum = WorksheetFunction.Match(searched, ws.Range("5:5"))
    cellLength = ws.Range(ws.Cells(5, colNum), ws.Cells(5, colNum)).MergeArea.Count
    Set getHeaderRange = Range(ws.Cells(6, colNum), ws.Cells(6, colNum + cellLength - 1))
End Function

Function getDataRange(searched As String, hRange As Range) As Range
    Dim column: column = WorksheetFunction.Match(searched, hRange) + hRange.column - 1
    Set getDataRange = Range(Cells(6, column), Cells(6, column))
    Debug.Print (hRange.Worksheet.Parent.Name & "Sheet: " & hRange.Worksheet.Name)
    Set getDataRange = getDataRange.Offset(1, 0)
    Set getDataRange = getDataRange.Resize(8)
    
End Function

Sub main()
    Dim srcWs As Worksheet: Set srcWs = Workbooks("Period end open receivables, step 5").Sheets(1)
    Dim trgWs As Worksheet: Set trgWs = ThisWorkbook.Sheets("Obiee")
    
    Dim searched As String
    Dim hSearched As String
    searched = "Magazines, Merchants & Office"
    
    Dim srcRange As Range: Set srcRange = getHeaderRange(searched, srcWs)
    Dim trgRange As Range: Set trgRange = getHeaderRange(searched, trgWs)
    
    Dim cocd() As Variant
    Dim i As Integer
    cocd = getHeaderRange("Magazines, Merchants & Office", trgWs)
    For i = 1 To UBound(cocd, 2)
        hSearched = cocd(1, i)
        getDataRange(hSearched, srcRange).Copy
        getDataRange(hSearched, trgRange).PasteSpecial xlPasteValues
    Next i
End Sub

When I change the last lines to:

    For i = 1 To UBound(cocd, 2)
        hSearched = cocd(1, i)
        srcWs.Activate
        getDataRange(hSearched, srcRange).Copy
        trgWs.Activate
        getDataRange(hSearched, trgRange).Select
        ActiveSheet.Paste
    Next i

It works just fine but I really would like to avoid this approach and find out what's wrong with the first one.

narikkadan · Answer 1 · Feb 18, 2023

Your ranges aren't fully qualified.

Excel will make a best guess as to which worksheet the range in question is located on when they are not qualified, typically using the worksheet that is presently active. As you alter the worksheet that is currently active, your workaround is effective.

This line needs to be fully qualified:

cellLength = Range(ws.Cells(5, colNum), ws.Cells(5, colNum)).MergeArea.Count

So it'll become:

Function getHeaderRange(searched As String, ws As Worksheet) As Range
    Dim colNum
    Dim cellLength
    colNum = WorksheetFunction.Match(searched, ws.Range("5:5"))
    cellLength = ws.Range(ws.Cells(5, colNum), ws.Cells(5, colNum)).MergeArea.Count
    Set getHeaderRange = ws.Range(ws.Cells(6, colNum), ws.Cells(6, colNum + cellLength - 1))
End Function

Also, this line is not qualified at all:

Set getDataRange = Range(Cells(6, column), Cells(6, column))

So it'll become:

Function getDataRange(searched As String, hRange As Range) As Range
    Dim column: column = WorksheetFunction.Match(searched, hRange) + hRange.column - 1
    Dim ws As Worksheet: Set ws = hRange.Worksheet
    Set getDataRange = ws.Range(ws.Cells(6, column), ws.Cells(6, column))
    Debug.Print (ws.Parent.Name & "Sheet: " & ws.Name)
    Set getDataRange = getDataRange.Offset(1, 0)
    Set getDataRange = getDataRange.Resize(8)
End Function