different ways to copy a range?

  • Thread starter Thread starter botha822
  • Start date Start date
B

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!
 
botha822 said:
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)
Click to expand...

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

AR
 
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)
 
Oops
Cells use not the cells of the activesheet
Click to expand...

Cells use always the cells from the activesheet now


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



Ron de Bruin said:
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)
Click to expand...
 
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
 
hey thanks for the advice guys. Both replies were useful in differen
ways. Any other online resources for learning VBA for Excel? I know
don't write very clean code and I would like to improve some more.

Thanks again
 
Back
Top