Working on unselected sheets

Whitestar · Feb 28th, 2002, 04:49 AM

How do i get a macro to run on a certain sheet without it being selected.

Cheers

Derek · Feb 28th, 2002, 06:05 AM

Hi
Not clear exactly what you are trying to do.
I think you need to post an example
Regards
Derek

Whitestar · Mar 1st, 2002, 03:27 AM

Well what im doing is an A level project using excel. Its going to be used by a systems admin. In this project iv'e got a stock list that is updated every 30 minutes or so by a macro, but to do this the sheet that the macro is working on has to be selected and i thought that it would be anoying if the sheets changed in the middle of the admin doing something so i was wondering if there was a way for the macro to work on the sheet without the sheet being selected.

Ivan F Moala · Mar 1st, 2002, 03:33 AM

Quote:

On 2002-03-01 02:27, Whitestar wrote:
Well what im doing is an A level project using excel. Its going to be used by a systems admin. In this project iv'e got a stock list that is updated every 30 minutes or so by a macro, but to do this the sheet that the macro is working on has to be selected and i thought that it would be anoying if the sheets changed in the middle of the admin doing something so i was wondering if there was a way for the macro to work on the sheet without the sheet being selected.

Use the With Statement..the With statement allows you to perform a series of statements on a specified object without requalifying the name of the object.eg

With ThisWorkbook.Sheets("Sheet3")
.Range("A1:A20") = 20
.Range("C2") = 25
end with

HTH

Ivan

Whitestar · Mar 1st, 2002, 07:39 AM

Thnx for the code, but the sheet still changes when the macro is run.

Ivan F Moala · Mar 2nd, 2002, 08:49 AM

Quote:

On 2002-03-01 06:39, Whitestar wrote:
Thnx for the code, but the sheet still changes when the macro is run.

how have you used the code?

Juan Pablo González · Mar 2nd, 2002, 09:07 AM

If the ActiveSheet is Sheet2, then this

ThisWorkbook.Sheets("Sheet3").Range("A1") = 10

should work WITHOUT changing the active sheet, that is, Sheet2 should be selected.

Whitestar · Mar 4th, 2002, 04:44 AM

Thats the code im using, but all i get is an error massage.

Ivan F Moala · Mar 6th, 2002, 08:19 PM

Quote:

On 2002-03-04 03:44, Whitestar wrote:
Thats the code im using, but all i get is an error massage.

What is the code & error msg you get ?

Ivan

Whitestar · Mar 7th, 2002, 05:26 AM

The whole code that i am using is

ThisWorkbook.Sheets("Stock List").Range("E2").Select
Selection.Copy
Range("B2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("C2:D2").Select
Selection.ClearContents
Range("E2").Select
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell = ("")
Selection.Copy
ActiveCell.Offset(0, -3).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveCell.Offset(0, 1).Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Select
Selection.ClearContents
ActiveCell.Offset(1, 1).Select
Loop

End Sub

and the error msg is

Run-time error 1004
Select method of range class failed.

Mar 2nd, 2002, 09:07 AM	#7
Juan Pablo González MrExcel MVP Join Date: Feb 2002 Location: Bogota, Colombia Posts: 11,927	If the ActiveSheet is Sheet2, then this ThisWorkbook.Sheets("Sheet3").Range("A1") = 10 should work WITHOUT changing the active sheet, that is, Sheet2 should be selected. __________________ Regards, Juan Pablo González http://www.juanpg.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 28th, 2002, 04:49 AM	#1
Whitestar New Member Join Date: Feb 2002 Location: England Posts: 15	How do i get a macro to run on a certain sheet without it being selected. Cheers

Feb 28th, 2002, 06:05 AM	#2
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	Hi Not clear exactly what you are trying to do. I think you need to post an example Regards Derek

Mar 1st, 2002, 03:27 AM	#3
Whitestar New Member Join Date: Feb 2002 Location: England Posts: 15	Well what im doing is an A level project using excel. Its going to be used by a systems admin. In this project iv'e got a stock list that is updated every 30 minutes or so by a macro, but to do this the sheet that the macro is working on has to be selected and i thought that it would be anoying if the sheets changed in the middle of the admin doing something so i was wondering if there was a way for the macro to work on the sheet without the sheet being selected.

Mar 1st, 2002, 07:39 AM	#5
Whitestar New Member Join Date: Feb 2002 Location: England Posts: 15	Thnx for the code, but the sheet still changes when the macro is run.

Mar 4th, 2002, 04:44 AM	#8
Whitestar New Member Join Date: Feb 2002 Location: England Posts: 15	Thats the code im using, but all i get is an error massage.

Mar 7th, 2002, 05:26 AM	#10
Whitestar New Member Join Date: Feb 2002 Location: England Posts: 15	The whole code that i am using is ThisWorkbook.Sheets("Stock List").Range("E2").Select Selection.Copy Range("B2").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("C2:D2").Select Selection.ClearContents Range("E2").Select ActiveCell.Offset(1, 0).Select Do Until ActiveCell = ("") Selection.Copy ActiveCell.Offset(0, -3).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveCell.Offset(0, 1).Select Selection.ClearContents ActiveCell.Offset(0, 1).Select Selection.ClearContents ActiveCell.Offset(1, 1).Select Loop End Sub and the error msg is Run-time error 1004 Select method of range class failed.