Is there a faster way to copy and paste one entire worksheets data into
another existing worksheet? I do NOT want to rename the sheets. Code below is
what I'm using now but it seems that there should be a simpler way. Thanks!

Sub CopySheets()
Sheets("Sheet2").Select
Cells.Select
Selection.Copy
Sheets("Sheet1").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

Give this a try...

Sub CopySheets()
Sheets("Sheet2").Cells.Copy Sheets("Sheet1").Range("A1")
End Sub

--
Rick (MVP - Excel)


"Jules" <(E-Mail Removed)> wrote in message
news:66235D91-9570-4025-8800-(E-Mail Removed)...
> Is there a faster way to copy and paste one entire worksheets data into
> another existing worksheet? I do NOT want to rename the sheets. Code below
> is
> what I'm using now but it seems that there should be a simpler way.
> Thanks!
>
> Sub CopySheets()
> Sheets("Sheet2").Select
> Cells.Select
> Selection.Copy
> Sheets("Sheet1").Select
> Range("A1").Select
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Application.CutCopyMode = False
> End Sub
>
>

Hi,

Maybee this

Sub CopySheets()
Sheets("Sheet2").UsedRange.Copy
Sheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Jules" wrote:

> Is there a faster way to copy and paste one entire worksheets data into
> another existing worksheet? I do NOT want to rename the sheets. Code below is
> what I'm using now but it seems that there should be a simpler way. Thanks!
>
> Sub CopySheets()
> Sheets("Sheet2").Select
> Cells.Select
> Selection.Copy
> Sheets("Sheet1").Select
> Range("A1").Select
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Application.CutCopyMode = False
> End Sub
>
>

Yes. Thanks!!

"Mike H" wrote:

> Hi,
>
> Maybee this
>
> Sub CopySheets()
> Sheets("Sheet2").UsedRange.Copy
> Sheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues
> End Sub
> --
> Mike
>
> When competing hypotheses are otherwise equal, adopt the hypothesis that
> introduces the fewest assumptions while still sufficiently answering the
> question.
>
>
> "Jules" wrote:
>
> > Is there a faster way to copy and paste one entire worksheets data into
> > another existing worksheet? I do NOT want to rename the sheets. Code below is
> > what I'm using now but it seems that there should be a simpler way. Thanks!
> >
> > Sub CopySheets()
> > Sheets("Sheet2").Select
> > Cells.Select
> > Selection.Copy
> > Sheets("Sheet1").Select
> > Range("A1").Select
> > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> > SkipBlanks _
> > :=False, Transpose:=False
> > Application.CutCopyMode = False
> > End Sub
> >
> >

Might be a little faster.

Sub CopySheets()
Sheets("Sheet2").UsedRange.Copy Sheets("Sheet1").Range("A1")
End Sub



"Jules" <(E-Mail Removed)> wrote in message
news:66235D91-9570-4025-8800-(E-Mail Removed)...
> Is there a faster way to copy and paste one entire worksheets data into
> another existing worksheet? I do NOT want to rename the sheets. Code below
> is
> what I'm using now but it seems that there should be a simpler way.
> Thanks!
>
> Sub CopySheets()
> Sheets("Sheet2").Select
> Cells.Select
> Selection.Copy
> Sheets("Sheet1").Select
> Range("A1").Select
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Application.CutCopyMode = False
> End Sub
>
>

Using the UsedRange might not be what the OP wants. If the UsedRange does
not start at A1, then using it in your code will move all the data up so
that the data starts in A1; using Cells as I proposed will keep the moved
data in the same cells they currently are in. Try this experiment with both
options to see the difference. Fill Sheet 1 with some data, then select the
first 5 rows and click Edit/Clear/All on the menu bar (do not just hit the
Delete key) to clear those cells from the UsedRange., then run your code and
watch where Row 6's data (and all the rows after it) end up. Now repeat the
process using my code.... Row 6 and all following rows remain in the same
rows they currently are in on Sheet2.

--
Rick (MVP - Excel)


"JLGWhiz" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Might be a little faster.
>
> Sub CopySheets()
> Sheets("Sheet2").UsedRange.Copy Sheets("Sheet1").Range("A1")
> End Sub
>
>
>
> "Jules" <(E-Mail Removed)> wrote in message
> news:66235D91-9570-4025-8800-(E-Mail Removed)...
>> Is there a faster way to copy and paste one entire worksheets data into
>> another existing worksheet? I do NOT want to rename the sheets. Code
>> below is
>> what I'm using now but it seems that there should be a simpler way.
>> Thanks!
>>
>> Sub CopySheets()
>> Sheets("Sheet2").Select
>> Cells.Select
>> Selection.Copy
>> Sheets("Sheet1").Select
>> Range("A1").Select
>> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
>> SkipBlanks _
>> :=False, Transpose:=False
>> Application.CutCopyMode = False
>> End Sub
>>
>>
>
>

Hi,

It will be faster but the OP noted in the header and example code that it
was values the were to be pasted.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"JLGWhiz" wrote:

> Might be a little faster.
>
> Sub CopySheets()
> Sheets("Sheet2").UsedRange.Copy Sheets("Sheet1").Range("A1")
> End Sub
>
>
>
> "Jules" <(E-Mail Removed)> wrote in message
> news:66235D91-9570-4025-8800-(E-Mail Removed)...
> > Is there a faster way to copy and paste one entire worksheets data into
> > another existing worksheet? I do NOT want to rename the sheets. Code below
> > is
> > what I'm using now but it seems that there should be a simpler way.
> > Thanks!
> >
> > Sub CopySheets()
> > Sheets("Sheet2").Select
> > Cells.Select
> > Selection.Copy
> > Sheets("Sheet1").Select
> > Range("A1").Select
> > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> > SkipBlanks _
> > :=False, Transpose:=False
> > Application.CutCopyMode = False
> > End Sub
> >
> >
>
>
> .
>

The OP used A1 as the destination cell. I just followed their lead.


"Rick Rothstein" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Using the UsedRange might not be what the OP wants. If the UsedRange does
> not start at A1, then using it in your code will move all the data up so
> that the data starts in A1; using Cells as I proposed will keep the moved
> data in the same cells they currently are in. Try this experiment with
> both options to see the difference. Fill Sheet 1 with some data, then
> select the first 5 rows and click Edit/Clear/All on the menu bar (do not
> just hit the Delete key) to clear those cells from the UsedRange., then
> run your code and watch where Row 6's data (and all the rows after it) end
> up. Now repeat the process using my code.... Row 6 and all following rows
> remain in the same rows they currently are in on Sheet2.
>
> --
> Rick (MVP - Excel)
>
>
> "JLGWhiz" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Might be a little faster.
>>
>> Sub CopySheets()
>> Sheets("Sheet2").UsedRange.Copy Sheets("Sheet1").Range("A1")
>> End Sub
>>
>>
>>
>> "Jules" <(E-Mail Removed)> wrote in message
>> news:66235D91-9570-4025-8800-(E-Mail Removed)...
>>> Is there a faster way to copy and paste one entire worksheets data into
>>> another existing worksheet? I do NOT want to rename the sheets. Code
>>> below is
>>> what I'm using now but it seems that there should be a simpler way.
>>> Thanks!
>>>
>>> Sub CopySheets()
>>> Sheets("Sheet2").Select
>>> Cells.Select
>>> Selection.Copy
>>> Sheets("Sheet1").Select
>>> Range("A1").Select
>>> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
>>> SkipBlanks _
>>> :=False, Transpose:=False
>>> Application.CutCopyMode = False
>>> End Sub
>>>
>>>
>>
>>
>

Rick,

Good point about the difference between usedrange and cells but yours and
JLGWhiz options don't paste values as the OP requested.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Rick Rothstein" wrote:

> Using the UsedRange might not be what the OP wants. If the UsedRange does
> not start at A1, then using it in your code will move all the data up so
> that the data starts in A1; using Cells as I proposed will keep the moved
> data in the same cells they currently are in. Try this experiment with both
> options to see the difference. Fill Sheet 1 with some data, then select the
> first 5 rows and click Edit/Clear/All on the menu bar (do not just hit the
> Delete key) to clear those cells from the UsedRange., then run your code and
> watch where Row 6's data (and all the rows after it) end up. Now repeat the
> process using my code.... Row 6 and all following rows remain in the same
> rows they currently are in on Sheet2.
>
> --
> Rick (MVP - Excel)
>
>
> "JLGWhiz" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Might be a little faster.
> >
> > Sub CopySheets()
> > Sheets("Sheet2").UsedRange.Copy Sheets("Sheet1").Range("A1")
> > End Sub
> >
> >
> >
> > "Jules" <(E-Mail Removed)> wrote in message
> > news:66235D91-9570-4025-8800-(E-Mail Removed)...
> >> Is there a faster way to copy and paste one entire worksheets data into
> >> another existing worksheet? I do NOT want to rename the sheets. Code
> >> below is
> >> what I'm using now but it seems that there should be a simpler way.
> >> Thanks!
> >>
> >> Sub CopySheets()
> >> Sheets("Sheet2").Select
> >> Cells.Select
> >> Selection.Copy
> >> Sheets("Sheet1").Select
> >> Range("A1").Select
> >> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> >> SkipBlanks _
> >> :=False, Transpose:=False
> >> Application.CutCopyMode = False
> >> End Sub
> >>
> >>
> >
> >
>
> .
>

Rick,

I meant to add that a neat solution would be

Sheets("Sheet1").Cells.Value Sheets("Sheet2").Cells.Value

Unfortunately; at least on my pc, I get an out of memory error so have to
limit the range with say

Sheets("Sheet1").Range("A1:d20").Value =
Sheets("Sheet2").Range("A1:d20").Value

The latter approach is (i think) frought with danger because you may be able
to set up the correct range by finding the last row and column but there's no
way i know of establishing any memory limitation on the host computer which
is why I don't bother with the 'single line' approach for pasting values.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Rick Rothstein" wrote:

> Using the UsedRange might not be what the OP wants. If the UsedRange does
> not start at A1, then using it in your code will move all the data up so
> that the data starts in A1; using Cells as I proposed will keep the moved
> data in the same cells they currently are in. Try this experiment with both
> options to see the difference. Fill Sheet 1 with some data, then select the
> first 5 rows and click Edit/Clear/All on the menu bar (do not just hit the
> Delete key) to clear those cells from the UsedRange., then run your code and
> watch where Row 6's data (and all the rows after it) end up. Now repeat the
> process using my code.... Row 6 and all following rows remain in the same
> rows they currently are in on Sheet2.
>
> --
> Rick (MVP - Excel)
>
>
> "JLGWhiz" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Might be a little faster.
> >
> > Sub CopySheets()
> > Sheets("Sheet2").UsedRange.Copy Sheets("Sheet1").Range("A1")
> > End Sub
> >
> >
> >
> > "Jules" <(E-Mail Removed)> wrote in message
> > news:66235D91-9570-4025-8800-(E-Mail Removed)...
> >> Is there a faster way to copy and paste one entire worksheets data into
> >> another existing worksheet? I do NOT want to rename the sheets. Code
> >> below is
> >> what I'm using now but it seems that there should be a simpler way.
> >> Thanks!
> >>
> >> Sub CopySheets()
> >> Sheets("Sheet2").Select
> >> Cells.Select
> >> Selection.Copy
> >> Sheets("Sheet1").Select
> >> Range("A1").Select
> >> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> >> SkipBlanks _
> >> :=False, Transpose:=False
> >> Application.CutCopyMode = False
> >> End Sub
> >>
> >>
> >
> >
>
> .
>