Hello, I am trying to perform a macro that will save data from one worksheet to

Question

Hello, I am trying to perform a macro that will save data from one worksheet to another worksheet based on two sets of criteria.

Person1 Worksheet: Each day a person will be filling out information on their Person1 Worksheet, for example. At the end of their day I want them to click a button titled Update that will save the Totals (cells: P3:P5) to the appropriate locations on the Matrix Worksheet.

Matrix Worksheet: Part Number and Date are the two sets of criteria that will define where in the matrix I want to save the information from the Person1 Worksheet.

Person1 Worksheet (below)

Matrix Worksheet (below)

Here is my index match formula that currently works. I just need to develop the macro code in VBA to execute the formula.

=IF(ISERROR(INDEX('Person 1'!$M$1:$S$5,MATCH(Matrix!$M3,'Person 1'!$M$1:$M$5,0),MATCH(Matrix!O$1,'Person 1'!$S$1:$S$5,0))),0,INDEX('Person 1'!$M$1:$S$5,MATCH(Matrix!$M3,'Person 1'!$M$1:$M$5,0),MATCH(Matrix!O$1,'Person 1'!$S$1:$S$5,0)))

Help :-)

Explanation / Answer

Is there some reason you need to use that formula? If you are going to be filling in data on Matrix from Person 1 based on activating the Update button, I would just use VBA code. Depending on the size of your database, you could used something like (vague because I'm not sure where all your info is located that you need to copy:

with Worksheets("Person1")

    theDate = .Cells(Row,Column)

    thePartNumber = .Cells(Row, Column)

    theInfo = .Cells(Row, Column)

end with

With Worksheets("Matrix").Rows(1)

    theColumn = .Find(What:=theDate).Column

    theRow = .Find(What:=thePartNumber).Row

    .Cells(theRow,theColumn) = theInfo

End With

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.