Looking for help

[VinceB]

I am trying to figure out how to get a specific row in an excel spreadsheet to automatically be entered into another worksheet (in the same workbook) by using a specific condition.

My example is this:

I have a sheet named Master list - which has all the information. The fields in column G (District) would be the value that would automatically insert that particular row in the corresponding worksheet:

If the Districts are Northern, Southern, Western, etc...The row that has that particular District name would be inserted into the WOrsheets named Northern, Southern, Western, etc.

The master list would be the list that would be changed, but would reflect in the other worksheets.

 

[Peter_SSs]

See if you can adapt my suggestion here, particularly posts #5 and #7.

If that is not sufficient, please try to give more detail about what you have and what you want.

 

[VinceB]

This is what I have:

My workbook has 9 sheets, a 'Master' and 8 district sheets 'Aroostook' , 'Central' , 'Cumberland' , 'Downeast' , 'Midcoast' , 'Penquis' , 'Western Maine' and 'York'. The Master sheet is the sheet that all the information gets plugged into, and according to Column G in Master (which is labeled District) each row then gets copied to the corresponding sheet. I have the last 2 columns in the master as y/n now, but would rather have them as checkboxes, but need to know how I could get those to copy directly to the corresponding District Sheets

 

[Peter_SSs]

1. Do the District sheets already exist?

2. If so, do they already contain data?

3. If so, should that data get replaced when this code is run or should the new data get appended to the existing data?

4. Why do you want CheckBoxes? Is it simply to save have to type Yes/No or choose Yes/No from a Data Validation drop-down? Tell us about what purpose they would be seving.

5. Would the CheckBoxes be Form Controls or ActiveX Controls?

6. BTW, did you try the code I suggested?

 

[VinceB]

Answers to your questions:
1. yes the sheets exist
2. yes the sheets already contain data
3. append current data
4. the check boxes are more of time saver - they are relly not needed for the form - would just be a handy addition
5.form controls (for y/n)
6. after realizing I really do not need the checkboxes, I did not try the code.

Thanks for all the help, and the sheets are working good at the moment, and as I stated, the check boxes were more for looks than functionality.

1. Do the District sheets already exist?

2. If so, do they already contain data?

3. If so, should that data get replaced when this code is run or should the new data get appended to the existing data?

4. Why do you want CheckBoxes? Is it simply to save have to type Yes/No or choose Yes/No from a Data Validation drop-down? Tell us about what purpose they would be seving.

5. Would the CheckBoxes be Form Controls or ActiveX Controls?

6. BTW, did you try the code I suggested?

 

[Peter_SSs]

As I understand it, at the moment you are using formulas on the 'District' sheets to pull information from the 'Master' sheet. Is that correct?

Suppose you did have the CheckBoxes you were considering. Would you want to be able to alter a CheckBox on one of the District sheets? If so, the corresponding Checkbox on the Master sheet may then not be the same. Is that a problem?

Or would you only be altering CheckBoxes on the Master and wanting that result to flow to the relevant CheckBox on the District sheet?

 

[VinceB]

Peter,

Yes I am pulling the information from the Master Sheet.

If at all possible I would like to have the checkboxes only be able to be altered on the master, and then have the result flow into the district sheets.

 

[Peter_SSs]

Then perhaps you could consider this. Instead of using checkboxes, I think you are currently using 'the last two columns' for a Y/N value. If so, and you are just trying to make that data entry simpler, you could try the following code.

I have assumed 'the last two columns' are I and J. Just adjust that part of the code to suit your columns.

To implement ..

1. Right click the 'Master' sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window.

4. Try double clicking in cells in the relevant columns.

If you have formulas to draw these columns to your district sheets, they should continue to do that.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_BeforeDoubleClick(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range, Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br>    <br>    <SPAN style="color:#00007F">Const</SPAN> YNCols <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "I:J" <SPAN style="color:#007F00">'<-- Change to suit your Y/N columns</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Range(YNCols)) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Cancel = <SPAN style="color:#00007F">True</SPAN><br>        Target.Value = IIf(Target.Value = "Y", "N", "Y")<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[VinceB]

when I double click, it just goes to normal entry - would it matter if there was content already in thise columns? They have y/n entered already that gets pushed into the district sheets.

 

[Peter_SSs]

when I double click, it just goes to normal entry - would it matter if there was content already in thise columns?

What do you mean it 'goes to normal entry'?

Are you sure you have the code in the 'Master' sheet module and not in a general module or 'ThisWorkbook' module?

Which columns are you using for the Y/N?

Did you amend the code to use these columns?

They have y/n entered already that gets pushed into the district sheets.

As I have mentioned several times, I thought you are just looking for a simpler way to enter the Y/N in those columns. So far you haven't contradicted (or confirmed) my thinking on that so I still don't know if I am on the right track with that. I can't see why else you would be considering CheckBoxes in the first place.