Change macro to work on a different sheet

There are a few ways to run a macro in another sheet from whichever sheet.

Please Login or Register  to view this content.

will put Hallo in cell A1 in the active sheet, in other words the sheet that is visible. Can be any sheet

Please Login or Register  to view this content.

Will put Hallo in cell A1 in Sheet2 only. Macro can be run from any sheet.

Please Login or Register  to view this content.

Will put Hallo in cell A1 in Sheet2 only. Macro can be run from any sheet.

It is often required to use the reference multiple times in one line.
For instance

Please Login or Register  to view this content.

Following is an example from Smallman. Notice the use of ws multiple times.

Please Login or Register  to view this content.

The following is from
http://msdn.microsoft.com/en-us/library/wc500chb.aspx
By using With...End With, you can perform a series of statements on a specified object without
specifying the name of the object multiple times. Within a With statement block, you can specify a
member of the object starting with a period, as if the With statement object preceded it.
For example, to change multiple properties on a single object, place the property assignment
statements inside the With...End With block, referring to the object only once instead of once for each
property assignment.
If your code accesses the same object in multiple statements, you gain the following benefits by using
the With statement:
You don't need to evaluate a complex expression multiple times or assign the result to a temporary
variable to refer to its members multiple times.
You make your code more readable by eliminating repetitive qualifying expressions.

Hello,

I refactored most of your code so it can be applied to different sheet. In addition to Jolivanes suggestion you should read this article on passing variables and arguments.
http://www.homeandlearn.org/passing_..._to_a_sub.html

Replace all your code with the code I provided. I was not able to test this because I am missing a sheet.

Hope this gets you on the right track.

Please Login or Register  to view this content.

Wow! Thanks for all the help guys!