Adding results to a list? 1

[sprintcar]

I've got a spreadsheet that calculates 2 values for data entered along with a drawing number. I would like to create a list that tracks the drawing number and the resulting values automatically so there is a file of work that has been done.

I did a Macro to copy the dwg number and values, then paste (Paste Special, Values, transpose) them under the header. Ctrl +s activates the macro. It works fine but overwrites the same line with each new data set. I've tried 'relative reference' but can't get that to work without placing the cursor on the next cell of the list and making it active. The Help archive is useless.
HELP!
THANKS!!

"If A equals success, then the formula is: A = X + Y + Z, X is work. Y is play. Z is keep your mouth shut."
-- by Albert Einstein

 

[joerd]

As a Q&D solution you can include a line in your code before the paste special that reads something like:
[tt] ActiveSheet.Range("A1").End(xlDown).Offset(1, 0).Select[/tt]
This mimics selection of A1, then pressing End and Down arrow, then moving 1 row down. Experiment some with it.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[pmover]

sprintcar,

the solution presented by joerd will work; however, if there is no data in "A1" and in "A2", then either an error will occur (cannot select beyond last row in workbook) or data will be written in last row of workbook - i have not tested the code.

btw, myself and another engineer did the same activity (gathered rotating machinery performance test data) back in the early/mid 90s starting with xl ver 5. i've since converted the macro to vba and will post code tomorrow - if i do not forget!

perhaps the skilled joerd will offer a solution to posting data; especially when no data exist in log.

good luck!
-pmover

 

[joerd]

Another - less quick&dirty - way is to use ActiveSheet.UsedRange.Rows.Count to get the total number of rows used, then set the activecell using that number.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[sprintcar]

pmover... did you forget??

"If A equals success, then the formula is: A = X + Y + Z, X is work. Y is play. Z is keep your mouth shut."
-- by Albert Einstein

 

[pmover]

yes, sorry! will attempt again tomorrow or maybe later today.

crs frequently occurs . . .

-pmover

 

[pmover]

Sprintcar

rev your engine a little! . . .

improvements can be made in simplifying the code here, but it does work.

the range "harddata" is a named range in top row (several continuous columns wide) on sheet named "Unit Data". the code simply copys the data and paste values only at bottom of current list. The code is activated by a button on another sheet. hope this helps and good luck!

' StoreData Macro
' Macro recorded 8/9/94 by DG
'
' Keyboard Shortcut: Ctrl+s
'
' This macro takes the input & results data and stores
' the data on the Unit Data Spreadsheet. This data is
' in turn used in historically analyzing the engine/compressor
' performance or operation. The information is mainly used
' by Engine Analysis and Reliability Engineering.
'
Sub StoreData()
Application.ScreenUpdating = False
Sheets("Unit Data").Select
Range("HardData").Select
Selection.Copy
kount = Application.Cells(3, 2)
If kount = 0 Then
Application.Goto Reference:="R7C1"
Selection.Offset(1, 0).Select
Else
Application.Goto Reference:="R7C1"
Selection.End(xlDown).Select
Selection.Offset(1, 0).Select
End If
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Application.Goto Reference:="R1C1"
Sheets("Calcs").Select
Application.ScreenUpdating = True
End Sub

good luck!
-pmover

 

[bltseattle]

Sprintcar,
Here's an easy solution to try. Record a macro similar to your initial approach but use the INSERT-copied-cells so that the previous values move over as you insert the new values. Or if you're transposing, first insert blank cells, moving your previous data over, then execute your copy routine. This approach usually works for me and I don't have to get into the VB code beyond using the recorder.