+ Reply to Thread
Results 1 to 12 of 12

"Save to database" macro: transfering data from one sheet to another based on Identifier

Hybrid View

  1. #1
    Registered User
    Join Date
    01-26-2016
    Location
    Zurich, Switzerland
    MS-Off Ver
    2004
    Posts
    8

    "Save to database" macro: transfering data from one sheet to another based on Identifier

    Dear VBA Experts

    After spending many hours trying to create a "Save to Database" macro, I have sadly given up. My VBA know-how is simply not enough.

    Can anyone perhaps help with the following task:

    I have a form which I use as tool. Once the form is filled out, I want to transfer all the info into a second sheet that I have defined as the database.

    The database is arranged with the variable names on the left (from top to bottom) on the A column. The form information should then be stored also vertically to match the variables. The first (highest) entry is the identifier of the entry itself and bellow is the rest of the form fields.

    To the right of that (from Column B and further), there will be entries with their corresponding variable values all the way down to the end of the variable list.

    The problems:

    1. the cells of the form are not continuous and are all over the place.

    2. I have to be able to create the entry if new. Otherwise I have to check the top row (entry identifier) to find the already existing
    entry and overwrite the data to that specific column.
    3. The data in the form has to be transposed because the database is vertical

    The conditions:

    1. There are a finite number of variables --> a maximum of 100 Team members
    2. There are unlimited number of entries in the data base
    3. If a field in the form is not filled-out then it stays blank in the database


    How do I do this?

    Here is an example:


    In Sheet 1 (Teams_Form) I would have some rows with fields to be filled-out:
    (the commas represent the next cell to the right)

    Team_north
    John, Engineer, 1978, male
    Tom, Doctor, 1965, male
    (empty)
    Janine, Teacher, 1999, female
    Chris, Electrician, 1985, male

    .... etc.



    This means in Sheet 2 (database) I would have a first column with all the variable names as follows:

    Identifier
    Member1
    Member2
    Member3
    Member4
    Member5
    Occupation1
    Occupation2
    Occupation3
    Occupation4
    Occupaton5
    Date1
    Date2
    Date3
    Date4
    Date5
    Gender1
    .... etc.


    and then next to that on the right there would be entries (one per column) like for example:


    Team_north
    John
    Tom
    (empty)
    Janine
    Chris
    Engineer
    Doctor
    (empty)

    Teacher
    Electrician
    1978
    1965
    (empty)
    1999
    1985

    .... etc.


    I would really appreciate some help!

    Greetings,
    Chris

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    6,333

    Re: "Save to database" macro: transfering data from one sheet to another based on Identifi

    I would start by defining named columns on the form sheet that match your variable names:

    Member
    Occupation
    Date

    And have a named cell "TeamName"

    The advantage of naming the ranges is that you can simply loop over the cells of column A to read/write the values of interest.

    Here is an example workbook that transfers data to and from a database sheet.

    Database transfer.xlsm
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    01-26-2016
    Location
    Zurich, Switzerland
    MS-Off Ver
    2004
    Posts
    8

    Re: "Save to database" macro: transfering data from one sheet to another based on Identifi

    Thanks a lot Bernie. That was fast and great!

    It works great. I forgot perhaps to mention, that my ranges are not continuous. This means I probably wont have the luxury to have a nice area like for example from B5 to E14. Since I will be recycling a relatively established form that I cannot change, I will have fore xample always 2 empty rows between team members and there is a sub-classification on the form for back-up team members. These two member-type sections are separated by 5 empty rows. Furthermore, there are empty columns between some of the titles on the form. For example between Name and occupation and after gender and the next title.

    Any suggestions to overcome this?

    Greetings,
    Chris
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    6,333

    Re: "Save to database" macro: transfering data from one sheet to another based on Identifi

    I think this will work - just a little logic to skip around the sheet given the structure you show.

    Database transfer_b.xlsm

  5. #5
    Registered User
    Join Date
    01-26-2016
    Location
    Zurich, Switzerland
    MS-Off Ver
    2004
    Posts
    8

    Re: "Save to database" macro: transfering data from one sheet to another based on Identifi

    That is indeed a straight-forward but elegant work around. Thank you.

    May I ask you one last question: I tried nesting another For loop inside the For Each loop for the case when a single member has several occupations but it didn't get it to work. Would you suggest treating that differently? It has to be nested, right?

    Thank you so much for your help.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    6,333

    Re: "Save to database" macro: transfering data from one sheet to another based on Identifi

    A little more than just nesting, since you changed how the values are labeled.

    Database transfer_c.xlsm

  7. #7
    Registered User
    Join Date
    01-26-2016
    Location
    Zurich, Switzerland
    MS-Off Ver
    2004
    Posts
    8

    Re: "Save to database" macro: transfering data from one sheet to another based on Identifi

    That is indeed a straight-forward but elegant work around. Thank you.

    May I ask you one last question: I tried nesting another For loop inside the For Each loop for the case when a single member has several occupations but it didn't get it to work. Would you suggest treating that differently? It has to be nested, right?

    Thank you so much for your help.

    Database transfer_c.xlsm

  8. #8
    Registered User
    Join Date
    01-26-2016
    Location
    Zurich, Switzerland
    MS-Off Ver
    2004
    Posts
    8

    Re: "Save to database" macro: transfering data from one sheet to another based on Identifi

    That is indeed a straight-forward but elegant work around. Thank you.

    May I ask you one last question: I tried nesting another For loop inside the For Each loop for the case when a single member has several occupations but it didn't get it to work. Would you suggest treating that differently? It has to be nested, right?

    Thank you so much for your help.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    01-26-2016
    Location
    Zurich, Switzerland
    MS-Off Ver
    2004
    Posts
    8

    Re: "Save to database" macro: transfering data from one sheet to another based on Identifi

    That is indeed a straight-forward but elegant work around. Thank you.

    May I ask you one last question: I tried nesting another For loop inside the For Each loop for the case when a single member has several occupations but it didn't get it to work. Would you suggest treating that differently? It has to be nested, right?

    Thank you so much for your help.

  10. #10
    Registered User
    Join Date
    01-26-2016
    Location
    Zurich, Switzerland
    MS-Off Ver
    2004
    Posts
    8

    Re: "Save to database" macro: transfering data from one sheet to another based on Identifi

    Sorry about the multiple posting. I kept getting an error and before I notice, it was posted many times. Can I delete posts?

  11. #11
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    6,333

    Re: "Save to database" macro: transfering data from one sheet to another based on Identifi

    Don't worry about the extra posts - I only replied to one

  12. #12
    Registered User
    Join Date
    01-26-2016
    Location
    Zurich, Switzerland
    MS-Off Ver
    2004
    Posts
    8

    Re: "Save to database" macro: transfering data from one sheet to another based on Identifi

    Cool.

    Once again 100000000 x thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy a range of data and paste to multiple sheets using "User" as key identifier
    By cmicmac in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-01-2015, 09:59 AM
  2. [SOLVED] Code that copies row of data to another sheet based on text "Complete"/"Delete"
    By Dremzy in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 04-20-2014, 05:51 PM
  3. Replies: 2
    Last Post: 04-20-2014, 11:18 AM
  4. Showing Names and data on sheet from a "database" sheet
    By FootyMan in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 08-26-2013, 08:38 AM
  5. [SOLVED] Need Macro to compile data from "Weekly" sheets and sum up into "Monthly" summary sheet
    By mo4391 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-21-2013, 07:25 PM
  6. [SOLVED] Code to save sheet as "PDF" to Desktop - Based on ComboBox 1 Value
    By matrixpom in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-21-2012, 04:37 AM
  7. Replies: 0
    Last Post: 07-17-2006, 09:45 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1