Why does my event leave my cell blank sometime 1

[DIVINEDAR0956]

I have the following event connected to a worksheet to copy text from another worksheet. When I execute the event it works the first time but the next time it leaves my cell blank and copies nothing.

The event is as follows:

Sub Copy_CurrentCell_toWBSNumber()
Dim wbscell As Range
Set wbscell = Range("B2&quot
wbscell = ActiveCell.Value
Sheets("Sheet1&quot.Range("N8&quot.Value = wbscell
End Sub

Do I need to reset this event before the sub end.

Please help?

 

[DaleWatson123321]

Hi Divined,

Here's the way I would write the routine...

Sub Copy_CurrentCell_toWBSNumber()
wbs = Range("wbscell&quot.Value
Range("WBSNumber&quot = wbs
End Sub

This requires that you assign "Range Names" to the two cells in question. I've assigned the name "wbscell" to cell "B2" which I expect is on your "other worksheet".

And I've assigned the name "WBSNumber" to cell "N8" on Sheet1.

Getting into the habit of assigning range names is EXTREMELY IMPORTANT - for a variety of reasons. But the main reason as it applies to VBA, is that you NO LONGER have to be concerned about continually having to adjust your VBA code EVERY time you insert or delete a column or row, or move data from place to place.

This is because "internally", Excel maintains the whereabouts (cell coordinates) of all range names.

The best way to assign a Range Name:
a) Highlight the cell or range-of-cells
b) Hold down the <Control> key and hit the <F3> key
c) Type the name
d) Hit <Enter>

Avoid using names that Excel can conflict with VBA commands or will cell coordinates. For example don't use a name like &quot;D6&quot;. A safe practice is to include the &quot;_&quot; character - e.g. &quot;_D6&quot; or &quot;D6_&quot;, or &quot;Sheet_1&quot; instead of &quot;Sheet1&quot;.

The above routine, by the way, can be activated from &quot;anywhere&quot; - i.e. your cursor (ActiveCell) does NOT have to be on the &quot;origin&quot; cell.

Hope this helps. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DIVINEDAR0956]

Dale thank you for getting back to me so soon. A question for you. You said the routine can be activated from anywhere. I cut and pasted in the worksheet module where I want the value to be copied to (B2). I guess I should have told you that I want to copy to a different worksheet then the one that holds the value. When I get to wbs = Range(&quot;wbscell&quot.Value I get a Runtime error '1004': Application-defined or object defined error. Should I define wbscell if it's located on another worksheet.

I appreciate you help but I also have another question for you.

There are 50 worksheets using a different wbs number (01.100.00, 02.100.00, 03.100.00, and so on for each sheet. If this value is true in any sheets I want it to list it in the HRSEnter worksheet. All of these wbs numbers are in the same cell in each sheet. In the HRSEnter worksheet there's a column that says WBS_Number_Item. I want it to continue down the column if the value is typed in that cell. The cell is N8 in each worksheet.

I also thought about making a separate excel workbook for the input of hours. Let me explain a little more. Each of these worksheets holds a form and the form is repeated 5 times on a worksheet and consist of a 60 months input for hours for each wbs number. When an engineer puts in the hours for months 1 through 60 it is automatically entered into the HRSEnter worksheet. The most common cell that will be the same value is the Job_Number cell (E8 1st form and all 50 sheets).

What I need is that when any information becomes true on any of these forms that the Job_Number is automatically entered into Column A of the HRSEnter worksheet and the WBS Number is automatically entered into Column B of the HRSEnter worksheet. If you don't understand where I'm coming from I wish I could e-mail you at least one of the forms and the HRSEnter form.

Let me know what you think. If you have any questions let me know.

Darlene

 

[DIVINEDAR0956]

Dale,

I got it to work thank you thank you thank you. Now the only thing I need to do is figure out for it to check each form in each worksheet and fall in line in the column. Sorry for the mix up but thank you thank you thank you.

Darlene

 

[DaleWatson123321]

Darlene,

I'm pleased that you got the routine to work.

As for your last question and your offer to email &quot;at least one of the forms and the HRSEnter form&quot;... I believe that seeing your application will &quot;enlighten&quot; me somewhat, and allow me to resolve your last question more precisely.

There's also a possibility that in seeing your application first-hand, it will enable me to make other suggestions regarding other &quot;data manipulation options&quot; that could be beneficial.

So if you can email me a file with enough data to give me a reasonable perspective of what you're dealing with, this would certainly eliminate guesswork at my end.

Your description has certainly been reasonable, but &quot;a picture is worth a thousand words&quot;.

I'll await your email, and then get back to you ASAP, likely sometime Wednesday.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca