# Pasting to a destination by comparing cells

#### gopa

##### New Member
I have 2 sheets; sheet 1 is a monthly account sheet with totals in one row and the name of the month in one cell. Sheet 2 is a summary with a row for each month with month names in the first column of each row. I want to compare the name of the month in sheet 1 with the month names in sheet 2 and then paste the total row from sheet 1 against the correct named month in sheet 2. After that I delete that month's name and figures from sheet 1 and start the new month.

Pasting the values and deleting figures to start a new month is no problem, but I need some code to compare month names and choose the correct row on sheet 2.

Any ideas really appreciated.

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### DonkeyOte

##### MrExcel MVP
On 2002-10-24 05:58, gopa wrote:
I have 2 sheets; sheet 1 is a monthly account sheet with totals in one row and the name of the month in one cell. Sheet 2 is a summary with a row for each month with month names in the first column of each row. I want to compare the name of the month in sheet 1 with the month names in sheet 2 and then paste the total row from sheet 1 against the correct named month in sheet 2. After that I delete that month's name and figures from sheet 1 and start the new month.

Pasting the values and deleting figures to start a new month is no problem, but I need some code to compare month names and choose the correct row on sheet 2.

Any ideas really appreciated.

OK say that your month in sheet1 is displayed in cell A1. And the totals are in row2

On sheet2 in Column A you have your list of months - start this in row 3

Then Sheet2 A1 = "A" & match(sheet1!a1,a3:a15,0) + 2

This will give you the correct cell to paste into on Sheet2

Then Macro

Sub PASTEDATA()

PasteArea = Sheets("sheet2").Range("a1")

Sheets("sheet1").range("a2:bz2").copy
Sheets("sheet2").select
Range(PasteArea).PasteSpecial xlpastevalues

End Sub

You will need to do as a macro as opposed to a formula because you want to delete the data in Sheet1 once you have completed the month.

Does this help?

_________________
LASW10
This message was edited by lasw10 on 2002-10-24 06:11

#### gopa

##### New Member
Thanks, but what are you proposing that I enter in Sheet 2 A1?

#### DonkeyOte

##### MrExcel MVP
="A" & match(sheet1!a1,a3:a15,0) + 2

The above formula - this will return the cell address of where you want to paste the data. You could put this in the Macro but seemed to make sense to do it as a formula at the time.

#### gopa

##### New Member
LASW10

By the time I got back to checking your reply, I'd already figured out that I was just missing some brackets and a few frills!

Thanks very much for this; I was so intent on the macro for pasting and deleting values, that I never thought of a formula in the sheet for identifying the row. I append the final macro in case anyone else is interested in this. In my final version I have several areas to paste, so calling them PasteArea1,2 etc. makes it easier to duplicate the code each time.

Sheet 1 A1 shows current month and A3:H3 shows totals to copy and paste.

Sheet 2 A1 = =("B"&MATCH(Sheet1!A1,A3:A14,0)+2) and A3:A14 shows months in year.

Sub PasteData()

Dim PasteArea1 As Range
Set PasteArea1 = Sheets("Sheet2").Range("A1")
Sheets("Sheet1").Select
Range("A3:H3").Select
Selection.Copy
Sheets("Sheet2").Select
Range(PasteArea1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=False
Application.CutCopyMode = False

End Sub

Replies
1
Views
110
Replies
14
Views
394
Replies
12
Views
259
Replies
0
Views
49
Replies
3
Views
164

1,181,233
Messages
5,928,801
Members
436,629
Latest member
abc32

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back