Hi everyone,

I'm using C2 in "Summary" sheet as the lookup value for Index Match formulas
pulling data from "Data" sheet. the lookup array where the C2 is found is
A5:A67 (named range "LocCode" Location Code) on "Data".
"Summary" has a table of data and chart for each Location Code.
What I'd like to do now is have a macro that enters each Location Code from
A5:A67 one at a time and copy the sheet and rename tab with the relevant
Location Code so that I have 1 sheet per each location code.

If anyone can help me with this, it would make me happier than you can
imagine. I'm getting so muddled trying to do this one. Probably because I'm
really cheesed off with this job. It was done as a favour. We're really
snowed under and thought that the person who asked for it would be chuffed
with a dynamic workbook. Everyone seems to be in favour of electronic version
and being green when it's not their project! Our boss, who originally said we
were too busy just now,has now asked us to provide same data 1 sheet per
Location Code because the other department "isn't very computer savvy" and
they'd like to print it!!!! OMG!
Sorry!! Rant over.
Cheers
Diddy

This should do what you need:

Code:
--------------------
Sub create_sheets()
Dim Rng As Range, MyCell As Range
Application.ScreenUpdating = False
Set Rng = Sheets("Data").Range("LocCode")
For Each MyCell In Rng
Sheets("Summary").Range("C2").Value = MyCell.Value
With Sheets("Summary")
.Copy Befo=Sheets(2)
ActiveSheet.Name = .Range("C2").Value
End With
Sheets("Summary").Select
Next
Application.ScreenUpdating = True
End Sub
--------------------


Diddy;454184 Wrote:
Hi everyone,

I'm using C2 in "Summary" sheet as the lookup value for Index Match
formulas
pulling data from "Data" sheet. the lookup array where the C2 is found
is
A5:A67 (named range "LocCode" Location Code) on "Data".
"Summary" has a table of data and chart for each Location Code.
What I'd like to do now is have a macro that enters each Location Code
from
A5:A67 one at a time and copy the sheet and rename tab with the
relevant
Location Code so that I have 1 sheet per each location code.

If anyone can help me with this, it would make me happier than you can
imagine. I'm getting so muddled trying to do this one. Probably because
I'm
really cheesed off with this job. It was done as a favour. We're really
snowed under and thought that the person who asked for it would be
chuffed
with a dynamic workbook. Everyone seems to be in favour of electronic
version
and being green when it's not their project! Our boss, who originally
said we
were too busy just now,has now asked us to provide same data 1 sheet
per
Location Code because the other department "isn't very computer savvy"
and
they'd like to print it!!!! OMG!
Sorry!! Rant over.
Cheers
Diddy


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125724

Hi Simon,

That's fantastic!!!

I've added some recorded code to copy and paste as values but suspect there
is a better way to do this.

Sub create_sheets()
Dim Rng As Range, MyCell As Range
Application.ScreenUpdating = False
Set Rng = Sheets("Data").Range("LocCode")
For Each MyCell In Rng
Sheets("Trends").Range("b2").Value = MyCell.Value
With Sheets("Trends")
..Copy Befo=Sheets(2)
Cells.Select
Range("B1").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
ActiveCell.FormulaR1C1 = "`"
ActiveSheet.Name = .Range("b2").Value
End With
Sheets("Trends").Select
Next
Application.ScreenUpdating = True
End Sub

THANK YOU so so much!!!

"Simon Lloyd" wrote:


This should do what you need:

Code:
--------------------
Sub create_sheets()
Dim Rng As Range, MyCell As Range
Application.ScreenUpdating = False
Set Rng = Sheets("Data").Range("LocCode")
For Each MyCell In Rng
Sheets("Summary").Range("C2").Value = MyCell.Value
With Sheets("Summary")
.Copy Befo=Sheets(2)
ActiveSheet.Name = .Range("C2").Value
End With
Sheets("Summary").Select
Next
Application.ScreenUpdating = True
End Sub
--------------------


Diddy;454184 Wrote:
Hi everyone,

I'm using C2 in "Summary" sheet as the lookup value for Index Match
formulas
pulling data from "Data" sheet. the lookup array where the C2 is found
is
A5:A67 (named range "LocCode" Location Code) on "Data".
"Summary" has a table of data and chart for each Location Code.
What I'd like to do now is have a macro that enters each Location Code
from
A5:A67 one at a time and copy the sheet and rename tab with the
relevant
Location Code so that I have 1 sheet per each location code.

If anyone can help me with this, it would make me happier than you can
imagine. I'm getting so muddled trying to do this one. Probably because
I'm
really cheesed off with this job. It was done as a favour. We're really
snowed under and thought that the person who asked for it would be
chuffed
with a dynamic workbook. Everyone seems to be in favour of electronic
version
and being green when it's not their project! Our boss, who originally
said we
were too busy just now,has now asked us to provide same data 1 sheet
per
Location Code because the other department "isn't very computer savvy"
and
they'd like to print it!!!! OMG!
Sorry!! Rant over.
Cheers
Diddy


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125724

Yep, like this

Code
-------------------
Sub create_sheets(
Dim Rng As Range, MyCell As Rang
Application.ScreenUpdating = Fals
Set Rng = Sheets("Data").Range("LocCode"
For Each MyCell In Rn
Sheets("Summary").Range("C2").Value = MyCell.Valu
With Sheets("Summary"
.Copy Befo=Sheets(2
ActiveSheet.Name = .Range("C2").Valu
ActiveSheet.UsedRange = ActiveSheet.UsedRange.Valu
End Wit
Sheets("Summary").Selec
Nex
Application.ScreenUpdating = Tru
End Su
-------------------

Diddy;454224 Wrote:
Hi Simon

That's fantastic!!

I've added some recorded code to copy and paste as values but suspec
ther
is a better way to do this

Sub create_sheets(
Dim Rng As Range, MyCell As Rang
Application.ScreenUpdating = Fals
Set Rng = Sheets("Data").Range("LocCode"
For Each MyCell In Rn
Sheets("Trends").Range("b2").Value = MyCell.Valu
With Sheets("Trends"
..Copy Befo=Sheets(2
Cells.Selec
Range("B1").Activat
Selection.Cop
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Operation:=
xlNone, SkipBlanks:=False, Transpose:=Fals
ActiveCell.FormulaR1C1 = "`
ActiveSheet.Name = .Range("b2").Valu
End Wit
Sheets("Trends").Selec
Nex
Application.ScreenUpdating = Tru
End Su

THANK YOU so so much!!

"Simon Lloyd" wrote


This should do what you need

Code
-------------------
Sub create_sheets(
Dim Rng As Range, MyCell As Rang
Application.ScreenUpdating = Fals
Set Rng = Sheets("Data").Range("LocCode"
For Each MyCell In Rn
Sheets("Summary").Range("C2").Value = MyCell.Valu
With Sheets("Summary"
.Copy Befo=Sheets(2
ActiveSheet.Name = .Range("C2").Valu
End Wit
Sheets("Summary").Selec
Nex
Application.ScreenUpdating = Tru
End Su
-------------------


Diddy;454184 Wrote
Hi everyone

I'm using C2 in "Summary" sheet as the lookup value for Index Matc
formula
pulling data from "Data" sheet. the lookup array where the C2 i
foun
i
A5:A67 (named range "LocCode" Location Code) on "Data"
"Summary" has a table of data and chart for each Location Code
What I'd like to do now is have a macro that enters each Locatio
Cod
fro
A5:A67 one at a time and copy the sheet and rename tab with th
relevan
Location Code so that I have 1 sheet per each location code

If anyone can help me with this, it would make me happier than yo
ca
imagine. I'm getting so muddled trying to do this one. Probabl
becaus
I'
really cheesed off with this job. It was done as a favour. We'r
reall
snowed under and thought that the person who asked for it would b
chuffe
with a dynamic workbook. Everyone seems to be in favour o
electroni
versio
and being green when it's not their project! Our boss, wh
originall
said w
were too busy just now,has now asked us to provide same data
shee
pe
Location Code because the other department "isn't very compute
savvy
an
they'd like to print it!!!! OMG
Sorry!! Rant over
Cheer
Didd


-
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' ('The Code Cage - Microsoft Office Help
Microsoft Office Discussion' (http://www.thecodecage.com)

-----------------------------------------------------------------------
Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simo
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: 'Macro to insert value from range on differen
sheet and copy sheet - The Code Cage Forums
(http://www.thecodecage.com/forumz/sh...d.php?t=125724



--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com
-----------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125724