referencing file paths
 

The following code switches from the worksheet "Download" to the tab
"CBOVIIPRIN", which then dumps the data. It then activates the
Download worksheet and then opens a path "Range("M1") that is on the
excel sheet and is created with an imput and the rest is concacted. I
then want to reference the sheet that i opened using "Range("N1")
which is just a shorter reference to the worksheet that i can then use
to "Activate" the sheet as i switch back and forth. Note, the DIFFREC
sub repeats itself several times and would use the same reference as
Range("N1"). Referencing the sheet using Range('N1") does not work.
Also, When i run the program from the DIFFREC sub it works fine, but
when i run it from the previous sub that calls DIFFREC, it will not
open the worksheet. It will also only work if i get rid of the
reference ot RangeN1 and just hardcode the worksheet name. Any ideas
here? sorry its a little confusing. thanks a bunch

ActiveWindow.Close
Windows("DOWNLOAD.xls").Activate
Sheets("CBOVIIPRIN").Select
Selection.CurrentRegion.Select
Selection.ClearContents
x = 1
For y = 0 To rw - 1
Cells(x, 1).Value = arrTicker(0, y)
Cells(x, 2).Value = arrTicker(1, y)
Cells(x, 3).Value = arrTicker(2, y)
x = x + 1
Next
Call DIFFREC
End Sub


Public Sub DIFFREC()
Windows("DOWNLOAD.xls").Activate
Workbooks.Open Filename:=Range("M1").Value
Windows("Download.xls").Activate
Dim cell As Variant
Dim rw As Integer
Dim x As Integer
Dim y As Integer
Dim FRange As Range
'Dim rwCt As Integer
'rwCt = Range("myRange").Count
Dim arrTicker() As String
Dim rTicker() As String
rw = 0 ' array counter
For Each cell In Range("RANGE1")
If Abs(cell.Value) 0.05 Then
ReDim Preserve arrTicker(4, rw)
arrTicker(0, rw) = cell.Offset(0, -12).Value
arrTicker(1, rw) = cell.Offset(0, -11).Value
arrTicker(2, rw) = cell.Offset(0, -5).Value
arrTicker(3, rw) = cell.Offset(0, -4).Value
rw = rw + 1
End If
Next cell
Windows(Range("N1").Value).Activate
Sheets("CBO I").Select
x = 3
For y = 0 To rw - 1
Cells(x, 1).Value = arrTicker(0, y)
Cells(x, 2).Value = arrTicker(1, y)
Cells(x, 3).Value = arrTicker(2, y)
Cells(x, 4).Value = arrTicker(3, y)
x = x + 1

Next

Windows("DOWNLOAD.xls").Activate
Call PRINCREC
End Sub

referencing file paths
 

unqualified range references in a sheet module refer to the sheet that
contains the code. In a general module they refer to the activesheet. I
suspect this is the root of your problem.

Always write your code so it will work without selecting sheets back and
forth. Fully qualify your references. Preface with Activesheet if you need
to reference the active sheet.

--
Regards,
Tom Ogilvy

" wrote:

The following code switches from the worksheet "Download" to the tab
"CBOVIIPRIN", which then dumps the data. It then activates the
Download worksheet and then opens a path "Range("M1") that is on the
excel sheet and is created with an imput and the rest is concacted. I
then want to reference the sheet that i opened using "Range("N1")
which is just a shorter reference to the worksheet that i can then use
to "Activate" the sheet as i switch back and forth. Note, the DIFFREC
sub repeats itself several times and would use the same reference as
Range("N1"). Referencing the sheet using Range('N1") does not work.
Also, When i run the program from the DIFFREC sub it works fine, but
when i run it from the previous sub that calls DIFFREC, it will not
open the worksheet. It will also only work if i get rid of the
reference ot RangeN1 and just hardcode the worksheet name. Any ideas
here? sorry its a little confusing. thanks a bunch

ActiveWindow.Close
Windows("DOWNLOAD.xls").Activate
Sheets("CBOVIIPRIN").Select
Selection.CurrentRegion.Select
Selection.ClearContents
x = 1
For y = 0 To rw - 1
Cells(x, 1).Value = arrTicker(0, y)
Cells(x, 2).Value = arrTicker(1, y)
Cells(x, 3).Value = arrTicker(2, y)
x = x + 1
Next
Call DIFFREC
End Sub


Public Sub DIFFREC()
Windows("DOWNLOAD.xls").Activate
Workbooks.Open Filename:=Range("M1").Value
Windows("Download.xls").Activate
Dim cell As Variant
Dim rw As Integer
Dim x As Integer
Dim y As Integer
Dim FRange As Range
'Dim rwCt As Integer
'rwCt = Range("myRange").Count
Dim arrTicker() As String
Dim rTicker() As String
rw = 0 ' array counter
For Each cell In Range("RANGE1")
If Abs(cell.Value) 0.05 Then
ReDim Preserve arrTicker(4, rw)
arrTicker(0, rw) = cell.Offset(0, -12).Value
arrTicker(1, rw) = cell.Offset(0, -11).Value
arrTicker(2, rw) = cell.Offset(0, -5).Value
arrTicker(3, rw) = cell.Offset(0, -4).Value
rw = rw + 1
End If
Next cell
Windows(Range("N1").Value).Activate
Sheets("CBO I").Select
x = 3
For y = 0 To rw - 1
Cells(x, 1).Value = arrTicker(0, y)
Cells(x, 2).Value = arrTicker(1, y)
Cells(x, 3).Value = arrTicker(2, y)
Cells(x, 4).Value = arrTicker(3, y)
x = x + 1

Next

Windows("DOWNLOAD.xls").Activate
Call PRINCREC
End Sub