[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

copy and paste values in a range

DaveF

12/14/2006 6:35:00 PM

This should be a simple VBA routine:

I have a range, B4:AD49

I want to copy the range and paste its values in another range. The upper
left hand corner of this new range would be B53 of the same worksheet. How
would I do this via VBA?

(Why am I doing this? Because this sheet is part of a larger model, and the
sheet is being used by a novice to Excel who needs only the values in the
aforementioned range. I figure copying this range and pasting its values
elsewhere on the sheet is the easiest way to idiot-proof the data. Once I
figure out how to copy and paste the values in range B4:AD49, I'll then have
the macro hide rows that don't need to be viewed by the novice user.)
--
Brevity is the soul of wit.
7 Answers

keri

12/14/2006 6:45:00 PM

0

Lets presume the range you want copy is always in B4:AD49. Try
something like this although don't shout if it doesn't work, i'm new to
code myself!


Sub copythesecells()

Sheets("sheet1").Select 'change the name of sheet1 to the name of
your sheet
Sheets("sheet1").Range("b4:ad49").Select
Selection.Copy
Sheets("sheet1").range("b53").Select
ActiveSheet.Paste

End Sub


Dave F wrote:
> This should be a simple VBA routine:
>
> I have a range, B4:AD49
>
> I want to copy the range and paste its values in another range. The upper
> left hand corner of this new range would be B53 of the same worksheet. How
> would I do this via VBA?
>
> (Why am I doing this? Because this sheet is part of a larger model, and the
> sheet is being used by a novice to Excel who needs only the values in the
> aforementioned range. I figure copying this range and pasting its values
> elsewhere on the sheet is the easiest way to idiot-proof the data. Once I
> figure out how to copy and paste the values in range B4:AD49, I'll then have
> the macro hide rows that don't need to be viewed by the novice user.)
> --
> Brevity is the soul of wit.

Dave Peterson

12/14/2006 6:58:00 PM

0

with worksheets("sheet9999")
.range("b4:ad49").copy _
destination:=.range("b53")
end with

If you really only need values:

dim RngToCopy as range
with worksheets("sheet9999")
set rngtocopy = .range("b4:ad49")
.range("b53").resize(rngtocopy.rows.count,rngtocopy.columns.count).value _
= rngtocopy.value
end with

Or
with worksheets("sheet9999")
.range("b4:ad49").copy
.range("b53").pastespecial paste:=xlpastespecialvalues
end with



Dave F wrote:
>
> This should be a simple VBA routine:
>
> I have a range, B4:AD49
>
> I want to copy the range and paste its values in another range. The upper
> left hand corner of this new range would be B53 of the same worksheet. How
> would I do this via VBA?
>
> (Why am I doing this? Because this sheet is part of a larger model, and the
> sheet is being used by a novice to Excel who needs only the values in the
> aforementioned range. I figure copying this range and pasting its values
> elsewhere on the sheet is the easiest way to idiot-proof the data. Once I
> figure out how to copy and paste the values in range B4:AD49, I'll then have
> the macro hide rows that don't need to be viewed by the novice user.)
> --
> Brevity is the soul of wit.

--

Dave Peterson

Gary''s Student

12/14/2006 7:03:00 PM

0

Sub copyit()
Dim r1 As Range, r2 As Range
Set r1 = Range("B4:AD49")
Set r2 = Range("B53")
r1.Copy r2
End Sub
--
Gary's Student


"Dave F" wrote:

> This should be a simple VBA routine:
>
> I have a range, B4:AD49
>
> I want to copy the range and paste its values in another range. The upper
> left hand corner of this new range would be B53 of the same worksheet. How
> would I do this via VBA?
>
> (Why am I doing this? Because this sheet is part of a larger model, and the
> sheet is being used by a novice to Excel who needs only the values in the
> aforementioned range. I figure copying this range and pasting its values
> elsewhere on the sheet is the easiest way to idiot-proof the data. Once I
> figure out how to copy and paste the values in range B4:AD49, I'll then have
> the macro hide rows that don't need to be viewed by the novice user.)
> --
> Brevity is the soul of wit.

Gary Keramidas

12/14/2006 7:13:00 PM

0

just curious, what would be wrong with just using 1 line like this?

Range("B4:AD49").Copy Range("B53")

--


Gary


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:45819E9A.2175FF6B@verizonXSPAM.net...
> with worksheets("sheet9999")
> .range("b4:ad49").copy _
> destination:=.range("b53")
> end with
>
> If you really only need values:
>
> dim RngToCopy as range
> with worksheets("sheet9999")
> set rngtocopy = .range("b4:ad49")
> .range("b53").resize(rngtocopy.rows.count,rngtocopy.columns.count).value _
> = rngtocopy.value
> end with
>
> Or
> with worksheets("sheet9999")
> .range("b4:ad49").copy
> .range("b53").pastespecial paste:=xlpastespecialvalues
> end with
>
>
>
> Dave F wrote:
>>
>> This should be a simple VBA routine:
>>
>> I have a range, B4:AD49
>>
>> I want to copy the range and paste its values in another range. The upper
>> left hand corner of this new range would be B53 of the same worksheet. How
>> would I do this via VBA?
>>
>> (Why am I doing this? Because this sheet is part of a larger model, and the
>> sheet is being used by a novice to Excel who needs only the values in the
>> aforementioned range. I figure copying this range and pasting its values
>> elsewhere on the sheet is the easiest way to idiot-proof the data. Once I
>> figure out how to copy and paste the values in range B4:AD49, I'll then have
>> the macro hide rows that don't need to be viewed by the novice user.)
>> --
>> Brevity is the soul of wit.
>
> --
>
> Dave Peterson


Dave Peterson

12/14/2006 8:37:00 PM

0

Absolutely nothing.

I just like to fully qualify my ranges (with worksheets(...)) and I like to use
named parameters. And I like two lines--just for ease of reading.



Gary Keramidas wrote:
>
> just curious, what would be wrong with just using 1 line like this?
>
> Range("B4:AD49").Copy Range("B53")
>
> --
>
> Gary
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:45819E9A.2175FF6B@verizonXSPAM.net...
> > with worksheets("sheet9999")
> > .range("b4:ad49").copy _
> > destination:=.range("b53")
> > end with
> >
> > If you really only need values:
> >
> > dim RngToCopy as range
> > with worksheets("sheet9999")
> > set rngtocopy = .range("b4:ad49")
> > .range("b53").resize(rngtocopy.rows.count,rngtocopy.columns.count).value _
> > = rngtocopy.value
> > end with
> >
> > Or
> > with worksheets("sheet9999")
> > .range("b4:ad49").copy
> > .range("b53").pastespecial paste:=xlpastespecialvalues
> > end with
> >
> >
> >
> > Dave F wrote:
> >>
> >> This should be a simple VBA routine:
> >>
> >> I have a range, B4:AD49
> >>
> >> I want to copy the range and paste its values in another range. The upper
> >> left hand corner of this new range would be B53 of the same worksheet. How
> >> would I do this via VBA?
> >>
> >> (Why am I doing this? Because this sheet is part of a larger model, and the
> >> sheet is being used by a novice to Excel who needs only the values in the
> >> aforementioned range. I figure copying this range and pasting its values
> >> elsewhere on the sheet is the easiest way to idiot-proof the data. Once I
> >> figure out how to copy and paste the values in range B4:AD49, I'll then have
> >> the macro hide rows that don't need to be viewed by the novice user.)
> >> --
> >> Brevity is the soul of wit.
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

Gary Keramidas

12/14/2006 9:32:00 PM

0

ok, i understand the qualifying of the ranges and do use it. i was just curious
about using, or not using, the named parameters.

--


Gary


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:4581B5D7.B403950E@verizonXSPAM.net...
> Absolutely nothing.
>
> I just like to fully qualify my ranges (with worksheets(...)) and I like to
> use
> named parameters. And I like two lines--just for ease of reading.
>
>
>
> Gary Keramidas wrote:
>>
>> just curious, what would be wrong with just using 1 line like this?
>>
>> Range("B4:AD49").Copy Range("B53")
>>
>> --
>>
>> Gary
>>
>> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
>> news:45819E9A.2175FF6B@verizonXSPAM.net...
>> > with worksheets("sheet9999")
>> > .range("b4:ad49").copy _
>> > destination:=.range("b53")
>> > end with
>> >
>> > If you really only need values:
>> >
>> > dim RngToCopy as range
>> > with worksheets("sheet9999")
>> > set rngtocopy = .range("b4:ad49")
>> > .range("b53").resize(rngtocopy.rows.count,rngtocopy.columns.count).value
>> > _
>> > = rngtocopy.value
>> > end with
>> >
>> > Or
>> > with worksheets("sheet9999")
>> > .range("b4:ad49").copy
>> > .range("b53").pastespecial paste:=xlpastespecialvalues
>> > end with
>> >
>> >
>> >
>> > Dave F wrote:
>> >>
>> >> This should be a simple VBA routine:
>> >>
>> >> I have a range, B4:AD49
>> >>
>> >> I want to copy the range and paste its values in another range. The upper
>> >> left hand corner of this new range would be B53 of the same worksheet.
>> >> How
>> >> would I do this via VBA?
>> >>
>> >> (Why am I doing this? Because this sheet is part of a larger model, and
>> >> the
>> >> sheet is being used by a novice to Excel who needs only the values in the
>> >> aforementioned range. I figure copying this range and pasting its values
>> >> elsewhere on the sheet is the easiest way to idiot-proof the data. Once I
>> >> figure out how to copy and paste the values in range B4:AD49, I'll then
>> >> have
>> >> the macro hide rows that don't need to be viewed by the novice user.)
>> >> --
>> >> Brevity is the soul of wit.
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson


Dave Peterson

12/14/2006 10:18:00 PM

0

I like named parms -- especially when the alternative is something like:

xxxx.yyyyy , , , , , , true, , , 0 , false, 1, 3



Gary Keramidas wrote:
>
> ok, i understand the qualifying of the ranges and do use it. i was just curious
> about using, or not using, the named parameters.
>
> --
>
> Gary
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:4581B5D7.B403950E@verizonXSPAM.net...
> > Absolutely nothing.
> >
> > I just like to fully qualify my ranges (with worksheets(...)) and I like to
> > use
> > named parameters. And I like two lines--just for ease of reading.
> >
> >
> >
> > Gary Keramidas wrote:
> >>
> >> just curious, what would be wrong with just using 1 line like this?
> >>
> >> Range("B4:AD49").Copy Range("B53")
> >>
> >> --
> >>
> >> Gary
> >>
> >> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> >> news:45819E9A.2175FF6B@verizonXSPAM.net...
> >> > with worksheets("sheet9999")
> >> > .range("b4:ad49").copy _
> >> > destination:=.range("b53")
> >> > end with
> >> >
> >> > If you really only need values:
> >> >
> >> > dim RngToCopy as range
> >> > with worksheets("sheet9999")
> >> > set rngtocopy = .range("b4:ad49")
> >> > .range("b53").resize(rngtocopy.rows.count,rngtocopy.columns.count).value
> >> > _
> >> > = rngtocopy.value
> >> > end with
> >> >
> >> > Or
> >> > with worksheets("sheet9999")
> >> > .range("b4:ad49").copy
> >> > .range("b53").pastespecial paste:=xlpastespecialvalues
> >> > end with
> >> >
> >> >
> >> >
> >> > Dave F wrote:
> >> >>
> >> >> This should be a simple VBA routine:
> >> >>
> >> >> I have a range, B4:AD49
> >> >>
> >> >> I want to copy the range and paste its values in another range. The upper
> >> >> left hand corner of this new range would be B53 of the same worksheet.
> >> >> How
> >> >> would I do this via VBA?
> >> >>
> >> >> (Why am I doing this? Because this sheet is part of a larger model, and
> >> >> the
> >> >> sheet is being used by a novice to Excel who needs only the values in the
> >> >> aforementioned range. I figure copying this range and pasting its values
> >> >> elsewhere on the sheet is the easiest way to idiot-proof the data. Once I
> >> >> figure out how to copy and paste the values in range B4:AD49, I'll then
> >> >> have
> >> >> the macro hide rows that don't need to be viewed by the novice user.)
> >> >> --
> >> >> Brevity is the soul of wit.
> >> >
> >> > --
> >> >
> >> > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson