Copy from active sheet and paste into new sheet using info from cell in active

Hi,
I'm having an excel sheet named 'Box'. In Cell "B7".
I have an info with 'Box1'.
In the area "A10:H100" I have a lot of values calculated by formulas.


What I want is a macro that create a new sheet using the name from "B7"

then copy A10:H100 from active sheet 'box' and then paste special with
only
the values in the new sheet 'box1".


After changing all the values in A10:H100 and "B7" to 'box2' ( in 'box'

)I will be able to have the macro do the same procedure again but this
time paste special to 'Box2' and so on.

Is this possible with a macro or maybe 2 macros?


Thanks
Ingve

Sub CopyData()
Dim sh as Worksheet, Sh1 as Worksheet
set sh = Worksheets("Box")
on error Resume Next
set sh1 = Worksheets(sh.Range("B7'))
On Error goto 0
If sh1 is nothing then
worksheets.Add After:=worksheets(Worksheets.count)
set sh1 = worksheets(worksheets.count)
sh1.Name = sh.Range("B7")
end if
sh.Range("A10:H10").copy
sh1.Range("A1").PasteSpecial xlValues
End Sub

--
Regards,
Tom Ogilvy



"Ingve" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> I'm having an excel sheet named 'Box'. In Cell "B7".
> I have an info with 'Box1'.
> In the area "A10:H100" I have a lot of values calculated by formulas.
>
>
> What I want is a macro that create a new sheet using the name from "B7"
>
> then copy A10:H100 from active sheet 'box' and then paste special with
> only
> the values in the new sheet 'box1".
>
>
> After changing all the values in A10:H100 and "B7" to 'box2' ( in 'box'
>
> )I will be able to have the macro do the same procedure again but this
> time paste special to 'Box2' and so on.
>
> Is this possible with a macro or maybe 2 macros?
>
>
> Thanks
> Ingve
>

Thanks a lot Tom!,

I got an error on this line:
set sh1 = Worksheets(sh.Range("B7'))

But I quickly found out that replacing the ' after B7 with " did the
trick:-)

Regards
Ingve

We can't all be great typists <g>

--
Regards,
Tom Ogilvy

"Ingve" <[email protected]> wrote in message
news:[email protected]...
> Thanks a lot Tom!,
>
> I got an error on this line:
> set sh1 = Worksheets(sh.Range("B7'))
>
> But I quickly found out that replacing the ' after B7 with " did the
> trick:-)
>
> Regards
> Ingve
>