Pasting to a destination by comparing cells

gopa

New Member
Joined
Oct 23, 2002
Messages
32
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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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
Joined
Oct 23, 2002
Messages
32
Thanks, but what are you proposing that I enter in Sheet 2 A1?
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
="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
Joined
Oct 23, 2002
Messages
32
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
 

Forum statistics

Threads
1,144,148
Messages
5,722,768
Members
422,456
Latest member
lipa

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top