Select sheet based on sheet name and copy range of columns from one workbook to another

[kalam1989]

I have 2 workbooks with various sheets in each book. I would like to copy a certain range of columns (A:J) from source to target book (same range A:J).

Additionally, I need to copy from source book based on specific name (For e.g. I have several worksheets: Product1, Product1 Sales, Product1 Profit, Product2, Product2 Sales, Product2 Profit, Product3, Profit Product3, etc.

Please also note that the contents in the name can be a bit jumbled (Sales/Profit at the end or beginning).
So, from source book's sheet : "Product1 Sales", I need to copy the range of columns (as values) to target book's "Product1".

I just have the logic that I should be using IF statements to check sheet names, but I don't know how to link with activate workbook, copy (based on logic) and paste (based on logic). Your help is greatly appreciated!!

Sub Copy()
Dim x As Workbook
Dim y As Workbook


Dim sourceColumn As Range, targetColumn As Range
Dim OldSelection As Range
Dim ws As Worksheet, flg As Boolean


Dim blnReplace As Boolean
Dim sht As Object


  ' Remember selection
  Set OldSelection = Selection


blnReplace = True
For Each ws In Sheets
    If InStr(ws.Name, "Sales") = 0 Then
        ws.Select blnReplace
        blnReplace = False


        If LCase(ws.Name) Like "*Product1*" Then
            ws.Select Not flg
            flg = True


            'Set sourceColumn = Workbooks("Source.xlsx").Worksheets("Product1 Sales").Columns("A:J")
             Set targetColumn = Workbooks("Target.xlsm").Worksheets("Product1").Columns("A:J")


            'Copy values
             targetColumn = sourceColumn.Value


        End If


    Else


    End If


    ' Restore selection
    OldSelection.Select


[FONT=Helvetica Neue][FONT=Verdana]End Sub[/FONT][/FONT]