Active sheet instead of a "sheet1" for Macro

Pantera

unread,

Dec 31, 2008, 12:57:20 PM12/31/08

to

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

unread,

Dec 31, 2008, 1:05:46 PM12/31/08

to

If Activesheet.range("a1").value = True Then

--

Dave Peterson

Don Guillett

unread,

Dec 31, 2008, 1:08:11 PM12/31/08

to

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
dguil...@austin.rr.com
"Pantera" <mynor...@yahoo.co.uk> wrote in message
news:f46471d9-c485-400b...@w1g2000prm.googlegroups.com...

JE McGimpsey

unread,

Dec 31, 2008, 1:10:26 PM12/31/08

to

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...@w1g2000prm.googlegroups.com>,

Pantera

unread,

Dec 31, 2008, 1:32:29 PM12/31/08

to

On Dec 31, 10:10 am, JE McGimpsey <jemcgimp...@mvps.org> wrote:
> 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...@w1g2000prm.googlegroups.com>,

>
>
>
> Pantera <mynorve...@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

Pantera

unread,

Dec 31, 2008, 1:40:47 PM12/31/08

to

> Dave Peterson- Hide quoted text -

>
> - Show quoted text -

Thanks!!!

Shane Devenshire

unread,

Dec 31, 2008, 1:39:01 PM12/31/08

to

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

Jarek Kujawa

unread,

Dec 31, 2008, 2:47:40 PM12/31/08

to

or briefly:

If ActiveSheet.Range("A1").Value Then