Active sheet instead of a "sheet1" for Macro Pantera <mynorveliz[ at ]yahoo.co.uk> 12/31/2008 5:57:20 PM I have this code in a macro: If Sheets("Sheet1").[A1] = True Then ActiveWindow.SmallScroll Down:=-9 Application.Goto Reference:="R17C3" Range("C11:C17").Select Range("C17").Activate Selection.Copy My questions is how do I make this macro work in any worksheet not only only in sheet1, what do i have to change from "Sheet1" to make it work. thank you, Pamela

Re: Active sheet instead of a "sheet1" for Macro Dave Peterson <petersod[ at ]verizonXSPAM.net> 12/31/2008 6:05:46 PM If Activesheet.range("a1").value = True Then Pantera wrote: [Quoted Text] > > I have this code in a macro: > If Sheets("Sheet1").[A1] = True Then > ActiveWindow.SmallScroll Down:=-9 > Application.Goto Reference:="R17C3" > Range("C11:C17").Select > Range("C17").Activate > Selection.Copy > > My questions is how do I make this macro work in any worksheet not > only only in sheet1, what do i have to change from "Sheet1" to make it > work. > thank you, > > Pamela -- Dave Peterson

Re: Active sheet instead of a "sheet1" for Macro "Don Guillett" <dguillett1[ at ]austin.rr.com> 12/31/2008 6:08:11 PM Sub copyif1() With ActiveSheet If .Range("A1") = True Then .Range("C11:C17").Copy .Range("d5") End If End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1[ at ]austin.rr.com "Pantera" <mynorveliz[ at ]yahoo.co.uk> wrote in message news:f46471d9-c485-400b-b83c-4988da37b48c[ at ]w1g2000prm.googlegroups.com... [Quoted Text] >I have this code in a macro: > If Sheets("Sheet1").[A1] = True Then > ActiveWindow.SmallScroll Down:=-9 > Application.Goto Reference:="R17C3" > Range("C11:C17").Select > Range("C17").Activate > Selection.Copy > > My questions is how do I make this macro work in any worksheet not > only only in sheet1, what do i have to change from "Sheet1" to make it > work. > thank you, > > Pamela

Re: Active sheet instead of a "sheet1" for Macro JE McGimpsey <jemcgimpsey[ at ]mvps.org> 12/31/2008 6:10:26 PM One way: If ActiveSheet.Range("A1").Value = True Then ActiveSheet.Range("C11:C7").Copy End If Or, equivalently: With ActiveSheet If .Range("A1").Value = True Then .Range("C11:C17").Copy End With In article <f46471d9-c485-400b-b83c-4988da37b48c[ at ]w1g2000prm.googlegroups.com>, Pantera <mynorveliz[ at ]yahoo.co.uk> wrote: [Quoted Text] > I have this code in a macro: > If Sheets("Sheet1").[A1] = True Then > ActiveWindow.SmallScroll Down:=-9 > Application.Goto Reference:="R17C3" > Range("C11:C17").Select > Range("C17").Activate > Selection.Copy > > My questions is how do I make this macro work in any worksheet not > only only in sheet1, what do i have to change from "Sheet1" to make it > work. > thank you, > > Pamela

Re: Active sheet instead of a "sheet1" for Macro Pantera <mynorveliz[ at ]yahoo.co.uk> 12/31/2008 6:32:29 PM On Dec 31, 10:10 am, JE McGimpsey <jemcgimp...[ at ]mvps.org> wrote: [Quoted Text] > One way: > >    If ActiveSheet.Range("A1").Value = True Then >          ActiveSheet.Range("C11:C7").Copy >    End If > > Or, equivalently: > >    With ActiveSheet >       If .Range("A1").Value = True Then .Range("C11:C17").Copy >    End With > > In article > <f46471d9-c485-400b-b83c-4988da37b...[ at ]w1g2000prm.googlegroups.com>, > > > >  Pantera <mynorve...[ at ]yahoo.co.uk> wrote: > > I have this code in a macro: > > If Sheets("Sheet1").[A1] = True Then > >     ActiveWindow.SmallScroll Down:=-9 > >     Application.Goto Reference:="R17C3" > >     Range("C11:C17").Select > >     Range("C17").Activate > >     Selection.Copy > > > My questions is how do I make this macro work in any worksheet not > > only only in sheet1, what do i have to change from "Sheet1" to make it > > work. > > thank you, > > > Pamela- Hide quoted text - > > - Show quoted text - Thank you!!! works like a charm. Pamela

RE: Active sheet instead of a "sheet1" for Macro Shane Devenshire 12/31/2008 6:39:01 PM Hi, A few general point alluded to in the earlier responses: 1. You don't need to select a range to work with it. 2. The recorder is verbose, although it is a great starting point you can often eliminate much of what it generates. So you macro becomes If [A1] = True Then [C11:C17].Copy [X1] .... more code End if End With This macro assume that you want to copy the data to the range starting in cell X1. Also, it assumes that you are in the same sheet as the cell A1. In which case there is no need to reference any sheet since this code assumes the active sheet. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Pantera" wrote: [Quoted Text] > I have this code in a macro: > If Sheets("Sheet1").[A1] = True Then > ActiveWindow.SmallScroll Down:=-9 > Application.Goto Reference:="R17C3" > Range("C11:C17").Select > Range("C17").Activate > Selection.Copy > > My questions is how do I make this macro work in any worksheet not > only only in sheet1, what do i have to change from "Sheet1" to make it > work. > thank you, > > Pamela >

Re: Active sheet instead of a "sheet1" for Macro Pantera <mynorveliz[ at ]yahoo.co.uk> 12/31/2008 6:40:47 PM On Dec 31, 10:05 am, Dave Peterson <peter...[ at ]verizonXSPAM.net> wrote: [Quoted Text] > If Activesheet.range("a1").value = True Then > > > > > > Pantera wrote: > > > I have this code in a macro: > > If Sheets("Sheet1").[A1] = True Then > >     ActiveWindow.SmallScroll Down:=-9 > >     Application.Goto Reference:="R17C3" > >     Range("C11:C17").Select > >     Range("C17").Activate > >     Selection.Copy > > > My questions is how do I make this macro work in any worksheet not > > only only in sheet1, what do i have to change from "Sheet1" to make it > > work. > > thank you, > > > Pamela > > -- > > Dave Peterson- Hide quoted text - > > - Show quoted text - Thanks!!!

Re: Active sheet instead of a "sheet1" for Macro Jarek Kujawa <blinok[ at ]gazeta.pl> 12/31/2008 7:47:40 PM or briefly: If ActiveSheet.Range("A1").Value Then On 31 Gru, 18:57, Pantera <mynorve...[ at ]yahoo.co.uk> wrote: [Quoted Text] > I have this code in a macro: > If Sheets("Sheet1").[A1] = True Then >     ActiveWindow.SmallScroll Down:=-9 >     Application.Goto Reference:="R17C3" >     Range("C11:C17").Select >     Range("C17").Activate >     Selection.Copy > > My questions is how do I make this macro work in any worksheet not > only only in sheet1, what do i have to change from "Sheet1" to make it > work. > thank you, > > Pamela