+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Ingve
    Guest

    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. #2
    Tom Ogilvy
    Guest

    Re: 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" <satnerd@sandve.org> wrote in message
    news:1138048463.411113.152810@z14g2000cwz.googlegroups.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. #3
    Ingve
    Guest

    Re: 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. #4
    Tom Ogilvy
    Guest

    Re: 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" <satnerd@sandve.org> wrote in message
    news:1138051790.961276.7420@g44g2000cwa.googlegroups.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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1