PDA

View Full Version : Need help moving many fields of data from the master sheet to several other sheets



PKelly
02-01-2018, 11:44 AM
I have a workbook (attached) where the Current Transactions 2018 tab is the master sheet. I want 4 other alternate sheets (CA Client Boards, AZ Client Boards, WA Client Boards, & UT Client Boards) to automatically populate with the fields listed in the header row on the next available line when someone updates the master sheet.
Basically - 21513
IF the POD States: Folsom CA - data should transfer to the CA Client Boards
IF the POD States: Phoenix AZ - data should transfer to the AZ Client Boards
IF the POD States: Everett WA or Federal Way WA - data should transfer to the WA Client Boards
IF the POD States: Salt Lake City UT - data should transfer to the UT Client Boards

I've tried some formulas and reviewed Youtube and Forums for a VBA that would work but I just can't make anything work as I'm just not that familiar with VBA to understand where I'm going wrong. Currently I've hidden the columns that I don't need transfered in the secondary sheets thinking I'd need all the header row to match.

What I'm trying to accomplish is less data entry in several different places for our people who update the master sheet. We will then add additional columns to the secondary sheets with other data we want there.

Thanks,
Kelly

SamT
02-01-2018, 12:38 PM
Best bet is to use a VBA UserForm to enter data on the Master Sheet. The Form's code can then transfer the data around as needed according to listboxPOD selection.

I treat data as having three "Forms," or Processes
Data Entry
Data Storage
Data Viewing

Data Entry Form(s) rarely change, Data Storage never loses Fields, Data Review is situationally dynamic.

Data Storage must be comprehensive: Store all Data all the time
Data Entry has two modes: Initial record creation and record editing
Data Review is flexible: See various data various times, with data manipulation and summary.

All three Processes use different documents, workbooks, Forms etc. Some can use multiple venues, ie, Reports and summaries can be produced as PDF's, Doc's, Emails, Excel Sheets, CSV's, Web html pages, VBA UserForms, etc.

I usually start by designing the Data Storage facilities, then the Data Entry Form(s). Data Viewing design flows from Storage and depends on the Business requirements.

PKelly
02-01-2018, 12:51 PM
Thanks so much SamT. I've never heard of the VBA UserForm in relation to Excel. I have however, seen something that looks similar in Access. Is this similar then? I'll admit I would not know where to begin but it sounds interesting and potentially safer to protect the rest of the data on the master sheet. I don't like unprotected data where multiple people are entering data sometimes at the same time - which is a whole other set of issues I'm facing. Any thing you can direct me to in order to learn more about this suggestion I'm open to learning.

SamT
02-01-2018, 04:34 PM
As Far As I Know, Access and Excel use the same MSForms sub-application.

Start by opening the VBA Editor in Excel, then press Ctrl+R to insure the Project Explorer window is open. While you're at it, press F4 for the Properties window. I usually Dock them to my left, YMMV.

In BA, Projects are parenthetically named after the workbook they're in. Their actual Names can be edited in the Properties window.

Right click any object in the correct Project tree and select insert UserForm. You can also insert modules to contain supporting code. Class Modules are for advanced programming.

Without know anything about your code project except that you are interested in 4 geographical zones, I suggest you look at using a 4 Page MultiPage Control for the actual data entry, (Assumes each zone has different Data Points to deal with.)

Note that selecting any Control and pressing F1 should bring ip Help on that Control. Pressing F2, will bring up the Object Browser, which has a search function, can be helpful with a little experience using it.

If you select multiple controls, the Properties window will display their common Properties, and editing a property will effect all selected Controls.

I recommend that you add all Control/Label pairs, and add captions to all Labels before you add meaning names to the Controls. I base the Label Captions on the Data Fields in the Data Storage sheets, and then base the control names on their respective Label Captions.

Example:
Data Field--------------------------Label Caption-------------------Ctrl Name
Cli_FirstName------------------------First Name-----------------------lbxNameFirst
Cli_LastName------------------------Last Name-----------------------lbxNameLast
Cli_CompanyName-------------------Company-------------------------lbxCompany
Contact_Date-----------------------Date of Contact------------------tbxDateContact

Note some details. Field Names contain no Spaces. Control Names may or may not be prefixed by ControlType acronyms, Contain no extraneous characters (_), place the most important name parts first NameFirst, NameLast, NameMI). Sometimes it is advantageous to the coder to use identical names for Fields and Controls, but I have moved away from that practice since I moved away from using Named Ranges in my Code. I am of the opinion that Sheets and Code should not intersect. It's too easy for Users to change Sheet attributes such as Named Ranges.

To that End, I use the Properties window to change the Code Name of all sheets my Code has to interact with. For example, you mentioned some sheet Tab Names, "Current Transactions 2018," "CA Client Boards," et al. I would Code Name them, "Master," "Folsom," "Phoenix," etc. This has two or three advantages, In Code you can use Folsom.Range("A1"), vice Sheets("AZ Client Boards").Range("A1"); No User can break your code by renaming a sheet, and when you get to 2019, you don't have to edit every instance of "Sheets("Current Transactions 2018")" in your code, you just rename the Code Names of the old and the new sheets.

Note that many of your Data Reviews might use Formulas. These formulas can be broken when you replace 2018 sheets with 2019 sheets. I suggest that you not date Sheets, but rather date the Workbook. After archiving the old book, merely rename it and Clear the Contents of the applicable sheets. This will not affect any code or formulas. You can also delete all code and Forms from the archived version to reduce its file size. Personally, my Business Rule in that circumstance would be to keep last years data in the book and only Clear 2yo Data.

PKelly
02-02-2018, 09:22 AM
Wow SamT Thank you again. I will give it a shot and see if I can make it happen.