Worksheet().Range(Cell(), Cell()) syntax?
 

Probably another newb question from me, but:

What is the proper syntax to use the Range(Cell(), Cell()) selection
when accessing a different worksheet?

example

On sheet1 I have the code:

Range(Cells(1, "A"), Cells(30, "A").select

This will of course select the range of cells A1:A30 on sheet1

It also works fine if I use

Worksheets("Sheet1").Range(Cells(1, "A"), Cells(30, "A")).select





However, if I use the code to try to select a different sheet, ala:

Worksheets("Sheet2").Range(Cells(1, "A"), Cells(30, "A")).select

I get an error back: "Run-time error '1004': Application-defined or
object-defined error"



Even if I make sheet2 the activesheet the code still returns the same
error.


--
Ouka
------------------------------------------------------------------------
Ouka's Profile: http://www.excelforum.com/member.php...o&userid=23988
View this thread: http://www.excelforum.com/showthread...hreadid=388946

Worksheet().Range(Cell(), Cell()) syntax?
 

Hi Ouka,

you cannot make a selection on a non active sheet.

In any event, making selections is rarely necessary and tends to produce
inefficient code.

Preferable, in my opinion , would be to set an object variable to the range
and manipulate that.

---
Regards,
Norman



"Ouka" wrote in message
...

Probably another newb question from me, but:

What is the proper syntax to use the Range(Cell(), Cell()) selection
when accessing a different worksheet?

example

On sheet1 I have the code:

Range(Cells(1, "A"), Cells(30, "A").select

This will of course select the range of cells A1:A30 on sheet1

It also works fine if I use

Worksheets("Sheet1").Range(Cells(1, "A"), Cells(30, "A")).select





However, if I use the code to try to select a different sheet, ala:

Worksheets("Sheet2").Range(Cells(1, "A"), Cells(30, "A")).select

I get an error back: "Run-time error '1004': Application-defined or
object-defined error"



Even if I make sheet2 the activesheet the code still returns the same
error.


--
Ouka
------------------------------------------------------------------------
Ouka's Profile:
http://www.excelforum.com/member.php...o&userid=23988
View this thread: http://www.excelforum.com/showthread...hreadid=388946

Worksheet().Range(Cell(), Cell()) syntax?
 

don't know what you want to do, but something like this would fill your
range with abcd

Option Explicit
Dim ws As Worksheet
Dim yourRange As Range

Sub test()
Set ws = Worksheets("sheet2")
Set yourRange = ws.Range("a1:a30")
yourRange = "abcd"
End Sub

--


Gary


"Ouka" wrote in message
...

Probably another newb question from me, but:

What is the proper syntax to use the Range(Cell(), Cell()) selection
when accessing a different worksheet?

example

On sheet1 I have the code:

Range(Cells(1, "A"), Cells(30, "A").select

This will of course select the range of cells A1:A30 on sheet1

It also works fine if I use

Worksheets("Sheet1").Range(Cells(1, "A"), Cells(30, "A")).select





However, if I use the code to try to select a different sheet, ala:

Worksheets("Sheet2").Range(Cells(1, "A"), Cells(30, "A")).select

I get an error back: "Run-time error '1004': Application-defined or
object-defined error"



Even if I make sheet2 the activesheet the code still returns the same
error.


--
Ouka
------------------------------------------------------------------------
Ouka's Profile:
http://www.excelforum.com/member.php...o&userid=23988
View this thread: http://www.excelforum.com/showthread...hreadid=388946

Worksheet().Range(Cell(), Cell()) syntax?
 

hmm I'll have to give that a shot. This is what I was trying to d
(code doesnt actually work of course, but you'll see the jist):

Dim I as integer
Dim J as integer

'get the total number of worksheets
J = ActiveWorkbook.Worksheets.Count
' first 4 sheets of workbook are summary sheets, everything after tha
I need to take data from and move to the 1st sheet.
For I = 5 to J

Dim N as integer
N = 0
' finding the last line of data entry & copying from the start of th
data set to the end, will be unique for each worksheet
Do
N = N + 1
Loop Until worksheets(I).Cells(N + 10, "B").value = ""
worksheets(I).Range(Cells(11, "B"), Cells(N, "E").copy

Dim M as integer
M = 0
'pasting the data from the previous sheet to the first available row
Do
M = M + 1
Loop until worksheets(1).cells(M, "A") = ""
worksheets(1).Cells(M, A).paste
Next I 'go to the next worksheet and repea

--
Ouk
-----------------------------------------------------------------------
Ouka's Profile: http://www.excelforum.com/member.php...fo&userid=2398
View this thread: http://www.excelforum.com/showthread.php?threadid=38894

Worksheet().Range(Cell(), Cell()) syntax?
 

Hi Ouka,

Try:

Sub Tester02()

Dim srcRng As Range
Dim destRng As Range
Dim Lrow As Long
Dim i As Long
Dim destSheet As Worksheet
Const firstSht As Long = 5
Const fRow As Long = 10
Const lCol As Long = 5 '(column E)

Set destSht = ActiveWorkbook.Sheets(1)

For i = firstSheet To ActiveWorkbook.Worksheets.Count
With Worksheets(i)
Lrow = .Cells(Rows.Count, "A").End(xlUp).Row

If Lrow fRow Then 'If anything to copy
Set srcRng = .Cells(fRow + 1, "A"). _
Resize(Lrow - fRow, lCol)
Set destRng = destSht.Cells _
(Rows.Count, "A").End(xlUp)(2)
srcRng.Copy Destination:=destRng
End If
End With
Next i

End Sub

---
Regards,
Norman


"Ouka" wrote in message
...

hmm I'll have to give that a shot. This is what I was trying to do
(code doesnt actually work of course, but you'll see the jist):

Dim I as integer
Dim J as integer

'get the total number of worksheets
J = ActiveWorkbook.Worksheets.Count
' first 4 sheets of workbook are summary sheets, everything after that
I need to take data from and move to the 1st sheet.
For I = 5 to J

Dim N as integer
N = 0
' finding the last line of data entry & copying from the start of the
data set to the end, will be unique for each worksheet
Do
N = N + 1
Loop Until worksheets(I).Cells(N + 10, "B").value = ""
worksheets(I).Range(Cells(11, "B"), Cells(N, "E").copy

Dim M as integer
M = 0
'pasting the data from the previous sheet to the first available row
Do
M = M + 1
Loop until worksheets(1).cells(M, "A") = ""
worksheets(1).Cells(M, A).paste
Next I 'go to the next worksheet and repeat


--
Ouka
------------------------------------------------------------------------
Ouka's Profile:
http://www.excelforum.com/member.php...o&userid=23988
View this thread: http://www.excelforum.com/showthread...hreadid=388946

Worksheet().Range(Cell(), Cell()) syntax?
 

Thanks much, it works but I have a few questions since I dont understand
the syntax (never used these procedures before).

1. Why does this code paste the data starting on Row 2 instead of Row
1? I tried playing with some of the variables to change this, but if I
change the code so that the pasting starts on Row 1, I lose the last row
of the copied data...

2. I changed the code to the following:

Dim srcRng As Range
Dim destRng As Range
Dim Lrow As Long
Dim I As Long
Dim destSheet As Worksheet
Dim studyName As Range
Dim N as Integer
Dim O as Integer
Dim M as Integer

Const firstSht As Long = 5
Const fRow As Long = 10
Const lCol As Long = 5 '(column E)

Set destSheet = ActiveWorkbook.Sheets("Hidden2") ' changed the dest
sheet from position-based selection '1' to the name of the sheet
"Hidden2"

For I = firstSht To ActiveWorkbook.Worksheets.Count
With Worksheets(I)
Lrow = .Cells(Rows.Count, "B").End(xlUp).Row 'changed col
from A to B
If Lrow fRow Then 'If anything to copy
Set srcRng = .Cells(fRow + 1, "B").Resize(Lrow - fRow, lCol)
'changed col from A to B
Set destRng = destSheet.Cells(Rows.Count, "B").End(xlUp)(2) 'changed
col from A to B
srcRng.Copy Destination:=destRng

' Following code is to copy the study name, found in cell B2 on every
data worksheet, into col A on the destination sheet for every entry
being copied from dataworksheets
Set Studyname = .cells(2, "B")

'Finds the last line of data entered from the current worksheet(I) onto
worksheet("Hidden2"). I have to start with N = 1 because the previous
code starts pasting on Row 2 instead of 1...
N =1
Do
N = N + 1
Loop until Worksheets(1).cells(N, "B").value = ""

'Finds the first empty cell in col A on worksheet("Hidden2"). Again,
starting with M = 1 because of the the pasting starting on Row 2...
M = 1
Do
M = M + 1
Loop Until Worksheets(1).cells(M + 1, "A").value = ""
'Sets the value of the empty cells in col A to the
worksheets(I).Range("B2").value
For O = M to N
Worksheets(1).cells(O, "A").value = studyName
Next O
End if
End With
Next I
End Sub

A related problem is that when the code reaches the end of the last
worksheet, it inserts an extra line of the
worksheets(I).Range("B2").value in col A of "Hidden2"

I assume this is related to the tweaking I did to make the variables
count the proper start points because of the pasting begining on row 2,
but I'm unsure....


--
Ouka
------------------------------------------------------------------------
Ouka's Profile: http://www.excelforum.com/member.php...o&userid=23988
View this thread: http://www.excelforum.com/showthread...hreadid=388946

Worksheet().Range(Cell(), Cell()) syntax?
 

Hi Ouka,

1. Why does this code paste the data starting on Row 2 instead of Row
1? I tried playing with some of the variables to change this, but if I
change the code so that the pasting starts on Row 1, I lose the last row
of the copied data...

To allow for a header row, if one does not exist.

Since you do not need this, change:

Set destRng = destSht.Cells _
(Rows.Count, "A").End(xlUp)(2)

to:

Set destRng = destSht.Cells _
(Rows.Count, "A").End(xlUp)
If destRng.Row 1 Then Set destRng = destRng(2)


---
Regards,
Norman



"Ouka" wrote in message
...

Thanks much, it works but I have a few questions since I dont understand
the syntax (never used these procedures before).

1. Why does this code paste the data starting on Row 2 instead of Row
1? I tried playing with some of the variables to change this, but if I
change the code so that the pasting starts on Row 1, I lose the last row
of the copied data...

2. I changed the code to the following:

Dim srcRng As Range
Dim destRng As Range
Dim Lrow As Long
Dim I As Long
Dim destSheet As Worksheet
Dim studyName As Range
Dim N as Integer
Dim O as Integer
Dim M as Integer

Const firstSht As Long = 5
Const fRow As Long = 10
Const lCol As Long = 5 '(column E)

Set destSheet = ActiveWorkbook.Sheets("Hidden2") ' changed the dest
sheet from position-based selection '1' to the name of the sheet
"Hidden2"

For I = firstSht To ActiveWorkbook.Worksheets.Count
With Worksheets(I)
Lrow = .Cells(Rows.Count, "B").End(xlUp).Row 'changed col
from A to B
If Lrow fRow Then 'If anything to copy
Set srcRng = .Cells(fRow + 1, "B").Resize(Lrow - fRow, lCol)
'changed col from A to B
Set destRng = destSheet.Cells(Rows.Count, "B").End(xlUp)(2) 'changed
col from A to B
srcRng.Copy Destination:=destRng

' Following code is to copy the study name, found in cell B2 on every
data worksheet, into col A on the destination sheet for every entry
being copied from dataworksheets
Set Studyname = .cells(2, "B")

'Finds the last line of data entered from the current worksheet(I) onto
worksheet("Hidden2"). I have to start with N = 1 because the previous
code starts pasting on Row 2 instead of 1...
N =1
Do
N = N + 1
Loop until Worksheets(1).cells(N, "B").value = ""

'Finds the first empty cell in col A on worksheet("Hidden2"). Again,
starting with M = 1 because of the the pasting starting on Row 2...
M = 1
Do
M = M + 1
Loop Until Worksheets(1).cells(M + 1, "A").value = ""
'Sets the value of the empty cells in col A to the
worksheets(I).Range("B2").value
For O = M to N
Worksheets(1).cells(O, "A").value = studyName
Next O
End if
End With
Next I
End Sub

A related problem is that when the code reaches the end of the last
worksheet, it inserts an extra line of the
worksheets(I).Range("B2").value in col A of "Hidden2"

I assume this is related to the tweaking I did to make the variables
count the proper start points because of the pasting begining on row 2,
but I'm unsure....


--
Ouka
------------------------------------------------------------------------
Ouka's Profile:
http://www.excelforum.com/member.php...o&userid=23988
View this thread: http://www.excelforum.com/showthread...hreadid=388946

Worksheet().Range(Cell(), Cell()) syntax?
 

Thank you, that did it, and I solved the other issue after some trial
and error. Now I'm puzzling out how to sort my Hidden2 sheet based on
the values in col B. Not an easy task since I don't fully understand
the syntax you used to accomplish the copy/paste selections! But I
figure if I can puzzle this out, I'll be able to use the code properly
from here on out ^_^

If I may ask, -why- is it that you cannot make a selection from a sheet
other than the current active sheet? It seems like a rather silly
restriction from my, admittedly very novice, point of view.

It seems like you have to go thru a rather painful exersize just to
specifiy range selections on other pages when it would be so much
easier if the code syntax would just be universal in function. What
benefit does VBA gain by existing as it does now?


--
Ouka
------------------------------------------------------------------------
Ouka's Profile: http://www.excelforum.com/member.php...o&userid=23988
View this thread: http://www.excelforum.com/showthread...hreadid=388946

Worksheet().Range(Cell(), Cell()) syntax?
 

Hi Ouka,

If I may ask, -why- is it that you cannot make a selection from a sheet
other than the current active sheet?

That is the way Excel is built.

It seems like a rather silly
restriction from my, admittedly very novice, point of view.

If you are in one room, it would be rather difficult to paint another. Silly
possibly, but true.

It seems like you have to go thru a rather painful exersize just to
specifiy range selections on other pages when it would be so much
easier if the code syntax would just be universal in function.

What is painful about specifying the sheet to manipulate and, as for the
selection approach being easier, I am afraid that we will have to differ.

Do you really prefer:

Sheets!(2).Select
Columns(2).select
Selection.Delete

to :

Sheets(2).Colums(2).Delete

What benefit does VBA gain by existing as it does now?

The flexibility to manipulate objects as desired. from the comfort of my
armchair, without the exhaustion involved in running here there and
everywhere, not to mention the saving in shoe leather.


---
Regards,
Norman



"Ouka" wrote in message
...

Thank you, that did it, and I solved the other issue after some trial
and error. Now I'm puzzling out how to sort my Hidden2 sheet based on
the values in col B. Not an easy task since I don't fully understand
the syntax you used to accomplish the copy/paste selections! But I
figure if I can puzzle this out, I'll be able to use the code properly
from here on out ^_^

If I may ask, -why- is it that you cannot make a selection from a sheet
other than the current active sheet? It seems like a rather silly
restriction from my, admittedly very novice, point of view.

It seems like you have to go thru a rather painful exersize just to
specifiy range selections on other pages when it would be so much
easier if the code syntax would just be universal in function. What
benefit does VBA gain by existing as it does now?


--
Ouka
------------------------------------------------------------------------
Ouka's Profile:
http://www.excelforum.com/member.php...o&userid=23988
View this thread: http://www.excelforum.com/showthread...hreadid=388946

Worksheet().Range(Cell(), Cell()) syntax?
 

Well, like I said, I'm a total novice at this stuff =P

I was talking more about what caused my problem to begin with, that th
method I was used to using in identifying a particular range simply di
not work when referencing another sheet.

The Sheets(2).Colums(2).Delete is exactly what I wanted to do, excep
in the format of :

worksheets(2).Range(Cells(I, 1), Cells(J, 5)).copy


I guess I just don't know enough yet to see why the method you laid ou
(i.e. diminsioning ranges instead of using them directly) is mor
flexible than how I though the code would have worked.

Anyway thanks for the time and the help. The overall development o
this app is rolling along much smoother now that I know the prope
syntax (if not understanding the why of it!) to reference non-activ
sheets and ranges properly

--
Ouk
-----------------------------------------------------------------------
Ouka's Profile: http://www.excelforum.com/member.php...fo&userid=2398
View this thread: http://www.excelforum.com/showthread.php?threadid=38894

Worksheet().Range(Cell(), Cell()) syntax?
 

Saw this as unanswered and typed this before checking ...

That all seems very complex code for the required solution ...

The Cells(1,"A") reference require a base worksheet. by default this is
the active sheet but when you begin including a sheet reference you need
to be explicit:

use :

With ActiveWorkbook.Sheets("Sheet 1")
Sheets("Sheet 2").Range(.Cells(1,1),.Cells(5,10)).Copy

wend


or use :


Sheets("Sheet1").Range(Sheets("Sheet2").Cells(1,1) ,Sheets("Sheet2").Cells(5,10)).Copy


Note that you cannot use the Sheet2 notation when referencing a
workbook other than that in which the code resides. And using the
Sheet(2) is very prone to errors (i.e. if the user re-locates the sheet
in the Tab-order or inserts another sheet ...) (but use the Sheet2
notation where possible (prevents user sheet names from messing up your
code))


--
optionbase1
------------------------------------------------------------------------
optionbase1's Profile: http://www.excelforum.com/member.php...o&userid=25212
View this thread: http://www.excelforum.com/showthread...hreadid=388946