Create a loop

[Miguel]

Hello,
I have recorded a macro that selects 1 cell (G4) and then the macro does a
variety of things (like copy and paste, refresh a pivot table, etc using that
number from G4). My question is I want the macro to select a range of cells
(not only G4) and select the first cell and do what the macro is supposed to
do, then select the next cell and let the macro do again, and so on. I know I
have to create a loop, but so far I have been unsuccselful. Is there a way to
create this loop? My macro is below:
Range("G4").Select
Selection.Copy
Sheets("Census Input").Select
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("G2").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("G2:G151")
Range("G2:G151").Select
Range("Q4").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
ActiveWindow.LargeScroll ToRight:=-1
Sheets("Summary").Select
Selection.Copy
Sheets("RVA").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Thanks!!!

 

[Atishoo]

wrap your sub in
For Each c In worksheet("sheet1").range ("a1:z1000")

Next
put next at the end.
replace the value you want to affect with c.value or c.address
this example presumes that the range you want to apply to is a1:z1000

 

[Miguel]

Hi,
I'm not sure what you mean when you say "by replacing the value you want to
affect with c.value". My code is below but I get the message "Sub or function
not defined" referring to the Worksheet("RVA"). Can you explain a bit more.

For Each c In Worksheet("RVA").Range("G4:G100")
Range("G4").Select
Selection.Copy
Sheets("Census Input").Select
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("G2").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("G2:G151")
Range("G2:G151").Select
Range("Q4").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
ActiveWindow.LargeScroll ToRight:=-1
Sheets("Summary").Select
Selection.Copy
Sheets("RVA").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Next

End Sub

Thanks
Miguel

 

[Jacob Skaria]

Try the below code which should do exactly what your code does ..Now explain
a bit more about the range you need to loop through

Sheets("Census Input").Range("G2").Value = Range("G4")
Sheets("Census Input").Range("G2").AutoFill _
Destination:=Sheets("Census Input").Range("G2:G151")
Sheets("Census Input").PivotTables("PivotTable1").PivotCache.Refresh
Sheets("RVA").Range("A4").Value = Sheets("Summary").Range("G4")

If this post helps click Yes

 

[Miguel]

Hi Jacob,
my range was first cell G4, that's how I recorded the macro, but now I want
it to be a range of unknown cells (it could be from G4 to G10 or from G4 to
G100). In other words, I want the macro to work for any number of rows for
column G. Then the macro would do the copy and paste, refresh the pivot
table, etc. I run your macro, but I don't think it's working. Can you help
me?

Thanks
Miguel

 

[Miguel]

sorry, your macro works, but now I'm trying to create the loop, any ideas?

Thanks
Miguel

 

[Miguel]

Jacob,
I created the following macro, and it seems to be working fine:
Sub Premium()
'
' Premium Macro
' Macro recorded 10/5/2009 by Vivar
'

'
startrow = Sheets("RVA").Range("G1")
numrow = Sheets("RVA").Range("H1")

i = startrow

For i = startrow To numrow
Sheets("Census Input").Range("G2").Value = Sheets("RVA").Cells(i + 3, 7)
Sheets("Census Input").Range("G2").AutoFill _
Destination:=Sheets("Census Input").Range("G2:G151")
Sheets("Census Input").PivotTables("PivotTable1").PivotCache.Refresh
Sheets("RVA").Cells(i + 3, 1).Value = Sheets("Summary").Range("H19")

Next

End Sub

Thanks
Miguel