I have created the code to copy some cells from another workbook. It works fine, but same code, if I copy and use it for another worksheet, it gives me error "subscript out of range"

Here is the code that works
Sub IllTaxReturn(

' IllTaxReturn Macr
' Macro recorded 05/28/2004 by KPAWA

Dim x As Varian
Dim y As Varian
Dim z As Varian

Workbooks("Mar-2004.xls").Sheets("Mar-04").Cells(86, 5).Copy Sheets("IllTaxReturn").Cells(14, 4

y = Workbooks("Mar-2004.xls").Sheets("Mar-04").Cells(86, 7).Valu
Sheets("IllTaxReturn").Cells(19, 4) =

Workbooks("Mar-04.xls").Sheets("Mar-04").Cells(23, 2).Copy Sheets("IllTaxReturn").Cells(23, 4

x = Workbooks("Mar-2004.xls").Sheets("Mar-04").Cells(34, 5).Valu
Sheets("IllTaxReturn").Cells(90, 4) =

z = Workbooks("Mar-2004.xls").Sheets("Mar-04").Cells(34, 7).Valu
Sheets("IllTaxReturn").Cells(95, 4) =

End Su


Here is the code that doesn't
Sub ALTaxReturn(

' Alabama TaxReturn Macr
' Macro recorded 05/28/2004 by KPAWA

Dim x As Varian
Dim y As Varian
'Dim z As Varian


Workbooks("Mar-2004.xls").Sheets("Mar-04").Cells(9, 5).Copy Sheets("ALTaxReturn").Cells(14, 4
'x = Workbooks("Mar-2004.xls").Sheets("Mar-04").Cells(9, 5).Valu
'Sheets("ALTaxReturn").Cells(14, 4) =
y = Workbooks("Mar-2004.xls").Sheets("Mar-04").Cells(9, 7).Valu
Sheets("ALTaxReturn").Cells(19, 4) =

Workbooks("Mar-04.xls").Sheets("Mar-04").Cells(10, 2).Copy Sheets("ALTaxReturn").Cells(23, 4

End Su

It gives me error on very first line, which is very similar to previous code

Thank

Is it not because you use the workbook name in the code

Workbooks("Mar-2004.xls")

try replacing with

Activeworkbook
--

HTH

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

"Kalpana" wrote in message
...
I have created the code to copy some cells from another workbook. It works
fine, but same code, if I copy and use it for another worksheet, it gives me
error "subscript out of range"

Here is the code that works:
Sub IllTaxReturn()
'
' IllTaxReturn Macro
' Macro recorded 05/28/2004 by KPAWAS
'
Dim x As Variant
Dim y As Variant
Dim z As Variant

Workbooks("Mar-2004.xls").Sheets("Mar-04").Cells(86, 5).Copy
Sheets("IllTaxReturn").Cells(14, 4)

y = Workbooks("Mar-2004.xls").Sheets("Mar-04").Cells(86, 7).Value
Sheets("IllTaxReturn").Cells(19, 4) = y

Workbooks("Mar-04.xls").Sheets("Mar-04").Cells(23, 2).Copy
Sheets("IllTaxReturn").Cells(23, 4)

x = Workbooks("Mar-2004.xls").Sheets("Mar-04").Cells(34, 5).Value
Sheets("IllTaxReturn").Cells(90, 4) = x

z = Workbooks("Mar-2004.xls").Sheets("Mar-04").Cells(34, 7).Value
Sheets("IllTaxReturn").Cells(95, 4) = z

End Sub



Here is the code that doesn't:
Sub ALTaxReturn()
'
' Alabama TaxReturn Macro
' Macro recorded 05/28/2004 by KPAWAS
'
Dim x As Variant
Dim y As Variant
'Dim z As Variant


Workbooks("Mar-2004.xls").Sheets("Mar-04").Cells(9, 5).Copy
Sheets("ALTaxReturn").Cells(14, 4)
'x = Workbooks("Mar-2004.xls").Sheets("Mar-04").Cells(9, 5).Value
'Sheets("ALTaxReturn").Cells(14, 4) = x
y = Workbooks("Mar-2004.xls").Sheets("Mar-04").Cells(9, 7).Value
Sheets("ALTaxReturn").Cells(19, 4) = y

Workbooks("Mar-04.xls").Sheets("Mar-04").Cells(10, 2).Copy
Sheets("ALTaxReturn").Cells(23, 4)

End Sub

It gives me error on very first line, which is very similar to previous
code.

Thanks

Mar-2004 is another worksheet, if I say Active workbook then wouldn't it refer to the the Active workbook which has the sheet called "ALTaxReturn"? IllTaxReturn and ALTaxReturn are in different work book than Mar-2004.xls

Thanks

-Kalpana

Workbooks("Mar-2004.xls") is a workbook not a worksheet. Sheets("Mar-04")
is a worksheet.

--

HTH

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

"Kalpana" wrote in message
...
Mar-2004 is another worksheet, if I say Active workbook then wouldn't it
refer to the the Active workbook which has the sheet called "ALTaxReturn"?
IllTaxReturn and ALTaxReturn are in different work book than Mar-2004.xls.

Thanks.

-Kalpana

Bob

Sorry for not being clear. Yes, I agree and I wrote the macro in the Workbook called TaxReturn.xls which has sheets ALTaxReturn and IllTaxReturn. Does it make sense
Thanks for your help.
-Kalpana

Kalpana,

That message means that it cannot find a named object, so either
- there is no workbook named "Mar-2004.xls"
- "Mar-2004.xls" doesn't have a sheet called "Mar-04"
- the activeworkbook doesn't have a sheet called "ALTaxReturn"

Have you double-checked all of these?

--

HTH

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

"Kalpana" wrote in message
...
Bob,

Sorry for not being clear. Yes, I agree and I wrote the macro in the
Workbook called TaxReturn.xls which has sheets ALTaxReturn and IllTaxReturn.
Does it make sense?
Thanks for your help..
-Kalpana