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 |
Copy from active sheet and paste into new sheet using info from cell in active
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" wrote in message oups.com... 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 |
Copy from active sheet and paste into new sheet using info from cell in active
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 |
Copy from active sheet and paste into new sheet using info from cell in active
We can't all be great typists <g
-- Regards, Tom Ogilvy "Ingve" wrote in message ps.com... 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 |
All times are GMT +1. The time now is 01:33 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com