I guess I don't know the proper way to reference variables in code..
can anyone troubleshoot this routine for me? GREATLY appreciated...
get errors almost every time I try to use a variable in code. fo
example,
Workbooks(fname).Activate gives me an error. If I eliminate this cod
by just activating a specific workbook instead of the one defined b
fname, I get an error somewhere else where I tried to use a variable
like at
Set destCell = destWks.Cells(3, dcol) where VB says I didn't define th
variable yet.

Sub get1degdata()
'
' get1degdata Macro
' Macro recorded 11/19/2004 by Kieran Coghlan
'
'
Dim ramp As Variant
Dim toes As Variant
Dim fname As Variant
Dim Wkbk As Workbook
Dim wksht As Worksheet
Dim destWks As Worksheet
Dim destCell As Range
Dim dcol As Integer
fname = Application.GetOpenFilename("Excel files(*.xls),*.xls")
Workbooks.Open (fname)
Workbooks(fname).Activate
' Sheets("charts").Select
Set Wkbk = Workbooks(fname)
ramp = InputBox("Enter the ramp duration in ms: 500, 1000, 2000, o
4000")
toes = InputBox("Enter the toes direction, UP or DOWN")
Sheets.Add.Name = ramp
Worksheets(ramp).Select
dcol = 1
For Each wksht In Wkbk.Worksheets
If wksht.Range("K5") = ramp And wksht.Range("G7") = toes Then
Set destWks = Wkbk.Worksheets(ramp)
wksht.Range("q12:q2011").Copy
End If
Set destCell = destWks.Cells(3, dcol)
destCell.PasteSpecial Paste:=xlPasteValues, Operation _
:=xlNone, SkipBlanks:=False, Transpose:=False
dcol = dcol + 1
Next
End Sub

Thanks,
Kiera

--
Kieran102
-----------------------------------------------------------------------
Kieran1028's Profile: http://www.excelforum.com/member.php...fo&userid=1567
View this thread: http://www.excelforum.com/showthread.php?threadid=31936

In your code, fname contains the entire full file name. You
can't use this in the Workbooks collection; you need only the
file name without the directory path.

I would write your code as

Dim FName As String
Dim WB As Workbook
FName = Application.GetOpenFilename("Excel files (*.xls),*.xls")
Set WB = Workbooks.Open(FName)
WB.Activate


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"Kieran1028" wrote in
message ...

I guess I don't know the proper way to reference variables in
code...
can anyone troubleshoot this routine for me? GREATLY
appreciated... I
get errors almost every time I try to use a variable in code.
for
example,
Workbooks(fname).Activate gives me an error. If I eliminate
this code
by just activating a specific workbook instead of the one
defined by
fname, I get an error somewhere else where I tried to use a
variable,
like at
Set destCell = destWks.Cells(3, dcol) where VB says I didn't
define the
variable yet.

Sub get1degdata()
'
' get1degdata Macro
' Macro recorded 11/19/2004 by Kieran Coghlan
'
'
Dim ramp As Variant
Dim toes As Variant
Dim fname As Variant
Dim Wkbk As Workbook
Dim wksht As Worksheet
Dim destWks As Worksheet
Dim destCell As Range
Dim dcol As Integer
fname = Application.GetOpenFilename("Excel files(*.xls),*.xls")
Workbooks.Open (fname)
Workbooks(fname).Activate
' Sheets("charts").Select
Set Wkbk = Workbooks(fname)
ramp = InputBox("Enter the ramp duration in ms: 500, 1000,
2000, or
4000")
toes = InputBox("Enter the toes direction, UP or DOWN")
Sheets.Add.Name = ramp
Worksheets(ramp).Select
dcol = 1
For Each wksht In Wkbk.Worksheets
If wksht.Range("K5") = ramp And wksht.Range("G7") = toes Then
Set destWks = Wkbk.Worksheets(ramp)
wksht.Range("q12:q2011").Copy
End If
Set destCell = destWks.Cells(3, dcol)
destCell.PasteSpecial Paste:=xlPasteValues, Operation _
:=xlNone, SkipBlanks:=False, Transpose:=False
dcol = dcol + 1
Next
End Sub

Thanks,
Kieran


--
Kieran1028
------------------------------------------------------------------------
Kieran1028's Profile:
http://www.excelforum.com/member.php...o&userid=15678
View this thread:
http://www.excelforum.com/showthread...hreadid=319361

Chip Pearson Wrote:
In your code, fname contains the entire full file name. You can't us
this in the Workbooks collection; you need only the file name withou
the directory path.

I would write your code as

Dim FName As String
Dim WB As Workbook
FName = Application.GetOpenFilename("Excel files (*.xls),*.xls")
Set WB = Workbooks.Open(FName)
WB.Activate
--
Cordially,
Chip Pearson

Chip, thanks, that worked for the first problem. However, I still ge
an error at the following line:
Set destCell = destWks.Cells(3, dcol)
...and I can't figure out why. Anyone have any ideas? The specifi
error is Run-time error 91: "Object Variable or With block variable no
set".

Thanks again,
Kiera

--
Kieran102
-----------------------------------------------------------------------
Kieran1028's Profile: http://www.excelforum.com/member.php...fo&userid=1567
View this thread: http://www.excelforum.com/showthread.php?threadid=31936

That error indicates that you have not Set the variable to the
appropriate worksheet. Step through your code (F8) and you'll
find that you are not executing the line of code that sets the
destWks variable.



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Kieran1028" wrote in
message ...

Chip Pearson Wrote:
In your code, fname contains the entire full file name. You
can't use
this in the Workbooks collection; you need only the file name
without
the directory path.

I would write your code as

Dim FName As String
Dim WB As Workbook
FName = Application.GetOpenFilename("Excel files
(*.xls),*.xls")
Set WB = Workbooks.Open(FName)
WB.Activate
--
Cordially,
Chip Pearson

Chip, thanks, that worked for the first problem. However, I
still get
an error at the following line:
Set destCell = destWks.Cells(3, dcol)
..and I can't figure out why. Anyone have any ideas? The
specific
error is Run-time error 91: "Object Variable or With block
variable not
set".

Thanks again,
Kieran


--
Kieran1028
------------------------------------------------------------------------
Kieran1028's Profile:
http://www.excelforum.com/member.php...o&userid=15678
View this thread:
http://www.excelforum.com/showthread...hreadid=319361