Need help with macro

[Kalpana]

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

Thanks
-Kalpan

 

[Tod]

Subscript Out Of Range means your code is looking for
something that doesn't seem to be there.

So for example, if this is the line of code that is giving
you the error:

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

It could be that either the workbook "Mar-2004.xls" is not
open, worksheet "Mar-04" is not in that workbook
or "ALTaxReturn" is not available, or a combination of
these.

tod

-----Original 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)