How to insert macro results on a separate spreadsheet in row order

A

andrewc

How to I insert macro results on a separate spreadsheet in row number order?
In other words, I want the macro to put the results of its computations
in the next row that is blank each time that it is run.

here is the macro that I created:
' A Macro
' Macro recorded 1/2/2009 by Kids Supply Company
'

'
Windows("Copy of Master SO & PO List.xls").Activate
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 58
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 63
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 67
ActiveWindow.ScrollRow = 68
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 80
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 82
ActiveWindow.ScrollRow = 83
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 86
ActiveWindow.ScrollRow = 87
ActiveWindow.ScrollRow = 88
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 90
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 92
ActiveWindow.ScrollRow = 93
ActiveWindow.ScrollRow = 94
ActiveWindow.ScrollRow = 98
ActiveWindow.ScrollRow = 101
ActiveWindow.ScrollRow = 104
ActiveWindow.ScrollRow = 116
ActiveWindow.ScrollRow = 117
ActiveWindow.ScrollRow = 119
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 127
Range("A157").Select
ActiveCell.FormulaR1C1 = "='[Copy of Prototype SO & PO.xls]PO Sheet
A'!R3C3"
Range("B157").Select
ActiveCell.FormulaR1C1 = "='[Copy of Prototype SO & PO.xls]PO Sheet
A'!R4C3"
Range("C157").Select
ActiveCell.FormulaR1C1 = "='[Copy of Prototype SO & PO.xls]PO Sheet
A'!R18C4"
Range("D157").Select
ActiveCell.FormulaR1C1 = "='[Copy of Prototype SO & PO.xls]PO Sheet
A'!R21C1"
Range("E157").Select
ActiveCell.FormulaR1C1 = "='[Copy of Prototype SO & PO.xls]PO Sheet
A'!R21C5"
Range("F157").Select
ActiveCell.FormulaR1C1 = "='[Copy of Prototype SO & PO.xls]PO Sheet
A'!R21C6"
Range("G157").Select
ActiveCell.FormulaR1C1 = "='[Copy of Prototype SO & PO.xls]PO Sheet
A'!R21C7"
Range("J157").Select
ActiveCell.FormulaR1C1 = "='[Copy of Prototype SO & PO.xls]PO Sheet
A'!R21C10"
Range("K157").Select
ActiveCell.FormulaR1C1 = "='[Copy of Prototype SO & PO.xls]PO Sheet
A'!R21C11"
Range("L157").Select
ActiveCell.FormulaR1C1 = "='[Copy of Prototype SO & PO.xls]PO Sheet
A'!R21C2"
Range("M157").Select
ActiveCell.FormulaR1C1 = "='[Copy of Prototype SO & PO.xls]PO Sheet
A'!R21C3"
Range("N157").Select
ActiveCell.FormulaR1C1 = "='[Copy of Prototype SO & PO.xls]PO Sheet
A'!R21C4"
Range("N158").Select
Windows("Copy of Prototype SO & PO.xls").Activate
End Sub
 
S

Sheeloo

You can get the last filled row (value in Col A) in a spreadsheet by
lastRow = ActiveSheet.Cells(65536, "A").End(xlUp).Row

You can then write the results in row lastRow+1

andrewc said:
How to I insert macro results on a separate spreadsheet in row number order?
In other words, I want the macro to put the results of its computations
in the next row that is blank each time that it is run.

here is the macro that I created:
' A Macro
' Macro recorded 1/2/2009 by Kids Supply Company
'

'
Windows("Copy of Master SO & PO List.xls").Activate
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 58
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 63
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 67
ActiveWindow.ScrollRow = 68
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 80
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 82
ActiveWindow.ScrollRow = 83
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 86
ActiveWindow.ScrollRow = 87
ActiveWindow.ScrollRow = 88
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 90
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 92
ActiveWindow.ScrollRow = 93
ActiveWindow.ScrollRow = 94
ActiveWindow.ScrollRow = 98
ActiveWindow.ScrollRow = 101
ActiveWindow.ScrollRow = 104
ActiveWindow.ScrollRow = 116
ActiveWindow.ScrollRow = 117
ActiveWindow.ScrollRow = 119
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 127
Range("A157").Select
ActiveCell.FormulaR1C1 = "='[Copy of Prototype SO & PO.xls]PO Sheet
A'!R3C3"
Range("B157").Select
ActiveCell.FormulaR1C1 = "='[Copy of Prototype SO & PO.xls]PO Sheet
A'!R4C3"
Range("C157").Select
ActiveCell.FormulaR1C1 = "='[Copy of Prototype SO & PO.xls]PO Sheet
A'!R18C4"
Range("D157").Select
ActiveCell.FormulaR1C1 = "='[Copy of Prototype SO & PO.xls]PO Sheet
A'!R21C1"
Range("E157").Select
ActiveCell.FormulaR1C1 = "='[Copy of Prototype SO & PO.xls]PO Sheet
A'!R21C5"
Range("F157").Select
ActiveCell.FormulaR1C1 = "='[Copy of Prototype SO & PO.xls]PO Sheet
A'!R21C6"
Range("G157").Select
ActiveCell.FormulaR1C1 = "='[Copy of Prototype SO & PO.xls]PO Sheet
A'!R21C7"
Range("J157").Select
ActiveCell.FormulaR1C1 = "='[Copy of Prototype SO & PO.xls]PO Sheet
A'!R21C10"
Range("K157").Select
ActiveCell.FormulaR1C1 = "='[Copy of Prototype SO & PO.xls]PO Sheet
A'!R21C11"
Range("L157").Select
ActiveCell.FormulaR1C1 = "='[Copy of Prototype SO & PO.xls]PO Sheet
A'!R21C2"
Range("M157").Select
ActiveCell.FormulaR1C1 = "='[Copy of Prototype SO & PO.xls]PO Sheet
A'!R21C3"
Range("N157").Select
ActiveCell.FormulaR1C1 = "='[Copy of Prototype SO & PO.xls]PO Sheet
A'!R21C4"
Range("N158").Select
Windows("Copy of Prototype SO & PO.xls").Activate
End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro works differently on different computers, same version of Ex 6
Macro that deletes and pastes filterd rows and then some 2
macro to copy rows that contain a cell with a certain number 3
Macro code 0
Macro to hide rows with a certain cell empty then set print area and print 12
Macro Saving 1
Making a macro universal to create multiple worksheets 0
Macro Help 0

Top