Working on unselected sheets

Whitestar

New Member
Joined
Feb 24, 2002
Messages
15
How do i get a macro to run on a certain sheet without it being selected.

Cheers
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Hi
Not clear exactly what you are trying to do.
I think you need to post an example
Regards
Derek
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top