Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Macro
I have a dozen worksheets in one workbook that are the same as the example
below. What I currently do is I have a Date Index worksheet set up to chance the top date for all 12 workwheets and the bottom date is calculated from the top date minus 7. This automatically updates all worksheets. Lets say they are called One through Twelve. I know I can set up a macro (12 times) to do what I want, however I think I would be better off with a loop function. What I would like to do is take the top (figures only) and copy them down to the bottom figures and then I will have to input the new (weekly) figures manually (as they are generated by a system generated program. After copying the figures I then would want to make the top figures equal to zero and manually inpute this weeks figures. Can do??? 07/23/07 07/23/07 MDM 1000 Rel 2x Severity Total Numbers MDM 1000 Rel 2x Priority Total Numbers Critical 52 1 28 High 58 2 77 Medium 30 3 26 Low 7 Unassigned 16 Total 147 Total 147 07/16/07 07/16/07 MDM 1000 Rel 2x Severity Total Numbers MDM 1000 Rel 2x Priority Total Numbers Critical 40 1 15 High 57 2 77 Medium 24 3 26 Low 7 Unassigned 10 Total 128 Total 128 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Macro
You can loop through the worksheets using this code
for each ws in worksheets with ws set copyrange = .Range("A2:b10") copyrange.copy Destination:=Range("A9") end with next ws "Beep Beep" wrote: I have a dozen worksheets in one workbook that are the same as the example below. What I currently do is I have a Date Index worksheet set up to chance the top date for all 12 workwheets and the bottom date is calculated from the top date minus 7. This automatically updates all worksheets. Lets say they are called One through Twelve. I know I can set up a macro (12 times) to do what I want, however I think I would be better off with a loop function. What I would like to do is take the top (figures only) and copy them down to the bottom figures and then I will have to input the new (weekly) figures manually (as they are generated by a system generated program. After copying the figures I then would want to make the top figures equal to zero and manually inpute this weeks figures. Can do??? 07/23/07 07/23/07 MDM 1000 Rel 2x Severity Total Numbers MDM 1000 Rel 2x Priority Total Numbers Critical 52 1 28 High 58 2 77 Medium 30 3 26 Low 7 Unassigned 16 Total 147 Total 147 07/16/07 07/16/07 MDM 1000 Rel 2x Severity Total Numbers MDM 1000 Rel 2x Priority Total Numbers Critical 40 1 15 High 57 2 77 Medium 24 3 26 Low 7 Unassigned 10 Total 128 Total 128 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Macro
Thanks Joel
However it does one and then stops. Do I have to identify the worksheets? "Joel" wrote: You can loop through the worksheets using this code for each ws in worksheets with ws set copyrange = .Range("A2:b10") copyrange.copy Destination:=Range("A9") end with next ws "Beep Beep" wrote: I have a dozen worksheets in one workbook that are the same as the example below. What I currently do is I have a Date Index worksheet set up to chance the top date for all 12 workwheets and the bottom date is calculated from the top date minus 7. This automatically updates all worksheets. Lets say they are called One through Twelve. I know I can set up a macro (12 times) to do what I want, however I think I would be better off with a loop function. What I would like to do is take the top (figures only) and copy them down to the bottom figures and then I will have to input the new (weekly) figures manually (as they are generated by a system generated program. After copying the figures I then would want to make the top figures equal to zero and manually inpute this weeks figures. Can do??? 07/23/07 07/23/07 MDM 1000 Rel 2x Severity Total Numbers MDM 1000 Rel 2x Priority Total Numbers Critical 52 1 28 High 58 2 77 Medium 30 3 26 Low 7 Unassigned 16 Total 147 Total 147 07/16/07 07/16/07 MDM 1000 Rel 2x Severity Total Numbers MDM 1000 Rel 2x Priority Total Numbers Critical 40 1 15 High 57 2 77 Medium 24 3 26 Low 7 Unassigned 10 Total 128 Total 128 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Macro
I think we arre missing the period on the following line
from: copyrange.copy Destination:=Range("A9") to: copyrange.copy Destination:=.Range("A9") "Beep Beep" wrote: Thanks Joel However it does one and then stops. Do I have to identify the worksheets? "Joel" wrote: You can loop through the worksheets using this code for each ws in worksheets with ws set copyrange = .Range("A2:b10") copyrange.copy Destination:=Range("A9") end with next ws "Beep Beep" wrote: I have a dozen worksheets in one workbook that are the same as the example below. What I currently do is I have a Date Index worksheet set up to chance the top date for all 12 workwheets and the bottom date is calculated from the top date minus 7. This automatically updates all worksheets. Lets say they are called One through Twelve. I know I can set up a macro (12 times) to do what I want, however I think I would be better off with a loop function. What I would like to do is take the top (figures only) and copy them down to the bottom figures and then I will have to input the new (weekly) figures manually (as they are generated by a system generated program. After copying the figures I then would want to make the top figures equal to zero and manually inpute this weeks figures. Can do??? 07/23/07 07/23/07 MDM 1000 Rel 2x Severity Total Numbers MDM 1000 Rel 2x Priority Total Numbers Critical 52 1 28 High 58 2 77 Medium 30 3 26 Low 7 Unassigned 16 Total 147 Total 147 07/16/07 07/16/07 MDM 1000 Rel 2x Severity Total Numbers MDM 1000 Rel 2x Priority Total Numbers Critical 40 1 15 High 57 2 77 Medium 24 3 26 Low 7 Unassigned 10 Total 128 Total 128 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Macro
Didn't seem to make any difference Joel.
"Joel" wrote: I think we arre missing the period on the following line from: copyrange.copy Destination:=Range("A9") to: copyrange.copy Destination:=.Range("A9") "Beep Beep" wrote: Thanks Joel However it does one and then stops. Do I have to identify the worksheets? "Joel" wrote: You can loop through the worksheets using this code for each ws in worksheets with ws set copyrange = .Range("A2:b10") copyrange.copy Destination:=Range("A9") end with next ws "Beep Beep" wrote: I have a dozen worksheets in one workbook that are the same as the example below. What I currently do is I have a Date Index worksheet set up to chance the top date for all 12 workwheets and the bottom date is calculated from the top date minus 7. This automatically updates all worksheets. Lets say they are called One through Twelve. I know I can set up a macro (12 times) to do what I want, however I think I would be better off with a loop function. What I would like to do is take the top (figures only) and copy them down to the bottom figures and then I will have to input the new (weekly) figures manually (as they are generated by a system generated program. After copying the figures I then would want to make the top figures equal to zero and manually inpute this weeks figures. Can do??? 07/23/07 07/23/07 MDM 1000 Rel 2x Severity Total Numbers MDM 1000 Rel 2x Priority Total Numbers Critical 52 1 28 High 58 2 77 Medium 30 3 26 Low 7 Unassigned 16 Total 147 Total 147 07/16/07 07/16/07 MDM 1000 Rel 2x Severity Total Numbers MDM 1000 Rel 2x Priority Total Numbers Critical 40 1 15 High 57 2 77 Medium 24 3 26 Low 7 Unassigned 10 Total 128 Total 128 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Macro
Then this will work
set copyrange = sheets(ws.name).Range("A2:b10") copyrange.copy Destination:=sheets(ws.name).Range("A9") "Beep Beep" wrote: Didn't seem to make any difference Joel. "Joel" wrote: I think we arre missing the period on the following line from: copyrange.copy Destination:=Range("A9") to: copyrange.copy Destination:=.Range("A9") "Beep Beep" wrote: Thanks Joel However it does one and then stops. Do I have to identify the worksheets? "Joel" wrote: You can loop through the worksheets using this code for each ws in worksheets with ws set copyrange = .Range("A2:b10") copyrange.copy Destination:=Range("A9") end with next ws "Beep Beep" wrote: I have a dozen worksheets in one workbook that are the same as the example below. What I currently do is I have a Date Index worksheet set up to chance the top date for all 12 workwheets and the bottom date is calculated from the top date minus 7. This automatically updates all worksheets. Lets say they are called One through Twelve. I know I can set up a macro (12 times) to do what I want, however I think I would be better off with a loop function. What I would like to do is take the top (figures only) and copy them down to the bottom figures and then I will have to input the new (weekly) figures manually (as they are generated by a system generated program. After copying the figures I then would want to make the top figures equal to zero and manually inpute this weeks figures. Can do??? 07/23/07 07/23/07 MDM 1000 Rel 2x Severity Total Numbers MDM 1000 Rel 2x Priority Total Numbers Critical 52 1 28 High 58 2 77 Medium 30 3 26 Low 7 Unassigned 16 Total 147 Total 147 07/16/07 07/16/07 MDM 1000 Rel 2x Severity Total Numbers MDM 1000 Rel 2x Priority Total Numbers Critical 40 1 15 High 57 2 77 Medium 24 3 26 Low 7 Unassigned 10 Total 128 Total 128 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop to run macro each tow | Excel Programming | |||
Do until loop with use of another macro in loop | Excel Programming | |||
macro loop | Excel Discussion (Misc queries) | |||
VBA Macro Loop | Excel Programming |