Excel Macro Loop / VBA code help

[London 007]

Hello,

Are there any Excel super stars that can help me?

I have never written any VBA code, but I think I need it for the following:

I wish to write a macro which has the following steps:
(1) Goes to a sheet called "Price Data"
(2) Copies a row of data (e.g. cells A2 to J2)
(3) Puts the copied data into a different sheet called "RSI Macro" in row 6
(4) Then runs a separate macro called "RSI Macro"
(5) Then repeats steps 1 to 4 for every row in the "Price Data" sheet that is not blank [e.g. takes data A3 to J3, then A4 to J4, and so on until all the rows with data have been completed]

I think this is called creating a row loop.

For your information the macro which I refer to in step (4) above is as follows (It may be better just to combine the two macros, rather than have two separate macros):

Macro1()
'
' Macro1 Macro
'


'
Sheets("RSI Macro").Select
Range("B6:DD6").Select
Selection.Copy
Range("C12").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True
Range("B3:CSubH3").Select
Application.CutCopyMode = False
Selection.Copy
Range("B12").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True
Range("A12:C203").Select
ActiveWindow.SmallScroll Down:=-282
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("RSI Macro").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("RSI Macro").Sort.SortFields.Add Key:=Range( _
"B12:B99"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("RSI Macro").Sort
.SetRange Range("A12:C99")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("B18").Select
ActiveWindow.SmallScroll Down:=3
ActiveWindow.SmallScroll ToRight:=-1
ActiveWindow.SmallScroll Down:=4
ActiveWindow.SmallScroll ToRight:=-2
ActiveWindow.SmallScroll Down:=41
ActiveWindow.SmallScroll ToRight:=-1
ActiveCell.Offset(51, 2).Range("A1:G1").Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:G3"), Type:= _
xlFillDefault
ActiveCell.Range("A1:G3").Select
Range("D59").Select
ActiveWindow.SmallScroll Down:=-42
Range("B12:B91").Select
Selection.Copy
Range("M12").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("C12:C127").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.SmallScroll Down:=-271
Range("N12").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("M12:N120").Select
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("RSI Macro").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("RSI Macro").Sort.SortFields.Add Key:=Range( _
"M12:M120"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("RSI Macro").Sort
.SetRange Range("M12:N120")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("M10:O177").Select
Selection.ClearContents
Range("B12:B101").Select
Selection.Copy
Range("M12").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("J12:J137").Select
Application.CutCopyMode = False
Selection.Copy
Range("N12").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("M12:N118").Select
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("RSI Macro").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("RSI Macro").Sort.SortFields.Add Key:=Range( _
"M12:M118"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("RSI Macro").Sort
.SetRange Range("M12:N118")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("N12:N108").Select


Many thanks,

James

 

[venkat1926]

suppose the data is ike this in sheet called "PRICE DATA" (ANOTHER SHEET IS CALLED rsi mACRO")

Price Data

*	A	B	C	D	E	F	G	H	I	J
1	hdng1	hdng2	hdng3	hdng4	hdng5	hdng6	hdng7	hdng8	hdng9	hdng10
2	76	23	20	21	91	36	48	47	71	57
3	26	9	28	81	11	45	43	49	43	56
4	84	17	99	72	63	16	15	53	73	36
5	98	51	66	14	2	36	93	23	13	25
6	45	26	1	52	90	32	36	5	63	13
7	51	83	60	84	8	27	83	98	21	45
8	94	99	21	84	73	25	57	82	31	25
9	48	53	56	66	62	46	73	76	55	47
10	7	20	73	44	56	46	3	44	72	37

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


Your macri is from recorded macro. That is perfectly right for learning how to write a macro. But the RECORDED macro can be tweaked.

now see this macro (and check with your macro)

Sub test()
Dim lastcell As Range


With Worksheets("price data")
Set lastcell = .Range("J2").End(xlDown)
.Range(Range("A2"), lastcell).Copy
With Worksheets("rsi macro")
.Range("a6").PasteSpecial
End With
End With