+ Reply to Thread
Results 1 to 18 of 18

Move Data in Cells based on another cell contents

  1. #1
    Registered User
    Join Date
    08-15-2019
    Location
    Oregon, USA
    MS-Off Ver
    Office 2016
    Posts
    9

    Post Move Data in Cells based on another cell contents

    I am withdrawing my request for help from this board. I cannot delete the post so I am changing the text. Amin. Please delete this thread as I am unable to do so. Thank you.

    MOD EDIT: No - we don't remove threads that have responses and you should not post-edit your posts in this way. This is a public forum and information should remain here for the benefit of all, not removed. Please don't do this again - we see it as thread vandalism.

    Original request:


    I have a reservations spreadsheet that I need to transfer data to to another worksheet if a cell is not empty, when an ActiveX button is clicked.

    I need the following to happen when I press the button:

    In Spreadsheet 1 If a cell in I3-I39 is not empty Transfer (append) Data in Cells D, E, F, and G in that row to Columns B, C, D, and E respectively in Spreadsheet 2 then place today's date in column A of spreadsheet 2. Then Delete the data in Spreadsheet 1 Column I ONLY.

    In Spreadsheet 1 If a cell in S3-S39 is not empty Transfer (append) Data in Cells N, O, P, and Q in that row to Columns B, C, D, and E respectively in Spreadsheet 2 then place today's date in column A of spreadsheet 2.Then Delete the data in Spreadsheet 1 Column S ONLY.

    Thank you in advance!
    Last edited by AliGW; 08-20-2019 at 10:47 AM.

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

    Re: Move Data in Cells based on another cell contents

    Try this (I had a typo in the original so if you are going off the emailed version it won't work...)

    Please Login or Register  to view this content.
    Last edited by Bernie Deitrick; 08-15-2019 at 03:29 PM.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Move Data in Cells based on another cell contents

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. As you are new here, I shall post the link for you this time.)

    https://www.excelguru.ca/forums/show...ll-in-that-row
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Registered User
    Join Date
    08-15-2019
    Location
    Oregon, USA
    MS-Off Ver
    Office 2016
    Posts
    9

    Re: Move Data in Cells based on another cell contents

    Thank you so much, I didnt get your email for some reason but I copied and pasted this under my ActiveX button VBA Code and it doesn't seem to do anything. The button, in properties, has the (name) TransferNoShows. Do I need to rename the sub to "Private Sub TransferNoShows_Click()?

  5. #5
    Registered User
    Join Date
    08-15-2019
    Location
    Oregon, USA
    MS-Off Ver
    Office 2016
    Posts
    9

    Re: Move Data in Cells based on another cell contents

    I have tried to post the link to my other post, however I get a message that says I am not allowed to post links until I have posted a few times. My other inquire is loacated at https : // www . excelguru. ca/forums/showthread.php?10168-Help!-Transfer-Data-from-a-few-cells-basee-on-value-of-another-cell-in-that-row

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

    Re: Move Data in Cells based on another cell contents

    You could simply call the macro from the click event of the ActiveX button, or insert the body of the code into that event wrapper. Fro testing, you can simply run the macro with the sheet of interest active.

  7. #7
    Registered User
    Join Date
    08-15-2019
    Location
    Oregon, USA
    MS-Off Ver
    Office 2016
    Posts
    9

    Re: Move Data in Cells based on another cell contents

    Bernie, I cannot seem to get that code to do anything, either being called from the button, or stepping through it using F8 in the VB editor.

  8. #8
    Registered User
    Join Date
    08-15-2019
    Location
    Oregon, USA
    MS-Off Ver
    Office 2016
    Posts
    9

    Re: Move Data in Cells based on another cell contents

    Because of the tight timeframe I have also posted at https : // www . mrexcel . com/forum/excel-questions/1107085-vba-move-data-different-worksheet-if-specific-cell-not-empty.html
    Last edited by TimPin; 08-15-2019 at 05:26 PM. Reason: Typo

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

    Re: Move Data in Cells based on another cell contents

    Can you attach a sample workbook?

  10. #10
    Registered User
    Join Date
    08-15-2019
    Location
    Oregon, USA
    MS-Off Ver
    Office 2016
    Posts
    9

    Re: Move Data in Cells based on another cell contents

    How do you do an attachment, it doesn't seem to want to let me.

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

    Re: Move Data in Cells based on another cell contents

    Scroll down and beneath the message editing window is a button "Go Advanced" When you click that, even further down will be a section titled "Attachments" and that has a link to Manage Attachments

  12. #12
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Move Data in Cells based on another cell contents

    Quote Originally Posted by TimPin View Post
    How do you do an attachment, it doesn't seem to want to let me.
    How to upload a Sample Workbook:

    Follow these steps to upload a sample Workbook. Please click the Image link for each step to get the visual clue.


    Step1:

    Type your question description or reply, instead of clicking on "Post Quick Reply", click on "Go Advanced".

    Refer to the image for Step1.



    Step2:

    After you click on Go Advanced in Step1, a new page will be opened.
    Scroll down on that page until you find "Additional Options" Section and in there you will find a link called "Manage Attachments", click it and a new tab will be opened in your browser.

    Refer to the image for Step2.



    Step3:
    When new tab opens in your browser after clicking on Manage Attachments...

    1) Click on Browse on top left of the upload window.
    2) Choose your sample workbook in the file picker dialog box which opens after clicking on Browse and click on Open.
    3) Once you pick the sample workbook, click on Upload and wait until file uploads and appears on the upload window.
    4) Click on Close this window to close the upload window and submit your reply and your sample workbook will be attached with your reply.

    Refer to the image for Step3.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  13. #13
    Registered User
    Join Date
    08-15-2019
    Location
    Oregon, USA
    MS-Off Ver
    Office 2016
    Posts
    9

    Re: Move Data in Cells based on another cell contents

    Attached is the file. I need it to be executed when the button to the right of the list is clicked.
    As you can see I will be copying that routine and using it to do the same thing based on another box being checked.
    Also I forgot that it needs to copy the data in the cells in column C for the first half of the routine,
    and the data in the cells in Column M for the second half.
    Thank you.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    08-15-2019
    Location
    Oregon, USA
    MS-Off Ver
    Office 2016
    Posts
    9

    Re: Move Data in Cells based on another cell contents

    Thank you!!

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

    Re: Move Data in Cells based on another cell contents

    OK - you had two basic problems.

    The code was not being called by the click event of the ActiveX button - I moved the code from the test macro into the click event for the two buttons.

    Further, you were confusing indexing and worksheet codenames. For example, your sheet "Late Reservations" has a codename (which is auto-generated but can be changed) of "Sheet15" but has an Index of 4, since it is the fourth sheet in your workbook (but not the fourth visible sheet...).

    In code, that sheet can be referred to as any of these:

    Worksheets(4)
    Worksheets("Late Reservations")
    Sheets(4)
    Sheets("Late Reservations")
    Sheet15

    The index in the Sheets collection and in the Worksheets collection are not always the same, since the Sheets collection includes macro sheets and graph sheets, but the Worksheets collection does not. Referring to the Index of the worksheet can lead to a lot of errors, since sheets can be moved, hidden (Changing the apparent order), or deleted. The safest thing to do is to use the Codename, since the name can be changed from the tab and most users are not sophisticated enough to change the codename. Further, you can give the sheet a meaningful codename, like shtLateRes to make your code more readable..
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    08-15-2019
    Location
    Oregon, USA
    MS-Off Ver
    Office 2016
    Posts
    9

    Re: Move Data in Cells based on another cell contents

    Bernie,

    Thank you so much! It works Awesome. I appreciate you sticking through this with me and my mistakes. It works perfectly!

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Move Data in Cells based on another cell contents

    TimPin - I reinstated the opening post. Please read my comment there and please don't do that again. Thanks.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

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

    Re: Move Data in Cells based on another cell contents

    Yay! I'm happy that it all worked out in the end...

+ 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. [SOLVED] How to move the contents of one cell based on the contents of another cell:
    By Chad Bateman in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-21-2015, 04:51 PM
  2. Move cell based on contents (VBA)
    By BaronDJB in forum Excel General
    Replies: 5
    Last Post: 12-21-2014, 11:24 AM
  3. Move contents of cells based on condition
    By JR001 in forum Excel General
    Replies: 7
    Last Post: 09-13-2013, 07:35 AM
  4. Macro to move row based on cell contents
    By alpha608 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-17-2013, 04:08 AM
  5. [SOLVED] Move contents of a cell based on contents of another cell
    By djfscouse in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-06-2012, 10:31 AM
  6. Move cell value from one column to another based on contents
    By uncleslinky in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-30-2011, 06:44 AM
  7. Move data between cells based on value of another cell
    By brian0612 in forum Excel General
    Replies: 3
    Last Post: 03-30-2009, 10:59 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