Need help with Macro

[Guest]

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

 

[Bob Phillips]

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)

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)

 

[Guest]

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 Phillips]

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)

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.

 

[Guest]

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

 

[Bob Phillips]

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)

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?