I have a very big spreadsheet which is divided out in job areas€¦there are 20
or so job areas and each job area has a few roles in it (can be between 5-10)
or maybe more. This information needs to be copied from this sheet into 20
other sheets (so each job area goes into a new sheet and the sheet is named
accordingly), so it might be some sort of IF statement that will recognise if
something =that and then copies it from the main sheet into another new sheet
which should be renamed by the macro as well. BUT the problem is that the
information needs to be copied into the new sheet in a certain way, each new
sheet will have a pre-set format and the macro should be used to either
create each of the 20 sheets or to just use the macro to duplicate last
months sheets and empty the data in it €“ leaving all the formatting and the
formulas (if thats possible?!). The copied info needs to be pasted in this
formatted file where there are colours separating fields and columns to put
in certain things.

How the macro will know where to copy from the master sheet into these new
sheets is because each new sheet, in column R will normally have the job
title in it. So the macro needs to get that job title from the master sheet
then put it in the new sheet where it finds the job title. BUT there will be
numerous job titles on the new sheet and each one will have formulas and
total rows etc (so these bits shouldnt be overwritten by the new info)€¦this
might make it difficult for the macro to know where to paste the information
and how can I say to paste in these rows in this place when the rows will
most likely change by the month. (If it helps I can send you the spreadsheet).

I dont really know how to get around this, or even if this is possible.

Can anyone please advise?

Thanks.

Well it does sound complex, but does it have to be?

Have you considered using a set of templates for each job creation, these
can contain all the required formatting, code, formulas etc. If each
template has a worksheet (suggest it is very hidden) into which you paste
your data, this data can then used in all the other sheets in the template
to present the user with their information. The destination of the data is
now defined and consistent.


--

Regards,
Nigel




"zak" wrote in message
...
I have a very big spreadsheet which is divided out in job areas€¦there are
20
or so job areas and each job area has a few roles in it (can be between
5-10)
or maybe more. This information needs to be copied from this sheet into 20
other sheets (so each job area goes into a new sheet and the sheet is
named
accordingly), so it might be some sort of IF statement that will recognise
if
something =that and then copies it from the main sheet into another new
sheet
which should be renamed by the macro as well. BUT the problem is that the
information needs to be copied into the new sheet in a certain way, each
new
sheet will have a pre-set format and the macro should be used to either
create each of the 20 sheets or to just use the macro to duplicate last
months sheets and empty the data in it €“ leaving all the formatting and
the
formulas (if thats possible?!). The copied info needs to be pasted in
this
formatted file where there are colours separating fields and columns to
put
in certain things.

How the macro will know where to copy from the master sheet into these new
sheets is because each new sheet, in column R will normally have the job
title in it. So the macro needs to get that job title from the master
sheet
then put it in the new sheet where it finds the job title. BUT there will
be
numerous job titles on the new sheet and each one will have formulas and
total rows etc (so these bits shouldnt be overwritten by the new
info)€¦this
might make it difficult for the macro to know where to paste the
information
and how can I say to paste in these rows in this place when the rows will
most likely change by the month. (If it helps I can send you the
spreadsheet).

I dont really know how to get around this, or even if this is possible.

Can anyone please advise?

Thanks.

Yes, normally the person who does this task copies across each of the 20
sheets into the new file from the old file (and then empties them). So a
template is sort of already created..he then works through each job area and
job role and copies and pastes accordingly.
And because the master file where all the info comes from is just huge, my
colleague normally pivots the information so that is is divided out by job
role and area etc. he then opens up each bit in the pivot and copies and
pastes it in the relevant sheet in the required format.
My colleague really does need help on this because it can take him a whole
day, it isnt the most pleasant of tasks because it means your copying and
pasting all day - because its 20 different job areas but each has 5-10 roles
in it.. so can be up to 200 items to copy and paste in a required way.

Please can you not suggest anything further? even if its automating just
part of the task?

thanks a lot.

"Nigel" wrote:

Well it does sound complex, but does it have to be?

Have you considered using a set of templates for each job creation, these
can contain all the required formatting, code, formulas etc. If each
template has a worksheet (suggest it is very hidden) into which you paste
your data, this data can then used in all the other sheets in the template
to present the user with their information. The destination of the data is
now defined and consistent.


--

Regards,
Nigel




"zak" wrote in message
...
I have a very big spreadsheet which is divided out in job areas€¦there are
20
or so job areas and each job area has a few roles in it (can be between
5-10)
or maybe more. This information needs to be copied from this sheet into 20
other sheets (so each job area goes into a new sheet and the sheet is
named
accordingly), so it might be some sort of IF statement that will recognise
if
something =that and then copies it from the main sheet into another new
sheet
which should be renamed by the macro as well. BUT the problem is that the
information needs to be copied into the new sheet in a certain way, each
new
sheet will have a pre-set format and the macro should be used to either
create each of the 20 sheets or to just use the macro to duplicate last
months sheets and empty the data in it €“ leaving all the formatting and
the
formulas (if thats possible?!). The copied info needs to be pasted in
this
formatted file where there are colours separating fields and columns to
put
in certain things.

How the macro will know where to copy from the master sheet into these new
sheets is because each new sheet, in column R will normally have the job
title in it. So the macro needs to get that job title from the master
sheet
then put it in the new sheet where it finds the job title. BUT there will
be
numerous job titles on the new sheet and each one will have formulas and
total rows etc (so these bits shouldnt be overwritten by the new
info)€¦this
might make it difficult for the macro to know where to paste the
information
and how can I say to paste in these rows in this place when the rows will
most likely change by the month. (If it helps I can send you the
spreadsheet).

I dont really know how to get around this, or even if this is possible.

Can anyone please advise?

Thanks.