Locate the file by its Tab name NOT the File name then Paste

[ChrisOK]

Desparately need help changing the way this code locates its recipient file. Need it to ignore the file name but look for an 'open/active' file that holds a tab named "Collab" and paste into that file.

There will always be 2 files open when the analyst runs this code.
One is the source file "export.xls" and the recipient file will always have a tab called "Collab" however its file name will always vary so I need the code to ignore / remove the referencing of that file name.

Sub CopyPasteTabIntoDiffFile()
'

'-------------------------------------------------------------------------------------
' SPECIFIC STEPS:
' 1- SHOULD SELECT THE ACTIVE SOURCE FILE: "export.xls",
' 2- SELECTS THE SOURCE TAB: "Repair Data"
' 2- COPIES TAB INTO RECIPIENT FILE (which will have various .xls names)
' ...but will always hold a tab called: "Collab", therefore, 
' ...if it finds an open active file w/ a tab named: "Collab"
' 3..IF TRUE, PASTE THE "Repair Data" TAB INTO THE RECIPEINT FILE.
' >> the below code is not good because it uses a specific file name and should not
'....  refer to a file but instead, locate an open file that has a tab called "Collab" 
'-------------------------------------------------------------------------------------
'
    Sheets("Repair Data").Select
    Sheets("Repair Data").Copy After:=Workbooks( _
        "35728P_testing_01232014.xls").Sheets(8)


'----------------------------------------------------------------
' SELECTS THE ACTIVE SOURCE TAB TO COPY/PASTE INTO RECIPIENT FILE
'----------------------------------------------------------------
'
    Sheets("Repair Data").Select
    
'----------------------------------------------------------------
' SELECTS RANGE TO COPY TAB TO COPY/PASTE INTO RECIPIENT FILE
'----------------------------------------------------------------
'
    Range("C9:K17").Select
    Selection.Copy

'----------------------------------------------------------------
' SELECTS RECIPIENT TAB TO PASTE RANGE IN TO
'----------------------------------------------------------------
    Sheets("RepairRules").Select
    Range("A3").Select
    ActiveSheet.Paste
    Range("A3").Select

End Sub

You might ask, why copy the tab (and) copy a range of data?
At this point in our laborious process, the data needs to be copy/pasted,
however the full tab holding other important data will be utilized later in the process.

 

[JLGWhiz]

You can fix that problem by using a variable. I also suspect that the original intent was to copy sheet 'Repair Data' after the last sheet in to other workbook, instead of always sheet after sheet 8. See if this works for you.

Use this:

Dim wb As Workbook
 Sheets("Repair Data").Select
 For Each wb In Application.Workbooks
  If wb.Name Like "*testing*" then
   Set dWb = wb
       Sheets("Repair Data").Copy After:=dWb.Sheets(8)
       Exit For
  End If
 Next

Instead of this:

 Sheets("Repair Data").Select
    Sheets("Repair Data").Copy After:=Workbooks( _
        "35728P_testing_01232014.xls").Sheets(8)

If you want to copy after the last sheet, as I mentioned before then:

Dim wb As Workbook
Sheets("Repair Data").Select
 For Each wb In Application.Workbooks
  If wb.Name Like "*testing*" then
   Set dWb = wb
       Sheets("Repair Data").Copy After:=dWb.Sheets(Sheets.Count)
       Exit For
  End If
 Next

 

[ChrisOK]

Thx JLGWiz, I attempted this chunk of code and it works but not the way it should... here's why..

Sub CopyPasteTabIntoDiffFile()

Dim wb As Workbook
Sheets("Repair Data").Select
 For Each wb In Application.Workbooks
 If wb.Name Like "*testing*" Then
   Set dWb = wb
       Sheets("Repair Data").Copy After:=dWb.Sheets(Sheets.Count)
       Exit For
  End If
 Next

End Sub

As noted in the original code's STEPS section, I need the code to ignore the file name and strictly look for:
1 - an open file that holds a tab named: "Collab"
2 - if it locates and open file that has a tab of that name, then THIS is the destination of where the code should paste the "Repair Data" tab coming from the "export.xls" file.

Reason being: The source file will ALWAYS be called "export.xls" however, the destination file that the analyst is working at that time will always change.
Example:
SOURCE FILE NAME....TAB NAME...>>>>>>DESTINATION FILE NAME
Export.xls................"Repair Data" >>>>> Wing_Parts.xls
Export.xls................"Repair Data" >>>>> Engine_Housing.xls
Export.xls................"Repair Data" >>>>> Turbine.xls

The only CONSTANT that will always be true about the destination file is that each of those will already hold a tab called "Collab".
The code needs to verify that the (1) OPEN file that it is about to paste into (2) has a tab present called "Collab".
I'm doing this so we don't risk pasting that "Repair Data" tab into another open file that was not intended to paste into...

I tested your code with my "test" file and because the word "test" was within the file name it worked...
However, when I tested it on a file that did (not) have "test" within the filename, it crashed.
(we need to find a way to ignore the file name altogether -- and strictly look for an (1)open file, with (2) a "Collab" tab present.
These 2 criterias will confirm that it is OK to paste into that file...

Hope that makes sense?

 

[JLGWhiz]

I guess what is confusing me is that you want to look for one sheet name in a file but are posting to an entirely different sheet. If you want to locate a file with a certain sheet name then this would do that:

Dim wb Ad Workbook, fWb As Workbook, sh As Worksheet
For Each wb In Application Workbooks
    For Each sh In wb.Sheets
        If sh.Name = "Collab" then
            Set fWb = wb
            Exit For
        End If
    Next
Next

 

[ChrisOK]

when I paste the abv code into my module, the 1st 2 lines light up in red -- "Compile Error - Expected End of Statement"

 

[JLGWhiz]

when I paste the abv code into my module, the 1st 2 lines light up in red -- "Compile Error - Expected End of Statement"

Probably because I spelled As like Ad. Change to Dim wb As Workbook and it should be OK.

 

[ChrisOK]

Made the correction as follows but the "For Each wb" line is lit up in red and producing this error: "Compile error - expected end of statement"

Sub CopyExport

Dim wb As Workbook, fWb As Workbook, sh As Worksheet
For Each wb In Application Workbooks
    For Each sh In wb.Sheets
        If sh.Name = "Collab" Then
            Set fWb = wb
            Exit For
        End If
    Next
Next
End Sub