Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy sheet and make new sheet active | Excel Discussion (Misc queries) | |||
copy name from active sheet to cell - using macro or function | Excel Worksheet Functions | |||
Active Cell Copy And Paste Sheet to Sheet | New Users to Excel | |||
Create new sheet from cell-info and then copy/paste special | Excel Programming | |||
To copy from a range in another sheet to the active cell | Excel Programming |