different ways to copy a range?

[botha822]

Whats wrong with this syntax?
WsName is a string variable that is assigned the name of a worksheet exists.
n and i are both integer variables that are based off of For statements.

Worksheets(WsName).Range(Cells(5, n), Cells(2415, n)).Copy _
Destination:=Worksheets("Summary").Cells(4, i)

Thanks!

[ducky]

botha822 wrote:
> Whats wrong with this syntax?
> WsName is a string variable that is assigned the name of a worksheet
> exists.
> n and i are both integer variables that are based off of For
> statements.
>
> Worksheets(WsName).Range(Cells(5, n), Cells(2415, n)).Copy _
> Destination:=Worksheets("Summary").Cells(4, i)

Where is your code bugging? Are you sure your variables are being
assigned? i think you should post all of your code

AR

[Ron de Bruin]

Cells use not the cells of the activesheet

Use it like this

Worksheets(wsname).Range(Worksheets(wsname).Cells(5, n), Worksheets(wsname).Cells(2415, n)).Copy _
Destination:=Worksheets("Summary").Cells(4, i)


--
Regards Ron de Bruin
http://www.rondebruin.nl



"botha822" <[email protected]> wrote in message
news:[email protected]...
>
> Whats wrong with this syntax?
> WsName is a string variable that is assigned the name of a worksheet
> exists.
> n and i are both integer variables that are based off of For
> statements.
>
> Worksheets(WsName).Range(Cells(5, n), Cells(2415, n)).Copy _
> Destination:=Worksheets("Summary").Cells(4, i)
>
> Thanks!
>
>
> --
> botha822
> ------------------------------------------------------------------------
> botha822's Profile: http://www.excelforum.com/member.php...o&userid=36752
> View this thread: http://www.excelforum.com/showthread...hreadid=569175
>

[Ron de Bruin]

Oops

> Cells use not the cells of the activesheet

Cells use always the cells from the activesheet now


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" <[email protected]> wrote in message news:exvUI%[email protected]...
> Cells use not the cells of the activesheet
>
> Use it like this
>
> Worksheets(wsname).Range(Worksheets(wsname).Cells(5, n), Worksheets(wsname).Cells(2415, n)).Copy _
> Destination:=Worksheets("Summary").Cells(4, i)
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "botha822" <[email protected]> wrote in message
> news:[email protected]...
>>
>> Whats wrong with this syntax?
>> WsName is a string variable that is assigned the name of a worksheet
>> exists.
>> n and i are both integer variables that are based off of For
>> statements.
>>
>> Worksheets(WsName).Range(Cells(5, n), Cells(2415, n)).Copy _
>> Destination:=Worksheets("Summary").Cells(4, i)
>>
>> Thanks!
>>
>>
>> --
>> botha822
>> ------------------------------------------------------------------------
>> botha822's Profile: http://www.excelforum.com/member.php...o&userid=36752
>> View this thread: http://www.excelforum.com/showthread...hreadid=569175
>>
>
>

[Dave Peterson]

if worksheets(wsname) isn't the activesheet, then you'll have a problem.

Cells(5,n) and cells(2415,n) are each unqualified--you didn't tell excel's VBA
what they belonged to.

If the code is in a General module, then those unqualified ranges will refer to
the activesheet.

One way around it:

with worksheets(wsname)
.range(.cells(5,n),.cells(2415,n)).copy _
destination:=worksheets("summary").cells(4,i)
end with

The dot's in front of .range(), .cells() mean these objects belong to the object
in the previous With statement. In this case, worksheets(wsname).

ps. Instead of using

dim WsName as string
wsname = activesheet.name
or
wsname = worksheets("something").name

you could just use a variable that represents that worksheet.

Dim Ws as worksheet
set ws = activesheet
or
set ws = worksheets("something")

then

with ws
.range(.cells(5,n),.cells(2415,n)).copy _
destination:=worksheets("summary").cells(4,i)
end with

You can always get the name if you want:
msgbox ws.name



botha822 wrote:
>
> Whats wrong with this syntax?
> WsName is a string variable that is assigned the name of a worksheet
> exists.
> n and i are both integer variables that are based off of For
> statements.
>
> Worksheets(WsName).Range(Cells(5, n), Cells(2415, n)).Copy _
> Destination:=Worksheets("Summary").Cells(4, i)
>
> Thanks!
>
> --
> botha822
> ------------------------------------------------------------------------
> botha822's Profile: http://www.excelforum.com/member.php...o&userid=36752
> View this thread: http://www.excelforum.com/showthread...hreadid=569175

--

Dave Peterson

[botha822]

hey thanks for the advice guys. Both replies were useful in different ways. Any other online resources for learning VBA for Excel? I know I don't write very clean code and I would like to improve some more.

Thanks again.

[Dave Peterson]

David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

And he has links to lots of other sites.



botha822 wrote:
>
> hey thanks for the advice guys. Both replies were useful in different
> ways. Any other online resources for learning VBA for Excel? I know I
> don't write very clean code and I would like to improve some more.
>
> Thanks again.
>
> --
> botha822
> ------------------------------------------------------------------------
> botha822's Profile: http://www.excelforum.com/member.php...o&userid=36752
> View this thread: http://www.excelforum.com/showthread...hreadid=569175

--

Dave Peterson