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
hdng1	hdng2	hdng3	hdng4	hdng5	hdng6	hdng7	hdng8	hdng9	hdng10

<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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]91[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]57[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]81[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]56[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]36[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]93[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]25[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]13[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]45[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]25[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]47[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]37[/TD]

</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