Range problem

[Mark]

Hi NG

I have a problem with ranges

i want to do a copy operation.

Worksheets("TABLES").Activate
Cells(row, column).Select
Selection = Worksheets("TABLES").Cells(row, column)

Worksheets("hidden").Activate
Cells(rowCounter, 1).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


This works but have the anoying side effect that the worksheets are
activated, which causes flickering. So i tried this instead

Worksheets("TABLES").Cells(row, column).Select
Selection.Copy Destination:=Worksheets("hidden").Cells(rowCounter, 1)

Works fine!! there is no flickering, but now for some reason it only works
if the worksheet TABLES is active before i do the operation.

Basically i want to do something like this

Worksheets("TABLES").Range(Cells(row, column)).Copy _
Destination:=Worksheets("hidden").Cells(rowCounter, 1)

But now i get this strange run time error 1004
If i do something like

Worksheets("TABLES").Range("a4").Copy _
Destination:=Worksheets("hidden").Cells(rowCounter, 1)

Then it works without TABLES being active before i do the operation. STRANGE

the documentation clearly states that i can do a Range(Cell(x,y)). I DONT
GET IT


Regards Mark

 

[Bob Phillips]

Mark,

Why not put it all together

Worksheets("TABLES").Cells(row, column).Copy _
Destination:=Worksheets("hidden").Cells(rowCounter, 1)


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Mark]

Thanks Bob!

It worked!! I am still puzzled that Range(cell(row,column)) part of the coed
does not work. After all the doc clearly says it should work. But thats just
acedemia anyway, your solution is more smooth. Thanks again.

Mark

 

[Bob Phillips]

Mark,

A couple of thoughts.

If you select a range, then the sheet that the range is on has to be active,
as the selection has a visual aspect, the screen is re-painted with the
selected cells, so hence it must be the active window for this re-paint to
happen.

Help does not say that Range(cells(row,column)) is valid. In the part on
Range and Cells, it states the form Range(cell1,cell2), in other words you
specify the start and end position of the range. Thus, with your example
Range(cells(row,column),(cells(row,column)) will work, but that is perverse
when you can simply put Cells(row,column), which is also a range.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Mark]

Ok - i get it.
But what if you only have numerical values e.g. for rows and columns. How do
you then specify a range?? I have to use it in a listbox.rowsource method...

Basically i just want to specify some type of "range" withou being bothered
with the re-paint issues. Surely that must be possible in Excel

Regards
Mark

 

[Bob Phillips]

Ok - i get it.
But what if you only have numerical values e.g. for rows and columns. How do
you then specify a range?? I have to use it in a listbox.rowsource

method...

Then you combine it all as I alluded in my previous post (i.e.
Range(cell1,cell2)), like so
Range(Cells(1,2),Cells(2,5)) refers to Range("B1:E2")

Basically i just want to specify some type of "range" withou being bothered
with the re-paint issues. Surely that must be possible in Excel

I showed you that and explained that in the previous post. Don't select,
which is rarely needed anyway, and there is no need to re-paint.

 

[Mark]

;-) thanks for taking the time to answer

Mark

How
method...

Then you combine it all as I alluded in my previous post (i.e.
Range(cell1,cell2)), like so
Range(Cells(1,2),Cells(2,5)) refers to Range("B1:E2")


I showed you that and explained that in the previous post. Don't select,
which is rarely needed anyway, and there is no need to re-paint.