copying and pasting contents of worksheet

[Naveen]

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

[Russ]

"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.

[Sam Barrett]

"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.

[Naveen]

"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

[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
>
>

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.