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, 2023 in Others by Kithuzzz
• 38,010 points
248 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, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

VBA - Build a Two-Column Array By Looping Through one Array with a Specific Criteria and Selecting From Another Array

As already mentioned as comment: Loop over ...READ MORE

answered Mar 24, 2023 in Others by Kithuzzz
• 38,010 points
254 views
0 votes
0 answers

Copying values from multiple excel files to a single one with python

I want to replicate the values from ...READ MORE

Apr 11, 2023 in Others by Kithuzzz
• 38,010 points
661 views
+1 vote
1 answer

Between cyber security and CCNA profession which one is best in terms of time to become an expert and salary payment

CCNA professional is more inclined towards the ...READ MORE

answered Dec 18, 2019 in Others by Pri
1,626 views
0 votes
1 answer

Copy data with filter applied using Excel VBA

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

answered Mar 19, 2023 in Others by Kithuzzz
• 38,010 points
264 views
0 votes
1 answer

Export multiple worksheets without formula with the ability to select exact sheets and location

Try this: Sub ExportSheets() Dim ...READ MORE

answered Mar 24, 2023 in Others by Kithuzzz
• 38,010 points
298 views
0 votes
1 answer

How to find all internal ranges in a specified range

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

answered Mar 27, 2023 in Others by narikkadan
• 63,420 points
358 views
0 votes
1 answer

Copying an existing row which also copies the checkbox with its code

You can make a new checkbox object ...READ MORE

answered Apr 2, 2023 in Others by Kithuzzz
• 38,010 points
403 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, 2023 in Others by narikkadan
• 63,420 points
224 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