Programming Macro - Sheet Specific

M

McMurray

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
 
F

FSt1

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
 
J

Jim Cone

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
 
M

McMurray

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.
 
M

McMurray

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.

:)
 
C

Cam Pearce

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
 
T

Tim Williams

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


Tim
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top