Macro to insert data in specific cell based on criteria

**SAsplin** · 05-31-2011, 11:29 AM

I'm re-posting this in the hope that someone can help.

In the example enclosed I'd like to add some code to a command button on Sheet 2 so that the data on Sheet 1 cell M27 is entered in to cell C12 on Sheet 2 because of the criteria set out in Sheet 1 cells H2 (Week) and M2 (Identifier) - the data is inserted in to the relevant point on the matrix in Sheet 2.

Any help appreciated.

Thanks Steve

**MarvinP** · 05-31-2011, 11:40 AM

Hi SAsplin,
If you were to turn on the Record Macro and simply copy and paste like you suggest, you would get the following macro.

Sub Macro1()
    Sheets("Sheet1").Select
    Range("M27").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("C12").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

Then edit your command button to fire this macro when you clicked on it and you would be done.

Is that all you wanted?

**SAsplin** · 05-31-2011, 11:47 AM

Thanks for the reply -

That would simply copy and paste the value from cell M27 on sheet 1 to cell C12 on Sheet 2.

Sheet 1 will constantly change - the unique identifier, the week and the value in M27 will change.

I'd like the macro to look up the week number (H2) and the identifier (M2) and insert value in cell M27 into the relevent place in the matrix on Sheet 2 - weeks are on row 6 and identifiers in column A.

Cheers

**MarvinP** · 05-31-2011, 12:31 PM

Hi,
I get it now. Try calling this code from you button click,

Sub Copy2Sheet2()
Dim WeekNum As Double
Dim UniqueID As String
Dim TotalUnits As Double
Dim Sht2Row As Double
Dim Sht2Col As Double

Sheets("Sheet1").Select
WeekNum = Cells(2, "H").Value
UniqueID = Cells(2, "M").Text
TotalUnits = Cells(27, "M").Value

Sheets("Sheet2").Select
Sht2Col = Application.WorksheetFunction.Match(WeekNum, ActiveSheet.Range("B6:BA6"), 0)
Sht2Row = Application.WorksheetFunction.Match(UniqueID, ActiveSheet.Range("A7:A100"), 0)

ActiveSheet.Cells(Sht2Row + 6, Sht2Col + 1) = TotalUnits

End Sub

**SAsplin** · 06-01-2011, 06:03 AM

Originally Posted by MarvinP

Hi,
I get it now. Try calling this code from you button click,

Sub Copy2Sheet2()
Dim WeekNum As Double
Dim UniqueID As String
Dim TotalUnits As Double
Dim Sht2Row As Double
Dim Sht2Col As Double

Sheets("Sheet1").Select
WeekNum = Cells(2, "H").Value
UniqueID = Cells(2, "M").Text
TotalUnits = Cells(27, "M").Value

Sheets("Sheet2").Select
Sht2Col = Application.WorksheetFunction.Match(WeekNum, ActiveSheet.Range("B6:BA6"), 0)Sht2Row = Application.WorksheetFunction.Match(UniqueID, ActiveSheet.Range("A7:A100"), 0)

ActiveSheet.Cells(Sht2Row + 6, Sht2Col + 1) = TotalUnits

End Sub

I've added this code to the command button and it comes back with the following runtime error 1004:

'Unable to get the Match property of the WorksheetFunction class'

**SAsplin** · 06-01-2011, 07:30 AM

Ok. I know I'm a bit of a thickie with this but...

The above code works perfectly if the command button is on Sheet1 but it's on Sheet2. How do I change the above code for this?