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

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

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

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

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

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