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

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

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

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

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

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

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

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