Copy cells from one workbook to another, replace if certain criterias are mett

[Tamlin]

Hi,

I have an issue with a macro that I do not know exactly how to solve, and hope there are some clever heads out there that can help me out.

Just a quick input on what I'm currently doing:
I have a monthly report that contains loads of numbers.
These are numbers per city per month per year, and those are handed to me on a monthly basis.
To clean the data so I can use it in QlikView, I've created a workbook where I extract the essential data by copying the entire table into it.
Then I have a macro where I export the cleaned data to another workbook.

The table also contains last years figures from the same month, and these figures are more correct than the figures given to me last year.

What I wish to accomplish, is to extract both the figures from last year and this year, and copy them into the other workbook. So basically I end up with a table stating city, year and month, as well as the figures to go with it on one row, e.g.:
Oslo, May, 2010, n1, n2, n3, n4
Oslo, May, 2011, n5, n6, n7, n8

... and so on.

In the book that I'm exporting the data to, I allready have importet figures for oslo, May, 2010 as I did that last year.
As these are figures that are somewhat off, it would be:
Oslo, May, 2010, n1.1, n2.1, n3.1, n4.1.

So basically I want to replace n1.1, n2.1, n3.1, n4.1 with n1, n2, n3, n4 for "Oslo, May, 2010" and then add the figures for "Oslo, May, 2011" at the bottom of the sheet.

For copying all the cells into the first empty cell in the new workbook, I use the following macro:

Range("A4:J31").Select
Selection.Copy
Workbooks.Open Filename:="filepath\woorkbook.xlsx"
Sheets("x").Select

If WorksheetName = vbNullString Then
WorksheetName = ActiveSheet.Name
End If
With Worksheets(WorksheetName)
x1FirstRow = .Cells.Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByRows, xlNext).Row

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Range("A2").End(xlDown).Offset(1, 0).Select
End With
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

Hope I was able to explain my issue in a way that helps you understand, and that there are someone here that can help me out.

Thank you in advance

/Tamlin