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!!
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!!
Code:
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]