copying and pasting contents of worksheet


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: copying and pasting contents of worksheet

Hybrid View

  1. #1
    Naveen Guest

    copying and pasting contents of worksheet


    Hi

    I have a VBA macro that copies a range on a given worksheet , deletes the
    copied matter from the worksheet , activates a new worksheet and pastes the
    contents.

    I am able to delete the selected matter from the worksheet but am simply
    not able to paste the contents on the new worksheet. I get an error message
    stating "object error".

    Can anyone send me code that copies matter from one worksheet and pastes
    it onto another one.

    Thanks in advance

    Naveen

  2. #2
    Russ Guest

    Re: copying and pasting contents of worksheet


    "Naveen" <thalanki_naveen@rocketmail.com> wrote:
    >
    >Hi
    >
    >I have a VBA macro that copies a range on a given worksheet , deletes the
    >copied matter from the worksheet , activates a new worksheet and pastes

    the
    >contents.
    >
    >I am able to delete the selected matter from the worksheet but am simply
    >not able to paste the contents on the new worksheet. I get an error message
    >stating "object error".
    >
    >Can anyone send me code that copies matter from one worksheet and pastes
    >it onto another one.
    >
    >Thanks in advance
    >
    >Naveen


    Naveen,
    Instead of actively moving between sheets, use range objects.
    Try something like this:

    'Copy the first 75 rows x 20 columns wide from sheet1
    ' to sheet2
    Set rng1 = shtl.Range(sht1.Cells(1, 1), sht1.Cells(75, 20))
    Set rng2 = sht2.Range(sht2.Cells(1, 1), sht2.Cells(75, 20))

    rng2.Copy(rng1)

    Hope this helps,
    -Russ.



  3. #3
    Sam Barrett Guest

    Re: copying and pasting contents of worksheet


    "Russ" <russell.thompson@adlink.com> wrote:
    >
    >"Naveen" <thalanki_naveen@rocketmail.com> wrote:
    >>
    >>Hi
    >>
    >>I have a VBA macro that copies a range on a given worksheet , deletes the
    >>copied matter from the worksheet , activates a new worksheet and pastes

    >the
    >>contents.
    >>
    >>I am able to delete the selected matter from the worksheet but am simply
    >>not able to paste the contents on the new worksheet. I get an error message
    >>stating "object error".
    >>
    >>Can anyone send me code that copies matter from one worksheet and pastes
    >>it onto another one.
    >>
    >>Thanks in advance
    >>
    >>Naveen

    >
    >Naveen,
    >Instead of actively moving between sheets, use range objects.
    >Try something like this:
    >
    > 'Copy the first 75 rows x 20 columns wide from sheet1
    > ' to sheet2
    > Set rng1 = shtl.Range(sht1.Cells(1, 1), sht1.Cells(75, 20))
    > Set rng2 = sht2.Range(sht2.Cells(1, 1), sht2.Cells(75, 20))
    >
    > rng2.Copy(rng1)
    >
    >Hope this helps,
    >-Russ.
    >
    >


    Russ's way is the best, but here is a different solution so that you can
    see how to move between workbooks.

    Sub Macro1()

    ' Copy [A1:B2] to a new workbook
    firstBook = ActiveWorkbook.Name
    [A1:B2].Copy
    Workbooks.Add
    ActiveSheet.Paste

    ' Delete [A1:B2] from first workbook
    Windows(firstBook).Activate
    ' Application.CutCopyMode = False
    [A1:B2].ClearContents
    End Sub

    You probably did the delete first which also destroys the copied material.


  4. #4
    Naveen Guest

    Re: copying and pasting contents of worksheet


    "Russ" <russell.thompson@adlink.com> wrote:
    >
    >"Naveen" <thalanki_naveen@rocketmail.com> wrote:
    >>
    >>Hi
    >>
    >>I have a VBA macro that copies a range on a given worksheet , deletes the
    >>copied matter from the worksheet , activates a new worksheet and pastes

    >the
    >>contents.
    >>
    >>I am able to delete the selected matter from the worksheet but am simply
    >>not able to paste the contents on the new worksheet. I get an error message
    >>stating "object error".
    >>
    >>Can anyone send me code that copies matter from one worksheet and pastes
    >>it onto another one.
    >>
    >>Thanks in advance
    >>
    >>Naveen

    >
    >Naveen,
    >Instead of actively moving between sheets, use range objects.
    >Try something like this:
    >
    > 'Copy the first 75 rows x 20 columns wide from sheet1
    > ' to sheet2
    > Set rng1 = shtl.Range(sht1.Cells(1, 1), sht1.Cells(75, 20))
    > Set rng2 = sht2.Range(sht2.Cells(1, 1), sht2.Cells(75, 20))
    >
    > rng2.Copy(rng1)
    >
    >Hope this helps,
    >-Russ.
    >
    >

    Thanks for your response Russ.

    Your suggestion worked (phew !)after I changed the last line of your code
    to the following :

    rng1.copy destination :=rng2

    Regards
    Naveen



  5. #5
    Russ Guest

    Re: copying and pasting contents of worksheet


    >Thanks for your response Russ.
    >
    >Your suggestion worked (phew !)after I changed the last line of your code
    >to the following :
    >
    >rng1.copy destination :=rng2
    >
    >Regards
    >Naveen
    >
    >


    Sorry 'bout that,
    I usually use the "Call" keyword in front of calls to Subs, Functions and
    Methods so that VB will allow me to enclose the parameters in parenthesis.
    (Old habits die hard I guess <g>). I accidentally left it out in the example.

    Call rng2.Copy(rng1)

    -Russ.



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center