Repeating a Macro Based

[carl]

Is there a way to have the macro below repeat itself using values in cell
G10:G20 ?

Sheets("Sheet2").Select

'need a way to take the value in G10, then G11,...G20

Selection.Copy
Range("A3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Calculate


Range("A3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Sheet1").Select
Range("A4").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


Thank you in advance.

 

[PCLIVE]

Not sure where you want to loop. However, you can use this:

For each cell in Range("G10:G20")
<Your code>

Next cell

When you want to pull the value from the active cell in that range, you
would reference it as such:

cell.value


HTH,
Paul

 

[JLatham]

Carl,
What do you mean by "using the values in cell G10:G20"? Take the value in
those cells and do what with them?

Yes, you can use the values in cells to control a loop within a macro. But
knowing your goal/purpose of the code may help to figure out best way to deal
with this.

As a trivial example, let us say you want to copy the contents of cells in
column A of Sheet1 to column B on Sheet2, one by one (as I said, it's trivial
example) and you want to be able to put a number in cell G10 on sheet 1 that
tells how many rows to copy.

Dim LoopControl as Long
Dim LoopCounter as Long
LoopControl = Sheets("Sheet1").Range("G10")
'subtract 1 from value to use as a Row Offset value
For LoopCounter = 0 to LoopControl-1 ' value from G10
Sheets("Sheet1").Select
Range("A1").Offset(LoopCounter, 0).Select
Selection.Copy
Sheets("Sheet2").Select
Range("B1").Offset(LoopCounter, 0).Select
ActiveSheet.Paste
Next ' Repeat, moving down each sheet

Now, if you mean you want the contents of cells in column G to be used to
decide whether or not to copy, then you could modify that to something like
this, which would work down from Rows 10 thru 20 on Sheet 1 and when a value
in column G is greater than 1, it would then copy the data from column A on
the row over to Sheet2 into cells B10:B10, leaving blanks where the value in
column G on Sheet1 was not greater than 1:

Dim LoopCounter as Long
For LoopCounter = 10 to 20
Sheets("Sheet1").Select
If Range("G" & LoopCounter) > 1 Then
Range("A" & LoopCounter).Select
Selection.Copy
Sheets("Sheet2").Select
Range("B" & LoopCounter).Select
ActiveSheet.Paste
End IF
Next

There are many ways to do this, but need to know how you want to use the
data in cells G10:G20 to be more specific.