I have multiple sheets in a workbook that are similar. They track new
projects as they are received.
They all have a command button to call a macro that is in each sheet.
Every day I make copies of this worksheet as new projects arrive and
they are all located in the same workbook. There probably is a better
place to have this code stored but it is in each sheet.

The problem I am having is that the range() values are not always
referencing the information on the current sheet where the command
button was clicked.
At times it looks at the previous sheets cell information and puts it
in the current sheets output. I have to exit excel and restart to get
the correct range information.

How can I make it reference the current called sheet only?

Also where would be a better place to store this code - In a module?
And if so I assume I need to tell the command button on click to
reference the module macro. But how do I tell it to look at the range
info from the sheet the button was clicked?


here is part of the code:


Private Sub CommandButton2_Click()

Dim R, C As Integer

Dim client_contact, client, project, project_number, boring, depth,
sample, lab_Id, test, date_rec As String

client = Range("C4")
project = Range("c5")
project_number = Range("p11")
date_rec = Range("ak12")

Worksheets("input").Range("b3..b6").Value = "" 'clear range
Worksheets("input").Range("b10..k13").Value = "" 'clear range

Worksheets("input").Range("b3").Value = client
Worksheets("input").Range("b4").Value = project
Worksheets("input").Range("b5").Value = project_number
Worksheets("input").Range("b6").Value = date_rec

Etc.......