| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a loop
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!!! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Create a loop
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!!! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Create a loop
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!!! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Create a loop
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!!! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Create a loop
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.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!!! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Create a loop
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!!! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Create a loop
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.Refr esh Sheets("RVA").Cells(i + 3, 1).Value = Sheets("Summary").Range("H19") Next End Sub Thanks Miguel "Miguel" wrote: 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!!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Create a loop | Excel Discussion (Misc queries) | |||
| How to create Loop | Excel Programming | |||
| (Complex) Loop within loop to create worksheets | Excel Programming | |||
| create a loop | Excel Programming | |||
| How do I create a For loop within a For loop? | Excel Programming | |||
Linear Mode
