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
"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.Refresh
> 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!!!
|
Similar Threads
