Can to make a macro specific to one sheet in a workbook? I am using Office
2003 and the simple macro is shown below. There are multiple sheets in the
workbook and if you accidently run this marco when you have one of the other
sheets in the active window it really messes things up. I want the macro to
be able to effect only one sheet. In the script is selects a range
Range("A2:P32").Select Can you add something like Range
("sheetname!a2:P32").Select so it will only go to the named sheet? If this
is possible I need the syntax for the command.

Sub Wins()
'
' Wins Macro
' Macro recorded 12/6/2007 by jh
'
' Keyboard Shortcut: Ctrl+w
'
Range("A2:P32").Select
ActiveWindow.SmallScroll Down:=-9
Selection.Sort Key1:=Range("N3"), Order1:=xlDescending,
Key2:=Range("A3") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1,
MatchCase:=False _
, Orientation:=xlTopToBottom
Range("A2").Select
End Sub


TIA

Mark

hi
yes you can. add this to just before the range select
sheets("yoursheetname").activate
Range("A2:P32").Select
this will take you to the desired sheet for the range select.

if you code it like this...
sheets("yoursheetname").range("A2:P32").select
you will get an error - script out of range - if your are not on the
activesheet.
you can only select from the active sheet

ActiveWindow.SmallScroll Down:=-9
get rid of this line. not needed.

Regards
FSt1
"McMurray" wrote:

Can to make a macro specific to one sheet in a workbook? I am using Office
2003 and the simple macro is shown below. There are multiple sheets in the
workbook and if you accidently run this marco when you have one of the other
sheets in the active window it really messes things up. I want the macro to
be able to effect only one sheet. In the script is selects a range
Range("A2:P32").Select Can you add something like Range
("sheetname!a2:P32").Select so it will only go to the named sheet? If this
is possible I need the syntax for the command.

Sub Wins()
'
' Wins Macro
' Macro recorded 12/6/2007 by jh
'
' Keyboard Shortcut: Ctrl+w
'
Range("A2:P32").Select
ActiveWindow.SmallScroll Down:=-9
Selection.Sort Key1:=Range("N3"), Order1:=xlDescending,
Key2:=Range("A3") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1,
MatchCase:=False _
, Orientation:=xlTopToBottom
Range("A2").Select
End Sub


TIA

Mark

Note the dot in from of every use of Range...
'---
Sub Wins()
' Wins Macro
' Macro recorded 12/6/2007 by jh
' Keyboard Shortcut: Ctrl+w
With Worksheets("Mine")
.Select
.Range("A2:P32").Sort Key1:=.Range("N3"), Order1:=xlDescending, _
Key2:=.Range("A3"), Order2:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
.Range("A2").Select
End With
ActiveWindow.SmallScroll Down:=-9
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"McMurray"
wrote in message
Can to make a macro specific to one sheet in a workbook? I am using Office
2003 and the simple macro is shown below. There are multiple sheets in the
workbook and if you accidently run this marco when you have one of the other
sheets in the active window it really messes things up. I want the macro to
be able to effect only one sheet. In the script is selects a range
Range("A2:P32").Select Can you add something like Range
("sheetname!a2:P32").Select so it will only go to the named sheet? If this
is possible I need the syntax for the command.

Sub Wins()
'
' Wins Macro
' Macro recorded 12/6/2007 by jh
'
' Keyboard Shortcut: Ctrl+w
'
Range("A2:P32").Select
ActiveWindow.SmallScroll Down:=-9
Selection.Sort Key1:=Range("N3"), Order1:=xlDescending,
Key2:=Range("A3") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1,
MatchCase:=False _
, Orientation:=xlTopToBottom
Range("A2").Select
End Sub


TIA

Mark

I did find the following script that seems to do part of what I want. It
points the macro to the appropriate sheet and range.

Sheets("Sheet1").Range("A2:P32").Select

I included this syntax in the macro. When I run it with Sheet1 in the
active window it works fine. When I run it with Sheet2 in the active window
I get an error. The debug program points to this same line as the source of
the error.

"McMurray" wrote:

Can to make a macro specific to one sheet in a workbook? I am using Office
2003 and the simple macro is shown below. There are multiple sheets in the
workbook and if you accidently run this marco when you have one of the other
sheets in the active window it really messes things up. I want the macro to
be able to effect only one sheet. In the script is selects a range
Range("A2:P32").Select Can you add something like Range
("sheetname!a2:P32").Select so it will only go to the named sheet? If this
is possible I need the syntax for the command.

Sub Wins()
'
' Wins Macro
' Macro recorded 12/6/2007 by jh
'
' Keyboard Shortcut: Ctrl+w
'
Range("A2:P32").Select
ActiveWindow.SmallScroll Down:=-9
Selection.Sort Key1:=Range("N3"), Order1:=xlDescending,
Key2:=Range("A3") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1,
MatchCase:=False _
, Orientation:=xlTopToBottom
Range("A2").Select
End Sub


TIA

Mark

Thanks for the tip. You answered my second post also. I did not see this
post before I wrote it. I will try it right now.

:)

"FSt1" wrote:

hi
yes you can. add this to just before the range select
sheets("yoursheetname").activate
Range("A2:P32").Select
this will take you to the desired sheet for the range select.

if you code it like this...
sheets("yoursheetname").range("A2:P32").select
you will get an error - script out of range - if your are not on the
activesheet.
you can only select from the active sheet

ActiveWindow.SmallScroll Down:=-9
get rid of this line. not needed.

Regards
FSt1
"McMurray" wrote:

Can to make a macro specific to one sheet in a workbook? I am using Office
2003 and the simple macro is shown below. There are multiple sheets in the
workbook and if you accidently run this marco when you have one of the other
sheets in the active window it really messes things up. I want the macro to
be able to effect only one sheet. In the script is selects a range
Range("A2:P32").Select Can you add something like Range
("sheetname!a2:P32").Select so it will only go to the named sheet? If this
is possible I need the syntax for the command.

Sub Wins()
'
' Wins Macro
' Macro recorded 12/6/2007 by jh
'
' Keyboard Shortcut: Ctrl+w
'
Range("A2:P32").Select
ActiveWindow.SmallScroll Down:=-9
Selection.Sort Key1:=Range("N3"), Order1:=xlDescending,
Key2:=Range("A3") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1,
MatchCase:=False _
, Orientation:=xlTopToBottom
Range("A2").Select
End Sub


TIA

Mark

I have a macro that does some minor processing to sheet3 of my workbook. I want to run the macro from a button on sheet1 of my workbook. When I do run the macro (from sheet1) the processing occurs in cells in sheet1, not in sheet 3. I tried the worksheet("sheet3").active code but it jumped to sheet3 when run (which I do not want). I want to run the macro from sheet1 and stay on sheet1, even thought sheet3 will have some changes made by the macro. Is this possible?

Sub Start()
'
' Play Macro
' Macro recorded 11/07/2008 by Cameron
'
' Keyboard Shortcut: Ctrl+a
'

Range("K3").Value = 0
Range("F3").Select
Selection.Copy
Range("G3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("K3").Value = Range("K3").Value + 1
Calculate


End Sub

Add this at the top of the module under Sub Start()
Sheets("Sheet3").Select


"Cam Pearce" wrote in message ...
I have a macro that does some minor processing to sheet3 of my workbook. I
want to run the macro from a button on sheet1 of my workbook. When I do run
the macro (from sheet1) the processing occurs in cells in sheet1, not in
sheet 3. I tried the worksheet("sheet3").active code but it jumped to
sheet3 when run (which I do not want). I want to run the macro from sheet1
and stay on sheet1, even thought sheet3 will have some changes made by the
macro. Is this possible?

Sub Start()
'
' Play Macro
' Macro recorded 11/07/2008 by Cameron
'
' Keyboard Shortcut: Ctrl+a
'

Range("K3").Value = 0
Range("F3").Select
Selection.Copy
Range("G3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("K3").Value = Range("K3").Value + 1
Calculate


End Sub

with thisworkbook.sheets("Sheet 3")
.range("K3").value = 1
.range("G3").value = .range("F3").value
.calculate
end with


Tim


<Cam Pearce wrote in message ...
I have a macro that does some minor processing to sheet3 of my workbook. I
want to run the macro from a button on sheet1 of my workbook. When I do run
the macro (from sheet1) the processing occurs in cells in sheet1, not in
sheet 3. I tried the worksheet("sheet3").active code but it jumped to
sheet3 when run (which I do not want). I want to run the macro from sheet1
and stay on sheet1, even thought sheet3 will have some changes made by the
macro. Is this possible?

Sub Start()
'
' Play Macro
' Macro recorded 11/07/2008 by Cameron
'
' Keyboard Shortcut: Ctrl+a
'

Range("K3").Value = 0
Range("F3").Select
Selection.Copy
Range("G3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("K3").Value = Range("K3").Value + 1
Calculate


End Sub

Just wanted to let ya know that this was extremely helpful. I am teaching myself, and I was able to integrate this into my code... and get it to work flawlessly!

Just in case, here is my code with the sheet specific info in it. Thanks again for responding to this reply and answering in such a way that anyone can easily customize your response to make it work in their code, even a newbie like me!!!


Sub H_I_J()
'
' H_I_J Macro

Sheets("Adjustments to Payouts - Agents").Activate
last = Range("G65536").End(xlUp).Row
For i = 3 To last
If Cells(i, 7).Value < "" Then
Cells(i, 8).FormulaR1C1 = "=VLOOKUP(RC[-3],dump,4,FALSE)"
Cells(i, 9).FormulaR1C1 = "=VLOOKUP(RC[-4],dump,5,FALSE)"
Cells(i, 10).FormulaR1C1 = _
"=IF(VLOOKUP(RC[-5],dump,6,FALSE)="""","""",VLOOKUP(RC[-5],dump,6,FALSE))"
End If
Next
End Sub



FSt wrote:

hiyes you can. add this to just before the range selectsheets("yoursheetname").
06-Dec-07

hi
yes you can. add this to just before the range select
sheets("yoursheetname").activate
Range("A2:P32").Select
this will take you to the desired sheet for the range select.

if you code it like this...
sheets("yoursheetname").range("A2:P32").select
you will get an error - script out of range - if your are not on the
activesheet.
you can only select from the active sheet

ActiveWindow.SmallScroll Down:=-9
get rid of this line. not needed.

Regards
FSt1
"McMurray" wrote:

Previous Posts In This Thread:

On Thursday, December 06, 2007 10:01 PM
McMurra wrote:

Programming Macro - Sheet Specific
Can to make a macro specific to one sheet in a workbook? I am using Office
2003 and the simple macro is shown below. There are multiple sheets in the
workbook and if you accidently run this marco when you have one of the other
sheets in the active window it really messes things up. I want the macro to
be able to effect only one sheet. In the script is selects a range
Range("A2:P32").Select Can you add something like Range
("sheetname!a2:P32").Select so it will only go to the named sheet? If this
is possible I need the syntax for the command.

Sub Wins()
'
' Wins Macro
' Macro recorded 12/6/2007 by jh
'
' Keyboard Shortcut: Ctrl+w
'
Range("A2:P32").Select
ActiveWindow.SmallScroll Down:=-9
Selection.Sort Key1:=Range("N3"), Order1:=xlDescending,
Key2:=Range("A3") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1,
MatchCase:=False _
, Orientation:=xlTopToBottom
Range("A2").Select
End Sub


TIA

Mark

On Thursday, December 06, 2007 10:10 PM
FSt wrote:

hiyes you can. add this to just before the range selectsheets("yoursheetname").
hi
yes you can. add this to just before the range select
sheets("yoursheetname").activate
Range("A2:P32").Select
this will take you to the desired sheet for the range select.

if you code it like this...
sheets("yoursheetname").range("A2:P32").select
you will get an error - script out of range - if your are not on the
activesheet.
you can only select from the active sheet

ActiveWindow.SmallScroll Down:=-9
get rid of this line. not needed.

Regards
FSt1
"McMurray" wrote:

On Thursday, December 06, 2007 10:24 PM
Jim Cone wrote:

Note the dot in from of every use of Range...
Note the dot in from of every use of Range...
'---
Sub Wins()
' Wins Macro
' Macro recorded 12/6/2007 by jh
' Keyboard Shortcut: Ctrl+w
With Worksheets("Mine")
.Select
.Range("A2:P32").Sort Key1:=.Range("N3"), Order1:=xlDescending, _
Key2:=.Range("A3"), Order2:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
.Range("A2").Select
End With
ActiveWindow.SmallScroll Down:=-9
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"McMurray"
wrote in message
Can to make a macro specific to one sheet in a workbook? I am using Office
2003 and the simple macro is shown below. There are multiple sheets in the
workbook and if you accidently run this marco when you have one of the other
sheets in the active window it really messes things up. I want the macro to
be able to effect only one sheet. In the script is selects a range
Range("A2:P32").Select Can you add something like Range
("sheetname!a2:P32").Select so it will only go to the named sheet? If this
is possible I need the syntax for the command.

Sub Wins()
'
' Wins Macro
' Macro recorded 12/6/2007 by jh
'
' Keyboard Shortcut: Ctrl+w
'
Range("A2:P32").Select
ActiveWindow.SmallScroll Down:=-9
Selection.Sort Key1:=Range("N3"), Order1:=xlDescending,
Key2:=Range("A3") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1,
MatchCase:=False _
, Orientation:=xlTopToBottom
Range("A2").Select
End Sub


TIA

Mark

On Thursday, December 06, 2007 10:43 PM
McMurra wrote:

I did find the following script that seems to do part of what I want.
I did find the following script that seems to do part of what I want. It
points the macro to the appropriate sheet and range.

Sheets("Sheet1").Range("A2:P32").Select

I included this syntax in the macro. When I run it with Sheet1 in the
active window it works fine. When I run it with Sheet2 in the active window
I get an error. The debug program points to this same line as the source of
the error.

"McMurray" wrote:

On Thursday, December 06, 2007 10:48 PM
McMurra wrote:

Thanks for the tip. You answered my second post also.
Thanks for the tip. You answered my second post also. I did not see this
post before I wrote it. I will try it right now.


"FSt1" wrote:

On Saturday, July 12, 2008 1:39 AM
Cam Pearce wrote:

Programming Macro - Sheet Specific
I have a macro that does some minor processing to sheet3 of my workbook. I want to run the macro from a button on sheet1 of my workbook. When I do run the macro (from sheet1) the processing occurs in cells in sheet1, not in sheet 3. I tried the worksheet("sheet3").active code but it jumped to sheet3 when run (which I do not want). I want to run the macro from sheet1 and stay on sheet1, even thought sheet3 will have some changes made by the macro. Is this possible?

Sub Start()
'
' Play Macro
' Macro recorded 11/07/2008 by Cameron
'
' Keyboard Shortcut: Ctrl+a
'

Range("K3").Value = 0
Range("F3").Select
Selection.Copy
Range("G3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("K3").Value = Range("K3").Value + 1
Calculate


End Sub


Submitted via EggHeadCafe - Software Developer Portal of Choice
Excel Identifying which formulas are slowing down workbook recalaculation
http://www.eggheadcafe.com/tutorials...aculation.aspx