If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Copy & Paste sections of data
I receive data each day exported into an excel file. In column A there
is a list of Areas, in column B a list of months from April to whatever 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 months listed from April to March. Rather than copying and pasting each individual section from one worksheet to another, how do I paste the data from column C,D & E from the chart above placing it next to the appropriate month & town and 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, but I don't know how to do the copy paste bit yet. --- Message posted from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
Copy & Paste sections of data
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. "Christine Wilso " wrote: I receive data each day exported into an excel file. In column A there is a list of Areas, in column B a list of months from April to whatever 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 months listed from April to March. Rather than copying and pasting each individual section from one worksheet to another, how do I paste the data from column C,D & E from the chart above placing it next to the appropriate month & town and 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, but I don't know how to do the copy paste bit yet. --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|