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

Thanks
Miguel

"Jacob Skaria" wrote:

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.Refr esh
Sheets("RVA").Range("A4").Value = Sheets("Summary").Range("G4")

If this post helps click Yes
---------------
Jacob Skaria


"Miguel" wrote:

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

"Atishoo" wrote:

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" wrote:

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