how to copy an excel sheet with out using paste method

[jaffar]

Hi,

I have a macro , using this macro i can copy an excel sheet into
another excel sheet, but i used paste method ( pastespecial
paste:=xlpastevalues),

I want the same functionality with out using paste method,

How can i do this task?

Thanks in advance
Jaffar.

 

[Don Guillett]

If you want the whole sheet right click the sheet tab>copy

 

[Puppet_Sock]

I have a macro , using this macro i can copy an excel sheet into
another excel sheet, but i used paste method ( pastespecial
paste:=xlpastevalues),

I want the same functionality with out using paste method,

How can i do this task?

Do you mean you want to make a copy of an entire sheet?
You can do this sort of thing with the tabs at the bottom.
Try clicking and dragging, with or without the <Ctrl> key.

To make a macro that does the same sort of thing, the
macro recorder is your friend. If you can learn to do it
manually, you can get the basics of the macro codes
to do it. Then you can easily modify them to do more
complicated and custom actions.

If that's not what you meant, then can you explain differently?
Socks

 

[jaffar]

Hi,



We have 36 excel files, each excel file consists 9 sheets. The 36 excel
files are copy of the some excel file. In some sheets of excel files may
contain data and some may not contain data.



So in my main excel file I have written a macro which copies the data (my
macro wont copy whole sheet it copies data sheets which is having data in
the cells, if any row not containing any data then that row is not copied)
from all these 36 excel files into a new sheets of new workbook, for this I
have used

Paste special pate: = xlPasteValues etc.

This is working fine but I want to achieve exact functionality with out
using Paste methods, is it possible to do this requirement with out using
paste method? If yes can you tell me how to do this?



Are there any alternative methods?

 

[Don Guillett]

these work the same
range("a1:b1").copy
range("c1:d1").paste special paste: = xlPasteValues

range("c1:d1").value=range("a1:b1").value
the range size must be the same

 

[Puppet_Sock]

jaffar wrote:
[snip]

So in my main excel file I have written a macro which copies the data (my
macro wont copy whole sheet it copies data sheets which is having data in
the cells, if any row not containing any data then that row is not copied)
from all these 36 excel files into a new sheets of new workbook, for this I
have used

Paste special pate: = xlPasteValues etc.

This is working fine but I want to achieve exact functionality with out
using Paste methods, is it possible to do this requirement with out using
paste method? If yes can you tell me how to do this?

Are there any alternative methods?

I'm confused. "This is working fine" but you don't like it?

I suppose you could, as Don says, use rangeDest.value = rangeSrc.value.

Or, I suppose if the ranges were not the same size, you could go
through cell by cells and do some kind of loop

Cells(destRow,destCol) = Cells(srcRow,srcCol)

then step the values of destRow etc. over the required range.

But what's wrong with copy/paste since that seems to be what
you want do do, and it's "working fine." If it's working fine, where
does it fail to satisfy? You see, if I could understand what it is
that you want to do, and in particular how it's not copy/paste,
then I might be able to better help.

I've seen this so many times in computing.

Q: How do I do <x>?
A: Well, you use the built in features to do <x>.
Q: But that does not do what I want. How do I do
<x> without using the built in features?
A: What about the built in features does not satisfy?
Q: I want to do <x> without them. How?

<many iterations later>

A: Oh! What you really want to do *isn't* <x> but <x+y>.
That's easy, it has built in features to do also. Here.
Q: Well, why didn't you just tell me that in the first place!
A: Because you didn't ask and wouldn't explain.
Q: Well! You don't have to get snippy.

See. I have this conversation about 8 times a week.
Socks

 

[Don Guillett]

Don't we ALL!!!

--
Don Guillett
SalesAid Software
(e-mail address removed)

jaffar wrote:
[snip]

So in my main excel file I have written a macro which copies the data (my
macro wont copy whole sheet it copies data sheets which is having data in
the cells, if any row not containing any data then that row is not
copied)
from all these 36 excel files into a new sheets of new workbook, for this
I
have used

Paste special pate: = xlPasteValues etc.

This is working fine but I want to achieve exact functionality with out
using Paste methods, is it possible to do this requirement with out using
paste method? If yes can you tell me how to do this?

Are there any alternative methods?

I'm confused. "This is working fine" but you don't like it?

I suppose you could, as Don says, use rangeDest.value = rangeSrc.value.

Or, I suppose if the ranges were not the same size, you could go
through cell by cells and do some kind of loop

Cells(destRow,destCol) = Cells(srcRow,srcCol)

then step the values of destRow etc. over the required range.

But what's wrong with copy/paste since that seems to be what
you want do do, and it's "working fine." If it's working fine, where
does it fail to satisfy? You see, if I could understand what it is
that you want to do, and in particular how it's not copy/paste,
then I might be able to better help.

I've seen this so many times in computing.

Q: How do I do <x>?
A: Well, you use the built in features to do <x>.
Q: But that does not do what I want. How do I do
<x> without using the built in features?
A: What about the built in features does not satisfy?
Q: I want to do <x> without them. How?

<many iterations later>

A: Oh! What you really want to do *isn't* <x> but <x+y>.
That's easy, it has built in features to do also. Here.
Q: Well, why didn't you just tell me that in the first place!
A: Because you didn't ask and wouldn't explain.
Q: Well! You don't have to get snippy.

See. I have this conversation about 8 times a week.
Socks