Hi,

I have a project that is setup like this:
We have several machines each of which has a worksheet in this workbook. The
worksheets have several ranges of data that cover the previous year. Every
month, we need to move the previous 11 months data up one row and clear the
12th row for the new month. I am trying to automate that so each page has a
button that will do it.

The code is essentially the same for each worksheet. The only difference is
different types of machines have different ranges (e.g. Type I has
A10:K21,V10:AA21,€¦ while Type II has A10:N21,X10:AF21,€¦). I created a module
which has the code and a select case routine to set range variables based on
the type of machine. I want to have all the machine sheets have a simple
button that passes the machine type as an argument to the module sub.

My question is: Do I need to pass a reference for the individual sheets to
the module? If I just run the module code, will it assume the sheet on which
the button was pressed was the active sheet or do I need to explicitly state
which sheet I will be setting ranges on and pasting?

Thanks in advance for any help€¦

Unless explicitly referenced code in a module references the active sheet.
Code within a sheet references the sheet it is in unless otherwise specified.
So the long and the short of it is since your code is in a module it will
execute on the active sheet. So the code will be executed on the sheet where
the button was presses (assuming your button came from the forms toolbar and
is linked to code withing a the module).
--
HTH...

Jim Thomlinson


"Rayo K" wrote:

Hi,

I have a project that is setup like this:
We have several machines each of which has a worksheet in this workbook. The
worksheets have several ranges of data that cover the previous year. Every
month, we need to move the previous 11 months data up one row and clear the
12th row for the new month. I am trying to automate that so each page has a
button that will do it.

The code is essentially the same for each worksheet. The only difference is
different types of machines have different ranges (e.g. Type I has
A10:K21,V10:AA21,€¦ while Type II has A10:N21,X10:AF21,€¦). I created a module
which has the code and a select case routine to set range variables based on
the type of machine. I want to have all the machine sheets have a simple
button that passes the machine type as an argument to the module sub.

My question is: Do I need to pass a reference for the individual sheets to
the module? If I just run the module code, will it assume the sheet on which
the button was pressed was the active sheet or do I need to explicitly state
which sheet I will be setting ranges on and pasting?

Thanks in advance for any help€¦

Always best to be explicit with the sheet, and not assume anything about the
activesheet.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Rayo K" wrote in message
...
Hi,

I have a project that is setup like this:
We have several machines each of which has a worksheet in this workbook.
The
worksheets have several ranges of data that cover the previous year. Every
month, we need to move the previous 11 months data up one row and clear
the
12th row for the new month. I am trying to automate that so each page has
a
button that will do it.

The code is essentially the same for each worksheet. The only difference
is
different types of machines have different ranges (e.g. Type I has
A10:K21,V10:AA21,. while Type II has A10:N21,X10:AF21,.). I created a
module
which has the code and a select case routine to set range variables based
on
the type of machine. I want to have all the machine sheets have a simple
button that passes the machine type as an argument to the module sub.

My question is: Do I need to pass a reference for the individual sheets
to
the module? If I just run the module code, will it assume the sheet on
which
the button was pressed was the active sheet or do I need to explicitly
state
which sheet I will be setting ranges on and pasting?

Thanks in advance for any help.

Ok,

What is the best way to do that? Passing the sheet by reference or passing
a variable with the sheet name. Should I use With statements for the sheet or
set the activesheet in the module and just do range operations on the
activesheet (which is now set explicitly).

I'm thinking about code "etiquette" but mainly I am concerned with possible
unhandled exceptions as the end user for this application will not know what
to do (not proficient with vba or even Excel itself).

Thanks!

"Bob Phillips" wrote:

Always best to be explicit with the sheet, and not assume anything about the
activesheet.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Rayo K" wrote in message
...
Hi,

I have a project that is setup like this:
We have several machines each of which has a worksheet in this workbook.
The
worksheets have several ranges of data that cover the previous year. Every
month, we need to move the previous 11 months data up one row and clear
the
12th row for the new month. I am trying to automate that so each page has
a
button that will do it.

The code is essentially the same for each worksheet. The only difference
is
different types of machines have different ranges (e.g. Type I has
A10:K21,V10:AA21,. while Type II has A10:N21,X10:AF21,.). I created a
module
which has the code and a select case routine to set range variables based
on
the type of machine. I want to have all the machine sheets have a simple
button that passes the machine type as an argument to the module sub.

My question is: Do I need to pass a reference for the individual sheets
to
the module? If I just run the module code, will it assume the sheet on
which
the button was pressed was the active sheet or do I need to explicitly
state
which sheet I will be setting ranges on and pasting?

Thanks in advance for any help.

I would pass the sheet reference, that way you have access to all of its
properties, otherwise you will have to create a sheet object using that
sheet name.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Rayo K" wrote in message
...
Ok,

What is the best way to do that? Passing the sheet by reference or
passing
a variable with the sheet name. Should I use With statements for the sheet
or
set the activesheet in the module and just do range operations on the
activesheet (which is now set explicitly).

I'm thinking about code "etiquette" but mainly I am concerned with
possible
unhandled exceptions as the end user for this application will not know
what
to do (not proficient with vba or even Excel itself).

Thanks!

"Bob Phillips" wrote:

Always best to be explicit with the sheet, and not assume anything about
the
activesheet.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Rayo K" wrote in message
...
Hi,

I have a project that is setup like this:
We have several machines each of which has a worksheet in this
workbook.
The
worksheets have several ranges of data that cover the previous year.
Every
month, we need to move the previous 11 months data up one row and
clear
the
12th row for the new month. I am trying to automate that so each page
has
a
button that will do it.

The code is essentially the same for each worksheet. The only
difference
is
different types of machines have different ranges (e.g. Type I has
A10:K21,V10:AA21,. while Type II has A10:N21,X10:AF21,.). I created a
module
which has the code and a select case routine to set range variables
based
on
the type of machine. I want to have all the machine sheets have a
simple
button that passes the machine type as an argument to the module sub.

My question is: Do I need to pass a reference for the individual
sheets
to
the module? If I just run the module code, will it assume the sheet on
which
the button was pressed was the active sheet or do I need to explicitly
state
which sheet I will be setting ranges on and pasting?

Thanks in advance for any help.

hmmm....

Having trouble with this. My button code says this

Private Sub NewMonthButton_Click()

NewMonth (1, Sheet3)

End Sub



and my module says this...

Public Sub NewMonth(MachineType As Integer, MachineSheet As Worksheet)
........
.......

It gives an error of "Object doesn't support this propert or method."

I haven't even used a property or method yet. I was checking to see if the
reference would pass. What am I doing wrong?


"Bob Phillips" wrote:

I would pass the sheet reference, that way you have access to all of its
properties, otherwise you will have to create a sheet object using that
sheet name.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Rayo K" wrote in message
...
Ok,

What is the best way to do that? Passing the sheet by reference or
passing
a variable with the sheet name. Should I use With statements for the sheet
or
set the activesheet in the module and just do range operations on the
activesheet (which is now set explicitly).

I'm thinking about code "etiquette" but mainly I am concerned with
possible
unhandled exceptions as the end user for this application will not know
what
to do (not proficient with vba or even Excel itself).

Thanks!

"Bob Phillips" wrote:

Always best to be explicit with the sheet, and not assume anything about
the
activesheet.

--

Use

Private Sub NewMonthButton_Click()

NewMonth (1, Worksheets("Sheet3"))

End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Rayo K" wrote in message
...
hmmm....

Having trouble with this. My button code says this

Private Sub NewMonthButton_Click()

NewMonth (1, Sheet3)

End Sub



and my module says this...

Public Sub NewMonth(MachineType As Integer, MachineSheet As Worksheet)
.......
......

It gives an error of "Object doesn't support this propert or method."

I haven't even used a property or method yet. I was checking to see if the
reference would pass. What am I doing wrong?


"Bob Phillips" wrote:

I would pass the sheet reference, that way you have access to all of its
properties, otherwise you will have to create a sheet object using that
sheet name.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Rayo K" wrote in message
...
Ok,

What is the best way to do that? Passing the sheet by reference or
passing
a variable with the sheet name. Should I use With statements for the
sheet
or
set the activesheet in the module and just do range operations on the
activesheet (which is now set explicitly).

I'm thinking about code "etiquette" but mainly I am concerned with
possible
unhandled exceptions as the end user for this application will not
know
what
to do (not proficient with vba or even Excel itself).

Thanks!

"Bob Phillips" wrote:

Always best to be explicit with the sheet, and not assume anything
about
the
activesheet.

--

Consider having the buttons on each worksheet set a static, public, variable
in the module containing the larger, common, routine to the worksheet name.
The button-triggered routine would then call the common routine. The common
routine, using this variable, would know what worksheet to select for
copy/pasting/ etc.
--
Gary's Student


"Rayo K" wrote:

Hi,

I have a project that is setup like this:
We have several machines each of which has a worksheet in this workbook. The
worksheets have several ranges of data that cover the previous year. Every
month, we need to move the previous 11 months data up one row and clear the
12th row for the new month. I am trying to automate that so each page has a
button that will do it.

The code is essentially the same for each worksheet. The only difference is
different types of machines have different ranges (e.g. Type I has
A10:K21,V10:AA21,€¦ while Type II has A10:N21,X10:AF21,€¦). I created a module
which has the code and a select case routine to set range variables based on
the type of machine. I want to have all the machine sheets have a simple
button that passes the machine type as an argument to the module sub.

My question is: Do I need to pass a reference for the individual sheets to
the module? If I just run the module code, will it assume the sheet on which
the button was pressed was the active sheet or do I need to explicitly state
which sheet I will be setting ranges on and pasting?

Thanks in advance for any help€¦