Hi,
Try something like this
Dim CopyRange As Range
Dim DestCell As Range
Dim FromSheet As Worksheet
Dim ToSheet As Worksheet
Set FromSheet = ThisWorkbook.Worksheets("Sheet1")
Set ToSheet = ThisWorkbook.Worksheets("Sheet2")
Set CopyRange = FromSheet.Range("a2:z223")
Set DestCell = ToSheet.Range("a1")
With DestCell
'unmege destination range
.Resize(CopyRange.Rows.Count, CopyRange.Columns.Count).UnMerge
'method 1
CopyRange.Copy
.PasteSpecial xlPasteValues
Application.CutCopyMode = False
End With
'method 2
CopyRange.Copy DestCell
Kris
On Thursday, 1 March 2012 20:00:57 UTC+5:30, Sam Mathai Chacko wrote:
>
> Can you post the exact range from where you copy and where to you copy?
>
> On Thu, Mar 1, 2012 at 7:33 PM, Scruffy Huffy <tuffhu...@yahoo.com> wrote:
>
>> Sam,
>>
>> I'm getting a run-time error '438': Object doesn't support this
>> property or method.
>>
>> Thanks for the advice, though.
>>
>> Robb
>>
>> On Mar 1, 8:49 am, Sam Mathai Chacko <samde...@gmail.com> wrote:
>> > Not tested, but try this
>> >
>> > range("a2:z223").copy
>> > with
>> >
>> range("a1").resize(range("a2:z223").rows.count,range("a2:z223").columns.cou
>> nt)
>> > .unmerge
>> > .paste pastevalues
>> > end with
>> >
>> > Regards,
>> > Sam Mathai Chacko
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > On Thu, Mar 1, 2012 at 6:21 PM, Scruffy Huffy <tuffhu...@yahoo.com>
>> wrote:
>> > > Don,
>> >
>> > > I did this and got this message: "run-time error '1004': Method
>> > > 'Range' of object'_Global' failed"
>> >
>> > > I'm definitely a novice when it comes to this....anything obvious I'm
>> > > missing?
>> >
>> > > Thanks.
>> >
>> > > Robb
>> >
>> > > On Feb 29, 4:55 pm, "dguillett1" <dguille...@gmail.com> wrote:
>> > > > range("a2:z223").copy
>> > > > range("a1").paste pastevalues
>> >
>> > > > Don Guillett
>> > > > SalesAid Software
>> > > > dguille...@gmail.com
>> >
>> > > > -----Original Message-----
>> > > > From: Scruffy Huffy
>> > > > Sent: Wednesday, February 29, 2012 3:51 PM
>> > > > To: MS EXCEL AND VBA MACROS
>> > > > Subject: Re: $$Excel-Macros$$ "Un-selecting" a cell being copied
>> >
>> > > > Don,
>> >
>> > > > Thanks for the suggestion. Any ideas on what to do when the source
>> > > > and destination are merged cells of different sizes?
>> >
>> > > > Right now, I'm copying the source value to a random cell in the
>> > > > workbook, and then copying that value to the destination merged
>> > > > cells. That just seems clumsy, though.
>> >
>> > > > Any better ideas?
>> >
>> > > > Robb
>> >
>> > > > On Feb 29, 3:00 pm, "dguillett1" <dguille...@gmail.com> wrote:
>> > > > > This will copy just the values from the source to the dest. Ranges
>> > > MUST be
>> > > > > the same size
>> >
>> > > > > range(destination).value=range(source).value
>> > > > > This is much better and the same as
>> >
>> > > > > range(source).copy
>> > > > > range(dest).paste.pastespecial
>> >
>> > > > > Don Guillett
>> > > > > SalesAid Software
>> > > > > dguille...@gmail.com
>> >
>> > > > > -----Original Message-----
>> > > > > From: Scruffy Huffy
>> > > > > Sent: Wednesday, February 29, 2012 1:41 PM
>> > > > > To: MS EXCEL AND VBA MACROS
>> > > > > Subject: Re: $$Excel-Macros$$ "Un-selecting" a cell being copied
>> >
>> > > > > Sam,
>> >
>> > > > > I've already tried that....but it doesn't remove the animation
>> around
>> > > > > the cell that I copied from. I guess I really need a
>> "double-click"
>> > > > > option, rather than just a "select" option. I could select fifty
>> > > > > different cells in succession, and still not remove the original
>> > > > > animated cursors.
>> >
>> > > > > Also (since you're being so helpful), any ideas on how to paste
>> JUST
>> > > > > the value of cell? I'm sure there's a "paste special" action
>> that I'm
>> > > > > missing somewhere, but I'm not having great success. (This is
>> > > > > necessary because I need four separate options copied and pasted
>> as
>> > > > > static totals: One formula performs the calculations for each
>> option,
>> > > > > but the total can't be "live linked" to each option).
>> >
>> > > > > Thanks for your time.
>> >
>> > > > > Robb
>> >
>> > > > > On Feb 29, 1:52 pm, Sam Mathai Chacko <samde...@gmail.com> wrote:
>> > > > > > The trick is to select another cell... at any given time, at
>> least
>> > > one
>> > > > > > cell
>> > > > > > WILL be selected (if not any other object) in a sheet. So at
>> the end
>> > > of
>> > > > > > your macro, select any other cell, for example the top left
>> cell A1
>> >
>> > > > > > Sam Mathai Chacko
>> >
>> > > > > > On Thu, Mar 1, 2012 at 12:12 AM, Scruffy Huffy <
>> tuffhu...@yahoo.com>
>> > > > > > wrote:
>> > > > > > > This problem is a bit difficult to describe, but here goes.
>> I'm
>> > > > > > > using a command button macro to select a cell, copy its
>> value, then
>> > > > > > > past the value to another cell.
>> >
>> > > > > > > However, I don't know the code to "de-select" the copied
>> cell. In
>> > > > > > > other words, after the copy/paste is complete, the cell that I
>> > > copied
>> > > > > > > from is still highlighted (has the animated "ant trails")
>> going
>> > > around
>> > > > > > > it.
>> >
>> > > > > > > I'm sure there's a simple solution. Thanks in advance for
>> your
>> > > help!
>> >
>> > > > > > > Robb
>> >
>> > > > > > > --
>> > > > > > > FORUM RULES (986+ members already BANNED for violation)
>> >
>> > > > > > > 1) Use concise, accurate thread titles. Poor thread titles,
>> like
>> > > > > > > Please
>> > > > > > > Help, Urgent, Need Help, Formula Problem, Code Problem, and
>> Need
>> > > > > > > Advice
>> > > > > > > will not get quick attention or may not be answered.
>> >
>> > > > > > > 2) Don't post a question in the thread of another member.
>> >
>> > > > > > > 3) Don't post questions regarding breaking or bypassing any
>> > > security
>> > > > > > > measure.
>> >
>> > > > > > > 4) Acknowledge the responses you receive, good or bad.
>> >
>> > > > > > > 5) Cross-promotion of, or links to, forums competitive to
>> this
>> > > forum
>> > > > > > > in
>> > > > > > > signatures are prohibited.
>> >
>> > > > > > > NOTE : Don't ever post personal or confidential data in a
>> > > workbook.
>> > > > > > > Forum
>> > > > > > > owners and members are not responsible for any loss.
>> >
>> > >
>> ---------------------------------------------------------------------------
>> > > > > > > ---------------------------
>> > > > > > > To post to this group, send email to
>> excel-macros@googlegroups.com
>> >
>> > > > > > --
>> > > > > > Sam Mathai Chacko
>> >
>> > > > > --
>> > > > > FORUM RULES (986+ members already BANNED for violation)
>> >
>> > > > > 1) Use concise, accurate thread titles. Poor thread titles, like
>> Please
>> > > > > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need
>> Advice
>> > > > > will
>> > > > > not get quick attention or may not be answered.
>> >
>> > > > > 2) Don't post a question in the thread of another member.
>> >
>> > > > > 3) Don't post questions regarding breaking or bypassing any
>> security
>> > > > > measure.
>> >
>> > > > > 4) Acknowledge the responses you receive, good or bad.
>> >
>> > > > > 5) Cross-promotion of, or links to, forums competitive to this
>> forum
>> > > in
>> > > > > signatures are prohibited.
>> >
>> > > > > NOTE : Don't ever post personal or confidential data in a
>> workbook.
>> > > Forum
>> > > > > owners and members are not responsible for any loss.
>> >
>> > >
>> ---------------------------------------------------------------------------
>> > > > > ---------------------------
>> > > > > To post to this group, send email to
>> excel-macros@googlegroups.com
>> >
>> > > > --
>> > > > FORUM RULES (986+ members already BANNED for violation)
>> >
>> > > > 1) Use concise, accurate thread titles. Poor thread titles, like
>> Please
>> > > > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need
>> Advice
>> > > will
>> > > > not get quick attention or may not be answered.
>> >
>> > > > 2) Don't post a question in the thread of another member.
>> >
>> > > > 3) Don't post questions regarding breaking or bypassing any security
>> > > > measure.
>> >
>> > > > 4) Acknowledge the responses you receive, good or bad.
>> >
>> > > > 5) Cross-promotion of, or links to, forums competitive to this
>> forum in
>> > > > signatures are prohibited.
>> >
>> > > > NOTE : Don't ever post personal or confidential data in a workbook.
>> > > Forum
>> > > > owners and members are not responsible for any loss.
>> >
>> > >
>> ---------------------------------------------------------------------------
>> > > ---------------------------
>> > > > To post to this group, send email to excel-macros@googlegroups.com
>> >
>> > > --
>> > > FORUM RULES (986+ members already BANNED for violation)
>> >
>> > > 1) Use concise, accurate thread titles. Poor thread titles, like
>> Please
>> > > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need
>> Advice
>> > > will not get quick attention or may not be answered.
>> >
>> > > 2) Don't post a question in the thread of another member.
>> >
>> > > 3) Don't post questions regarding breaking or bypassing any security
>> > > measure.
>> >
>> > > 4) Acknowledge the responses you receive, good or bad.
>> >
>> > > 5) Cross-promotion of, or links to, forums competitive to this forum
>> in
>> > > signatures are prohibited.
>> >
>> > > NOTE : Don't ever post personal or confidential data in a workbook.
>> Forum
>> > > owners and members are not responsible for any loss.
>> >
>> > >
>> ---------------------------------------------------------------------------
>> ---------------------------
>> > > To post to this group, send email to excel-macros@googlegroups.com
>> >
>> > --
>> > Sam Mathai Chacko
>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook.
>> Forum owners and members are not responsible for any loss.
>>
>>
>> ------------------------------------------------------------------------------------------------------
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>
>
>
> --
> Sam Mathai Chacko
>
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
