Can someone help me get started or point me in the right direction

[GoBrowns!]

I have never written a macro in Excel, and I need big-time help.

I need a macro that will take the following fields from one worksheet:

Date
Average SORT Score
Average SET IN ORDER Score
Average SHINE Score
Average STANDARDIZE Score
Average SUSTAIN Score
Average TOTAL Score

Then, I need the macro to place these fields in a new worksheet in a
horizontal table.

Once that is complete, I would like for a macro to clear the initial
worksheet, then allow me to re-run the first macro so that I can have the
same fields placed onto the second worksheet under the data that has already
collected there. In short, I want my second worksheet to look like this:

DATE SORT SET IN ORDER SHINE STANDARDIZE SUSTAIN TOTAL
Jan 09
Feb 09
Mar 09

And so on, with all data points filled in.

Is this possible? And if so, how the heck do I go about doing it?

I am willing to provide any and all needed additional information. THANKS SO
MUCH FOR YOUR HELP!!!!

 

[Dave Peterson]

First, how about a slight change in your worksheets.

Create one sheet that contains the input cells and (soon to be) output cells.

Then create another sheet that contains all the calculations that you need.

Then you type in all the input cells and the macro takes each line of data,
populates the calc sheet, calculates, and then extracts the resulting cells that
you want and places them onto the same row as the input values on that input
sheet.

If that sounds reasonable, here's a macro that may get you started...

Seems like a reasonable approach to me:

You'll have to change this to use the right cells on the calculation sheet:

Option Explicit
Sub testme()

Dim InputWks As Worksheet
Dim CalcWks As Worksheet
Dim myRng As Range
Dim myCell As Range

Set InputWks = Worksheets("sheet1")
Set CalcWks = Worksheets("sheet2")

With InputWks
'headers in row 1
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

With CalcWks
For Each myCell In myRng.Cells
'populate the CalcWks with values from the input sheet
.Range("a1").Value = myCell.Value
.Range("x99").value = mycell.offset(0,1).value
.range("iv323").value = mycell.offset(0,2).value

'do the calculation
Application.Calculate

'take some values back from the calcwks to the input sheet
myCell.Offset(0, 3).Value = .Range("b1").Value
myCell.Offset(0, 4).Value = .Range("c1").Value
myCell.Offset(0, 5).Value = .Range("d1").Value
Next myCell
End With

End Sub

You'll have to change all the addresses that get populated and add more lines as
you need them. And same thing with the "after calc" portion. You'll want to
put them in the cells you want.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[GoBrowns!]

Hi Dave;

I don't have any leeway in how the worksheet is set up, but I may have
miscommunicated something. I don't need the macro to average the figures that
I need placed in a new worksheet - those are simply the names of the figures.

What part of this code would I need to just move my figures from one sheet
to another? I also need to be able to delete data from Worksheet 1 once I
move it, then input new data and send it to the cells directly below the
cells I already sent over.

Thanks for the help - if you need more info from me, please let me know!!

 

[Dave Peterson]

Option Explicit
Sub testme()

dim DataWks as worksheet
dim TabWks as worksheet
dim NextRow as long

set datawks = worksheets("sheetname that user types in")
set tabwks = worksheets("sheetname that looks like a table")

with tabwks
'find the next available row based on the stuff in column A
nextrow = .cells(.rows.count,"A").end(xlup).row + 1
end with

with datawks
'copy the value in A1 to the next row in column A and clear A1
tabwks.cells(nextrow,"A").value = .range("a1").value
.range("A1").clearcontents

'copy the value in x99 to the next row in column B and clear X99
tabwks.cells(nextrow,"B").value = .range("x99").value
.range("x99").clearcontents

'and so forth
end with

End Sub

(Untested, uncompiled. watch for typos.)

 

[GoBrowns!]

Dave;

Here is what I put in:

Option Explicit
Sub Zone1()

Dim DataWks As Worksheet
Dim TabWks As Worksheet
Dim NextRow As Long

Set DataWks = Worksheets("Zone 1-Color Crews")
Set TabWks = Worksheets("Tables")

With TabWks
'find the next available row based on the stuff in column A
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With

With DataWks
'copy the value in C7 to the next row in column A and clear C7
TabWks.Cells(NextRow, "A").Value = .Range("C7").Value
.Range("C7").ClearContents

'copy the value in D7 to the next row in column A and clear D7
TabWks.Cells(NextRow, "B").Value = .Range("D7").Value
.Range("D7").ClearContents

'copy the value in F7 to the next row in column A and clear F7
TabWks.Cells(NextRow, "C").Value = .Range("F7").Value
.Range("F7").ClearContents

'copy the value in G7 to the next row in column A and clear G7
TabWks.Cells(NextRow, "D").Value = .Range("G7").Value
.Range("G7").ClearContents

'copy the value in H7 to the next row in column B and clear H7
TabWks.Cells(NextRow, "E").Value = .Range("H7").Value
.Range("H7").ClearContents

'copy the value in I7 to the next row in column B and clear I7
TabWks.Cells(NextRow, "F").Value = .Range("I7").Value
.Range("I7").ClearContents
End With
End Sub

I keep getting a run-time erro: "Application-defined or object defined
error." I tried to look this up online, and all I found was that this occurs
when you are trying to copy/paste from one worksheet to another in the same
workbook. Is there something I can do about that?

Also, just to be sure: the values I put in to be copied are from my
worksheet the users types into... the columns represent the columns in the
worksheet that has the table I am pasting to. Right?

Thanks for the help!!!!!

 

[Dave Peterson]

The code ran fine for me.

I don't have any good guesses.

What line causes the error?

Is the worksheet getting the data protected? Do you have merged cells?

Just random guesses

Dave;

Here is what I put in:

Option Explicit
Sub Zone1()

Dim DataWks As Worksheet
Dim TabWks As Worksheet
Dim NextRow As Long

Set DataWks = Worksheets("Zone 1-Color Crews")
Set TabWks = Worksheets("Tables")

With TabWks
'find the next available row based on the stuff in column A
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With

With DataWks
'copy the value in C7 to the next row in column A and clear C7
TabWks.Cells(NextRow, "A").Value = .Range("C7").Value
.Range("C7").ClearContents

'copy the value in D7 to the next row in column A and clear D7
TabWks.Cells(NextRow, "B").Value = .Range("D7").Value
.Range("D7").ClearContents

'copy the value in F7 to the next row in column A and clear F7
TabWks.Cells(NextRow, "C").Value = .Range("F7").Value
.Range("F7").ClearContents

'copy the value in G7 to the next row in column A and clear G7
TabWks.Cells(NextRow, "D").Value = .Range("G7").Value
.Range("G7").ClearContents

'copy the value in H7 to the next row in column B and clear H7
TabWks.Cells(NextRow, "E").Value = .Range("H7").Value
.Range("H7").ClearContents

'copy the value in I7 to the next row in column B and clear I7
TabWks.Cells(NextRow, "F").Value = .Range("I7").Value
.Range("I7").ClearContents
End With
End Sub

I keep getting a run-time erro: "Application-defined or object defined
error." I tried to look this up online, and all I found was that this occurs
when you are trying to copy/paste from one worksheet to another in the same
workbook. Is there something I can do about that?

Also, just to be sure: the values I put in to be copied are from my
worksheet the users types into... the columns represent the columns in the
worksheet that has the table I am pasting to. Right?

Thanks for the help!!!!!