+ Reply to Thread
Results 1 to 20 of 20

VBA Code Or Function Help - Cut Information From One Cell And Paste Into Another

  1. #1
    Registered User
    Join Date
    09-23-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    68

    VBA Code Or Function Help - Cut Information From One Cell And Paste Into Another

    Hi,

    Please could someone help with the below -

    I have a workbook with multiple sheets.

    On sheet one I have a number of cells formatted as below -

    E3 - Name
    E5, G5, I5 - Number
    E7 - Type

    Basically I would like a button below this that once the above boxes have been completed with information it cuts the information from them and adds it onto a single row on another sheet.

    For Example -

    The above moves onto sheet two -

    E3 data is pasted in A1 on sheet two
    E5, G5, I5 data is pasted in B1, C1, D1 on sheet two
    E7 date is pasted into E1 on sheet two

    If information is then entered into sheet one again and the button is pressed it uses the next available row on sheet two, so enters into A2, B2, C2, D2, E2 etc.

    Is this possible?

    Thank You

  2. #2
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: VBA Code Or Function Help - Cut Information From One Cell And Paste Into Another

    Give this a try:

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    09-23-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    68

    Re: VBA Code Or Function Help - Cut Information From One Cell And Paste Into Another

    That works great thank you.

    What parts of the code do I need to amend if I want to perform the same trick to pull the data down to a row within the same worksheet?

    Say data is in the same row as above E3, E5, G5, I5, E7, as above but destination row would be row 24 but start at column K?

  4. #4
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: VBA Code Or Function Help - Cut Information From One Cell And Paste Into Another

    You can spot the 5 cut/destination lines of the code. You'll want to change the destination locations to Sheets(1).Cells(NextRow, "K"), then adjust the other locations to the columns of your choice. You'll also need to change the NextRow destination to check Sheets(1) and column K. It's hard to say exactly what NextRow needs to look like without seeing your workbook, but here's my best guess, give it a shot:

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    09-23-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    68

    Re: VBA Code Or Function Help - Cut Information From One Cell And Paste Into Another

    Thank you again, I got it to work perfectly.

    The only thing, it does is take the entire cell formatting and all away, is there an amendment to make to just cut the text that's typed into it?

  6. #6
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: VBA Code Or Function Help - Cut Information From One Cell And Paste Into Another

    Try this tweak:

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    09-23-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    68

    Re: VBA Code Or Function Help - Cut Information From One Cell And Paste Into Another

    Thank you, I've managed to make that fit, my formula currently looks like this (I've got rid of some of the cells as turns out I didn't need as much information as I thought.)

    Sub MoveDATA()
    Dim NextRow As Long
    Application.ScreenUpdating = False

    NextRow = Sheets(1).Cells(Rows.Count, "B").End(xlUp).Row + 1
    If NextRow < 24 Then
    NextRow = 24
    End If
    Sheets(1).Cells(NextRow, "B") = Sheets(1).Range("C4").Value
    Sheets(1).Cells(NextRow, "C") = Sheets(1).Range("C6").Value
    Sheets(1).Range("C4, C6").ClearContents
    Application.ScreenUpdating = True
    End Sub

    Sorry I have two further questions, the input cell boxes (C4 & C6) are actually merged cells, It tried it when they were merged but the code didn't work so I unmerged to make it work correctly.

    If there a day of keeping them merged and using the code? They are merged with the next column along so actually they are C4-D4 and C6-D6.

    My next challenge and I think where this might get problematic is that the spreadsheet will be shared, so if two people are in it, they input from data, run the code and it jumps into the next row, if the other person then tries because their version hasn't updated yet, it tries to input into the same row causing a conflict error and only one persons data can win.

    One thing I could think of would be before the above code is ran a save command is used which updates data from other uses, to show it all correctly and any rows completed, then inputs your data, then save again?

    Thank You

  8. #8
    Registered User
    Join Date
    09-23-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    68

    Re: VBA Code Or Function Help - Cut Information From One Cell And Paste Into Another

    I've tried most the day to make it work but having no joy, could anyone help me please?

    Thank You

  9. #9
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: VBA Code Or Function Help - Cut Information From One Cell And Paste Into Another

    I think you're right about saving - we can put a save command at the beginning and end, and it should be a tight enough window to avoid shared document errors.

    What do you want to happen with the merged cells? Are me moving the dual cells to NextRow? If so, we need a dual cell landing spot. Or are we moving the one cell value, but leaving blank merged cells behind?

    EDIT: If it's the latter, try this:

    Please Login or Register  to view this content.
    The downside to the dual save is that it slows down the code a bit...
    Last edited by CAntosh; 04-08-2016 at 10:15 AM.

  10. #10
    Registered User
    Join Date
    09-23-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    68

    Re: VBA Code Or Function Help - Cut Information From One Cell And Paste Into Another

    The data I want to move is in the merged cells, the name is in a merged cell across C4-D4 and a number across C6-D6 the name will then go into a single cell and the number into a single cell.

    Its only merged to make the appearance of the spreadsheet look a little neater.

  11. #11
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: VBA Code Or Function Help - Cut Information From One Cell And Paste Into Another

    Try the procedure I edited into post #9, I think it does what you've described. I tried to be sneaky and edit it in rather than make a new post.

  12. #12
    Registered User
    Join Date
    09-23-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    68

    Re: VBA Code Or Function Help - Cut Information From One Cell And Paste Into Another

    Didn't seem to quite work, came up with the conflict again?

  13. #13
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: VBA Code Or Function Help - Cut Information From One Cell And Paste Into Another

    what's your error message?

  14. #14
    Registered User
    Join Date
    09-23-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    68

    Re: VBA Code Or Function Help - Cut Information From One Cell And Paste Into Another

    Its the resolve conflicts again, but its not on the destination row it says about the input cells C4 (even though the other person has closed the spreadsheet), it works and updated for both people when you select accept mine on all, can this be automated to save the error message?

  15. #15
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: VBA Code Or Function Help - Cut Information From One Cell And Paste Into Another

    I haven't worked with many shared live files, and it's not something I can mimic to test with, so I'm not wholly familiar with the sort of errors you're getting - would it work if we turned off the alerts, or would that sacrifice something necessary? The procedure below simply turns off the alerts:

    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    09-23-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    68

    Re: VBA Code Or Function Help - Cut Information From One Cell And Paste Into Another

    Not quite sure it works, I need a code in there that when the resolve conflicts error comes up it automatically selects "Accept All Mine" (so the changes I have just made)

  17. #17
    Registered User
    Join Date
    09-23-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    68

    Re: VBA Code Or Function Help - Cut Information From One Cell And Paste Into Another

    This is the error I get, the person below added test 4 into the input box, it cleared the cell and dropped down into the right row and they close the spreadsheet.

    While im on it I enter test 5 and it comes up with the below error, if I choose accept all mine, test 4 and test 5 appear correctly in the right rows. Just a way of automating that?

    SCHOT.png

  18. #18
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: VBA Code Or Function Help - Cut Information From One Cell And Paste Into Another

    Try this one:

    Please Login or Register  to view this content.
    FYI: Several users (myself included) can't view .png files. I believe it's a compatibility issue. Hopefully the procedure above does the trick and makes it a moot point.

  19. #19
    Registered User
    Join Date
    09-23-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    68

    Re: VBA Code Or Function Help - Cut Information From One Cell And Paste Into Another

    Error message -

    Run-time error' 1004':

    Method 'AcceptAllChanges' of object ' _Workbook' failed

    JPEG of Conflict error -

    SCHOT.jpgSCHOT.jpg

  20. #20
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: VBA Code Or Function Help - Cut Information From One Cell And Paste Into Another

    http://www.excelforum.com/excel-prog...plication.html

    The post above is a few years old, but it doesn't bode well for a clean resolution to your issue. This might be possible for sendkeys, but sendkeys should generally be considered a last resort. Here's an effort with sendkeys attempting to handle both saves:
    Please Login or Register  to view this content.

+ 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. Replies: 1
    Last Post: 10-06-2015, 08:14 AM
  2. Replies: 1
    Last Post: 10-06-2015, 08:14 AM
  3. [SOLVED] paste source filepath information to destination book (code included)
    By macrorookie in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-24-2014, 03:15 PM
  4. Code to Copy & Paste Transpose Selected Information
    By tiger01 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-17-2011, 01:02 AM
  5. Macro or function to lookup and copy and paste the information
    By tjwm202 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-02-2009, 10:05 PM

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