Pasting to a destination by comparing cells

[gopa]

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.

 

[DonkeyOte]

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]

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

 

[DonkeyOte]

="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]

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