Copying and pasting from one workbook to another doesn t work

0 votes

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.

Feb 18 in Others by Kithuzzz
• 35,300 points
47 views

1 answer to this question.

0 votes

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
answered Feb 18 by narikkadan
• 60,820 points

Related Questions In Others

0 votes
1 answer
+1 vote
1 answer
0 votes
1 answer

Copy data with filter applied using Excel VBA

Try this: Private Sub CommandButton1_Click() Dim ...READ MORE

answered Mar 19 in Others by Kithuzzz
• 35,300 points
43 views
0 votes
1 answer
0 votes
1 answer

How to find all internal ranges in a specified range

It is simple to compare once you ...READ MORE

answered 4 days ago in Others by narikkadan
• 60,820 points
26 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
577 views
0 votes
1 answer
0 votes
1 answer

How to expend the code to transfer data from one spreadsheet to another based on multiple criteria

 The progress bar is unnecessary. Option Explicit Sub VTest2() ...READ MORE

answered Jan 29 in Others by narikkadan
• 60,820 points
57 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP