I posted this question, but in a rather more long winded way yesterday but
was hoping for an answer if i word it a little more simply. Essentially, I
need to design a macro to Copy information from a worksheet and paste to
other worsksheets after this point - but not necessarily worksheets with a
fixed name or to ALL other worksheets after it.

I can do it to worksheets with fixed names - something like

Sheets("Mon").Select
Cells.Select
Selection.Copy
Sheets("Tues").Select
Cells.Select
ActiveSheet.Paste

I'm just after some tips to make this more flexible. For instance, looking
at the above example, I may want to copy to all sheets that begin with "Tues
........"

I realise I can copy and paste manually but for my needs, thats not
particularly useful - this need to be a command button that updates other
sheets quickly if information in one sheet is changed.

I suppose I'm not looking for a definitve answer, just some tips and
pointers - my excel knowldege is good, but programming not so!!!!

Thanks in advance for any help!!!

Ther2 arre two methods for specifying sheet in a macro


ShtArray = Array("sheet1","Sheet5",Sheet8")
for each sht in ShtArray
with sheets(sht)

end with
next sht


for each sht in sheets
if left(sht.name,3) = "Tue" then

end if
next sht


Which method you use depends on the number of sheets and the filter you are
using to pick the sheets. A common approach is something like this

for each sht in sheets
if sht.name < "Master" then

end if
next sht


"Terry" wrote:

I posted this question, but in a rather more long winded way yesterday but
was hoping for an answer if i word it a little more simply. Essentially, I
need to design a macro to Copy information from a worksheet and paste to
other worsksheets after this point - but not necessarily worksheets with a
fixed name or to ALL other worksheets after it.

I can do it to worksheets with fixed names - something like

Sheets("Mon").Select
Cells.Select
Selection.Copy
Sheets("Tues").Select
Cells.Select
ActiveSheet.Paste

I'm just after some tips to make this more flexible. For instance, looking
at the above example, I may want to copy to all sheets that begin with "Tues
......."

I realise I can copy and paste manually but for my needs, thats not
particularly useful - this need to be a command button that updates other
sheets quickly if information in one sheet is changed.

I suppose I'm not looking for a definitve answer, just some tips and
pointers - my excel knowldege is good, but programming not so!!!!

Thanks in advance for any help!!!

Hey Terry, i would try and set a loop, something like

for each WrkSht in thisworkbook.worksheets
if wrksht.name = "blah" then
goto mynext
else
What you want to copy i.e. sheet1.range("D2:D5").copy
where you want to paste it i.e.
wrksht.name.range("D2:D5").pastespecial
end if

mynext:
nextwrksht

have a play and get it do what you want. HTH pal

The Noob.

"Terry" wrote:

I posted this question, but in a rather more long winded way yesterday but
was hoping for an answer if i word it a little more simply. Essentially, I
need to design a macro to Copy information from a worksheet and paste to
other worsksheets after this point - but not necessarily worksheets with a
fixed name or to ALL other worksheets after it.

I can do it to worksheets with fixed names - something like

Sheets("Mon").Select
Cells.Select
Selection.Copy
Sheets("Tues").Select
Cells.Select
ActiveSheet.Paste

I'm just after some tips to make this more flexible. For instance, looking
at the above example, I may want to copy to all sheets that begin with "Tues
......."

I realise I can copy and paste manually but for my needs, thats not
particularly useful - this need to be a command button that updates other
sheets quickly if information in one sheet is changed.

I suppose I'm not looking for a definitve answer, just some tips and
pointers - my excel knowldege is good, but programming not so!!!!

Thanks in advance for any help!!!

Thanks guys, some useful advice there. I;m going to have a crack at it and
see if i can get it working!!!

"Noob McKnownowt" wrote:

Hey Terry, i would try and set a loop, something like

for each WrkSht in thisworkbook.worksheets
if wrksht.name = "blah" then
goto mynext
else
What you want to copy i.e. sheet1.range("D2:D5").copy
where you want to paste it i.e.
wrksht.name.range("D2:D5").pastespecial
end if

mynext:
nextwrksht

have a play and get it do what you want. HTH pal

The Noob.

"Terry" wrote:

I posted this question, but in a rather more long winded way yesterday but
was hoping for an answer if i word it a little more simply. Essentially, I
need to design a macro to Copy information from a worksheet and paste to
other worsksheets after this point - but not necessarily worksheets with a
fixed name or to ALL other worksheets after it.

I can do it to worksheets with fixed names - something like

Sheets("Mon").Select
Cells.Select
Selection.Copy
Sheets("Tues").Select
Cells.Select
ActiveSheet.Paste

I'm just after some tips to make this more flexible. For instance, looking
at the above example, I may want to copy to all sheets that begin with "Tues
......."

I realise I can copy and paste manually but for my needs, thats not
particularly useful - this need to be a command button that updates other
sheets quickly if information in one sheet is changed.

I suppose I'm not looking for a definitve answer, just some tips and
pointers - my excel knowldege is good, but programming not so!!!!

Thanks in advance for any help!!!