VBA Debug code for using a partial name match to copy and paste between active/open workbooks

Question

I found this code in another thread here but can't get it working in my book... What I'm trying to achieve is... The macro to be called from a wb called "SHIFT REPORT*" which looks for and switches to a wb called "PlayerTransactionReport*" to copy some data before switching back to the SHIFT REPORT and pasting it in.

The code I have is:

Sub Import_Data()

    Dim wb As Workbook
    Dim ShiftReport As Workbook
    Dim PlayerTransactionReport As Workbook

    Set ShiftReport = ThisWorkbook
    For Each wb In Workbooks
     If Left(wb.Name, 23) = "PlayerTransactionReport" Then Set PlayerTransactionReport = wb
    Next

    Sheets("Sheet1").Select
    Columns("A:Z").Select
    Selection.Copy

    Set PlayerTransactionReport = ThisWorkbook
    For Each wb In Workbooks
        If Left(wb.Name, 10) = "ShiftReport" Then Set ShiftReport = wb
    Next

    Sheets("Data").Select
    Range("A1").Select
    ActiveSheet.Paste

End Sub

Currently, it's not setting the PlayerTransactionReport to the active wb but throughout the process of debugging this by myself I've had various degrees of success, but I fear that this one might have between me, Please Help!

Thanks, Stuart

Why are you *'setting the PlayerTransactionReport to the active wb'* when you set it earlier? — , Jul 30 '18 at 12:38

Vityata · Answer 1 · 2018-07-30T12:39:56.550

You have to refer to the Parent Worksheet, whenever you are refering to Sheets() and Columns():

Sub Import_Data()

    Dim wb As Workbook
    Dim ShiftReport As Workbook
    Dim PlayerTransactionReport As Workbook

    Set ShiftReport = ThisWorkbook
    For Each wb In Workbooks
        If Left(wb.Name, 23) = "PlayerTransactionReport" Then Set PlayerTransactionReport = wb
    Next



    PlayerTransactionReport.Sheets("Sheet1").Select
    Columns("A:Z").Select
    Selection.Copy

    Set PlayerTransactionReport = ThisWorkbook
    For Each wb In Workbooks
        If Left(wb.Name, 10) = "ShiftReport" Then Set ShiftReport = wb
    Next

    PlayerTransactionReport.Sheets("Data").Select
    Range("A1").Select
    ActiveSheet.Paste

End Sub

If you do not refer to the parent worksheet, then the ActiveSheet or the sheet where the code is, is the one referred.

As a next step you can improve the following 2 points:

How to avoid using Select in Excel VBA
Check whether the PlayerTransactionReport is not Nothing, before calling it:

If Not PlayerTransactionReport Is Nothing Then
    PlayerTransactionReport.Sheets("Sheet1").Select
    Columns("A:Z").Select
    Selection.Copy
End If

score 0 · Answer 2 · answered Jul 30 '18 at 12:47

Stop using Select and Activate.

Sub Import_Data()

    Dim w As long
    Dim PlayerTransactionReport As Workbook, ShiftReport As Workbook

    Set ShiftReport = ThisWorkbook
    For w = 1 to Workbooks.count
        If Left(Workbooks(w).Name, 23) = "PlayerTransactionReport" Then
            Set PlayerTransactionReport = Workbooks(w)
            exit for
        end if
    Next w

    if w > Workbooks.count then
        debug.print "cannot find PlayerTransactionReport"
        exit sub
    end if

    PlayerTransactionReport.workSheets("Sheet1").Columns("A:Z").Copy _
      destination:=ShiftReport.workSheets("Data").Range("A1").

End Sub

VBA Debug code for using a partial name match to copy and paste between active/open workbooks

2 Answers2