+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 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 2003, 2010, 2016 (Windows)
    Posts
    4,299

    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
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    32,628

    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!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  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 2003, 2010, 2016 (Windows)
    Posts
    4,299

    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 2003, 2010, 2016 (Windows)
    Posts
    4,299

    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 2003, 2010, 2016 (Windows)
    Posts
    4,299

    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,263

    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 2003, 2010, 2016 (Windows)
    Posts
    4,299

    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

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

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

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