+ Reply to Thread
Results 1 to 17 of 17

copying and pasting from one workbook to another

  1. #1
    Registered User
    Join Date
    08-30-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    95

    Lightbulb copying and pasting from one workbook to another

    ok so this is the code that i am having trouble with

    in the col G i have #N/A and some numbers of different parts in inventory (on a spread sheet called results)

    now what i would like is that if we have #N/A in column G it copies the following cells on the same line B, C, D, G, and H and copies them to another spread sheet
    I want to past these into the respective columns on the (Final results spread sheet) meaning B,C, D,G and H

    else #N/A is not in G (meaning there is a part number in column G) i would like it to copy the cells on the same line but this time cells E,F,G and H and then copy them to the final results spread sheet but this time have them shift over (E from results pasted in B Final results sheet, F pasted in C, G pasted in E and H pasted in F)

    do it while there is no more text in G

    i am not familiar too too much with the vba codeing so i'm asking for a little help
    Attached Files Attached Files
    Last edited by RoOmIE; 09-13-2013 at 09:02 AM. Reason: title

  2. #2
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: I need a little help with this code..

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

    Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...

  3. #3
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: copying and pasting from one workbook to another

    Appologize.. I forgot to mention one more thing.. Please

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  4. #4
    Registered User
    Join Date
    08-30-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    95

    Re: copying and pasting from one workbook to another

    i hope everything is clear ..

  5. #5
    Registered User
    Join Date
    08-30-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    95

    Re: copying and pasting from one workbook to another

    this is what i have so far but there are some bugs in the code


    Sub Final_result()
    Sheets("Result").Activate
    Columns("A:H").Select
    Selection.Copy
    Sheets(Array("Result", "Final Result")).Select
    Sheets("Final Result").Activate
    ActiveSheet.Paste
    Dim Data(4) As String
    Dim var As Variant
    Dim x As Byte
    x = 6

    Do

    If Cells(x, 5) = "#N/A" Then

    Else
    Cells(x, 5).Copy
    Cells(x, 2).Activate
    Cells(x, 2).Paste
    Cells(x, 6).Copy
    Cells(x, 3).Activate
    Cells(x, 3).Paste

    End If
    x = x + 1
    Loop While Cells(x, 5) = ""


    End Sub
    Last edited by RoOmIE; 09-12-2013 at 11:42 AM.

  6. #6
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: copying and pasting from one workbook to another

    I'm not sure what you wish to do since you talk about column G having a value of #N/A but in your uploaded example you have #N/A in coulmn E and F??

    I've written a macro that assumes you wish to use column E for the "guiding" #N/A value.

    Please Login or Register  to view this content.
    Alf

  7. #7
    Registered User
    Join Date
    08-30-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    95

    Re: copying and pasting from one workbook to another

    thanks alf ! its almost what i was looking for .. and it's very effective the only thing is that the code doesn't do is when the cell is not equal to NA put the column D and E in G and H (with your code) so that columns E and F would be either NA or blank ... D also has to be blank if E and F don't have NA in it

  8. #8
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: copying and pasting from one workbook to another

    Perhaps this then would be what you are looking for?

    Please Login or Register  to view this content.
    Alf

  9. #9
    Registered User
    Join Date
    08-30-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    95

    Re: copying and pasting from one workbook to another

    thanks again alf but ...

    Why does it start working only on row 5 ??

    i wish i could maybe understand your code so i could maybe correct it but i'm too much of a n00b
    oh and one more thing .. it seems too loop too many times .. a much too large of a final result tab ...
    Last edited by RoOmIE; 09-12-2013 at 02:56 PM.

  10. #10
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: copying and pasting from one workbook to another

    Why does it start working only on row 5 ??
    ??????

    Based on your uploaded file macro checks every cell in range E2 to E9. If you by row 5 mean column 5 i.e. the E column this is what I said in post 6

    I'm not sure what you wish to do since you talk about column G having a value of #N/A but in your uploaded example you have #N/A in column E and F??

    I've written a macro that assumes you wish to use column E for the "guiding" #N/A value.
    So I'm not sure of what you wish to achieve. You better upload a new sample file with raw data and how you wish that data to be "sorted" and I'll see if I can help you.

    Alf

  11. #11
    Registered User
    Join Date
    08-30-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    95

    Re: copying and pasting from one workbook to another

    did you see the answer tab on the 'raw data' that i have uploaded before? .. its just that the file that i use has over 3000 lines... what we want to do is basically erase all the data with the red lines and replace it with the black data and get rid of all of the NA's in the file .. i guess i was trying to explain it but i was beating around the bush ... i just thought it was easier to use the NA value since its not common to the other line .. congrats on your 1600th post

  12. #12
    Registered User
    Join Date
    08-30-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    95

    Re: copying and pasting from one workbook to another

    you are right about the column e being the guiding column though

  13. #13
    Registered User
    Join Date
    08-30-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    95

    Re: copying and pasting from one workbook to another

    RoOmIE2.xlsx
    this is what i would like .. thanks again alf

  14. #14
    Registered User
    Join Date
    08-30-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    95

    Re: copying and pasting from one workbook to another

    my Data file that i have here has 2414 lines and when i run the first macro i get 12060 lines that's almost a factor of 5 times too many also line 1 ... and it is also offset by 5 rows (instead of starting at row 2 it starts at row 5)

  15. #15
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: copying and pasting from one workbook to another

    This macro is what I really what I gave you earlier only difference now is that I've added the macro to your latest uploade file and changed sheet name in macro from "Final Result" to "Answer"

    Checking your wished for result you have missed one row i.e. row 18 sheet "Data" is not found in sheet "Your_answer".

    To test run macro "CopyTo"

    As far as I can see the result from macro corresponds to what you asked for excep for the one line you missed.

    If your other data files behaves differently I would sassume the have a different layout than yuor uploaded file.

    Alf
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    08-30-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    95

    Re: copying and pasting from one workbook to another

    ok i had some other code inter-fearing with yours ... i am sorry the last batch of code that you gave me works very well .. thank you very much!!

  17. #17
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: copying and pasting from one workbook to another

    No problem.

    Then I think you posted problem is solved? If so could you please mark thread "solved" as per forum rules.

    Alf

    Ps
    To mark your thread solved do the following:
    New Method
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

+ 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: 2
    Last Post: 03-09-2013, 04:30 AM
  2. Adding Text to another cell VB code (Help Tweeking code) (Excel 2007)
    By Excelnoub in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-06-2012, 11:37 AM
  3. Code for email alerts from excel isn't working, wrong code possibly?
    By jessthorogood in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-27-2012, 01:45 AM
  4. Replies: 2
    Last Post: 03-17-2011, 08:55 PM
  5. Replies: 0
    Last Post: 10-06-2006, 09:05 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