Copy & Paste sections of data

[Christine Wilso]

I receive data each day exported into an excel file. In column A ther
is a list of Areas, in column B a list of months from April to whateve
the month we are in now, and C,D & E contain values.
For example:
A B C D E
Liverpool April x y z
Liverpool May a x b
Manchester April c z z
Manchester May b c d
In another spreadsheet I have these same areas with all 12 month
listed from April to March.
Rather than copying and pasting each individual section from on
worksheet to another, how do I paste the data from column C,D & E fro
the chart above placing it next to the appropriate month & town an
leaving blank the months we have not reached yet.

As I have to do this every day I can then build a macro around it, bu
I don't know how to do the copy paste bit yet

 

[Dave Peterson]

Does this mean that the data on the "to" worksheet goes into columns C:E?

If yes, I think this works. (try it on a copy of your data--or don't save the
workbooks if it's wrong).

Option Explicit
Sub testme01()

Dim ToWks As Worksheet
Dim FromWks As Worksheet
Dim res As Variant
Dim myCell As Range
Dim myFRng As Range
Dim myTRng As Range

Set FromWks = Workbooks("book1.xls").Worksheets("From")
Set ToWks = Workbooks("book2.xls").Worksheets("to")

With FromWks
Set myFRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

With ToWks
Set myTRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myFRng.Cells
If Application.CountA(myCell.Offset(0, 2).Resize(1, 3)) > 0 Then
res = Application.Evaluate("match(1,(" _
& myTRng.Address(external:=True) & "=" _
& myCell.Address(external:=True) & ")*" _
& "(" & myTRng.Offset(0, 1).Address(external:=True) _
& "=" & myCell.Offset(0, 1).Address(external:=True) & "),0)")

If IsError(res) Then
'no match found
MsgBox "No match for data on row: " & myCell.Row
Else
myTRng(res).Offset(0, 2).Resize(1, 3).Value _
= myCell.Offset(0, 2).Resize(1, 3).Value
End If
End If
Next myCell

End Sub

Don't forget to change the worksheet names to match.

It's actually evaluating a worksheet formula that looks like this:

=MATCH(1,(sheet1!$A$1:$A$4=sheet2!$a$1)*(sheet1!$B$1:$B$4=sheet2!$b$1),0)

If that comes back as a number, then there was a match on both column A and B.

If it's an error, then there's not a match.