Page 1 of 2 12 LastLast
Results 1 to 40 of 50

Thread: Save data from one workbook to another book

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    108

    Save data from one workbook to another book

    Hi Everyone,
    I have two workbook (for ex. A and B)
    I have fill the details in specific cells in workbook A that details will be automatically update in specific cells in last blank row on workbook B (without opening or with opening workbook B).
    Is it possible?
    Can anyone help me..

    Thanks in advance...

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save data from one workbook to another book

    Is it possible?
    you can not read from or write to a workbook without opening it
    you can do it without it showing to the user, you can even do it without having excel installed, but you still have to open the workbook files

    Can anyone help me..
    so it depends on what you want to do and why?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    108

    Re: Save data from one workbook to another book

    Hi Westconn
    Thanks for your reply, I'm newbie to VBA. Could you please give me the code for update details in Book B with opening.

    Thanks

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save data from one workbook to another book

    assumes the code is in workbook A

    Code:
    set srcrange = Range(of specific cells)     ' i have no idea which cells you want to copy, in this case the range would be on the active worksheet, you could specify the specific worksheet and workbook
    set wbb = workbooks.open("fullpath\workbookB.xls")   ' change to suit
    set targrange = wbb.sheets(1).cells(rows.count, 1).end(xlup).offset(1) ' targrange would be A and first empty row on the first worksheet
    targrange.resize(, srcrange.cells.count).value = srcrange   ' this assumes that srcrange is contiguous in a row
    wbb.close true     ' save changes
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    108

    Re: Save data from one workbook to another book

    Hi westconn
    Thanks for your code
    I have tried but its not working for me. also tried to attach xlsx files but i getting error. so i have attached the image of the file for your reference. can you create vba using this images.


    Thanks for your help again.
    Last edited by gmmdinesh; Oct 19th, 2017 at 05:58 AM.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save data from one workbook to another book

    also tried to attach xlsx files
    zip first

    you could copy the 2 columns of data using transpose function like

    Code:
    set wbb = workbooks.open("fullpath\workbookB.xls")   ' change to suit
    set targrange = wbb.sheets(1).cells(rows.count, 1).end(xlup).offset(1) ' targrange would be A and first empty row on the first worksheet
    targrange.resize(, 6).value = Application.transpose(range("c5:c10").value)   
    targrange.offset(,6).resize(,6).value =Application.transpose(range("e5:e10").value)
    targrange.offset(, 12).value = range("c11").value
    
    wbb.close true     ' save changes
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    108

    Question Re: Save data from one workbook to another book

    Hi westconn
    Thank you so much help again..
    here i have attached the required files. zip have three files A,B and C. can you add one more process on B file. once getting details from A file that detail have to update into C file. B file have a command button. when i click the button the selected row copied and find the selected network number on C file and paste entire row.
    Is that possible.

    Much appreciate for help.


    Attachment 151721

    Actually i'm not a expert in VBA. Your code starting with set wbb but normally codes started with sub, could you please clarify how to run this.
    Last edited by gmmdinesh; Oct 19th, 2017 at 05:58 AM.

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save data from one workbook to another book

    Your code starting with set wbb
    put the code into a procedure (sub), declare the variables used, the procedure can be the button click event or anyother event or procedure that suits you

    Is that possible.
    i am sure it is, i will look at your workbooks later
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  9. #9

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    108

    Re: Save data from one workbook to another book

    Thank you so much for your kind reply..
    I will wait for your next reply..
    Thaks a lot..

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save data from one workbook to another book

    find the selected network number on C file and paste entire row.
    it is easy up to found the cell containing the network number, but i have no idea which entire row you want to paste to where

    Code:
    Sub bclick()
    Dim p As String, targrange As Range, wbb As Workbook, fnd As Range
    p = ThisWorkbook.Path & "\"  ' assumes that all 3 workbooks in same folder
    
    'ActiveWorkbook.SaveAs p & "a.xls"
    
    Set wbb = Workbooks.Open(p & "a.xls")   ' change to suit
    Set targrange = ThisWorkbook.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1)
    targrange.Resize(, 6).Value = Application.Transpose(wbb.Sheets(1).Range("c5:c10").Value)
    targrange.Offset(, 6).Resize(, 6).Value = Application.Transpose(wbb.Sheets(1).Range("e5:e10").Value)
    targrange.Offset(, 12).Value = wbb.Sheets(1).Range("c11").Value
    wbb.Close False     ' do not save changes
    
    Set wbb = Workbooks.Open(p & "c.xls")   ' change to suit
    Set fnd = wbb.Sheets(1).Range("b:b").Find(targrange)
    msgbox "network number found in cell " & fnd.address  ' you can remove this line
    '   do some paste here
    wbb.Close False
    End Sub
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  11. #11

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    108

    Re: Save data from one workbook to another book

    I have to select the row and click command button on file B, once click the button that row will copied and goto file C, then find the Network number ( which Network number is in copied row in file B) if the Network in there then it will be paste the copied row (from file B) on entire row (which row having the Network number to select by find )on file C.
    Could you please assign the macros in already attached files?

    Thank you so much.

  12. #12
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save data from one workbook to another book

    Could you please assign the macros in already attached files?
    just assign the bclick macro to your button, or copy the code into the existing procedure

    then it will be paste the copied row (from file B) on entire row (which row having the Network number to select by find )on file C.
    you want to overwrite the existing line?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  13. #13

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    108

    Re: Save data from one workbook to another book

    Yes, i want paste it as overwrite

  14. #14

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    108

    Re: Save data from one workbook to another book

    Quote Originally Posted by westconn1 View Post

    you want to overwrite the existing line?
    Yes, i want paste it as overwrite

    Sorry for again question.
    Which file i have to paste this macro file A or B?
    Actually i need two macro. one should be file A(so that i can update the data from file A to B)
    another one should be file B ( so that i can update the data from file B to C)
    A---->B--->C
    Thanks for your time.
    Last edited by gmmdinesh; Sep 13th, 2017 at 09:16 AM.

  15. #15

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    108

    Re: Save data from one workbook to another book

    I have tried this code it was working good.
    but it's only find the network number on file C
    how can update the data from file A to file B.

    Thanks
    Last edited by gmmdinesh; Sep 13th, 2017 at 09:25 AM.

  16. #16
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save data from one workbook to another book

    Which file i have to paste this macro file A or B?
    file B where your button is
    but it's only find the network number on file C
    it should have already updated file B, by the time it finds network number

    try adding below to update file C
    Code:
    Set fnd = wbb.Sheets(1).Range("b:b").Find(targrange)
    fnd.Resize(, 13).Value = targrange.Resize(, 13).Value
    wbb.Close True   ' save changes
    if you are updating the files multiple times it would probably be better to keep the files a and c open, until finished with

    edit: note i changed the files to xls for testing here, so make sure to change the file extensions in the code
    Last edited by westconn1; Sep 13th, 2017 at 04:21 PM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  17. #17

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    108

    Re: Save data from one workbook to another book

    Hi westconn
    Thanks for your code.
    I have tried your code unfortunately its not working properly.I think i'm not assign properly.
    also when i run the macro on file A the 5th row will removed.

    File located in different folder path. file A located in desktop and file B and C located in different folder (in E drive)

    Please see the Attached files.

    Again sorry for continue this forum.

    Thanks.Attachment 151767
    Last edited by gmmdinesh; Oct 19th, 2017 at 05:59 AM.

  18. #18
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save data from one workbook to another book

    File located in different folder path.
    change the 2 workbooks.open to the full correct path

    file A the 5th row will removed.
    in which workbook

    unfortunately its not working properly
    i tested all the code before posting, but of course you can not expect it to work properly (if at all) until you give the correct paths for workbooks A and C

    I think i'm not assign properly.
    you need to change the button code to be the macro with the code you want to run, that will be different in your version of excel, but will still have to click the design mode button then assign macro, no ribbon here, if you can not figure it i may have to have a look on a machine with later version of excel when i can

    i just noticed that you are opening file B, but you specified you want the code to run from a button in workbook B, so it would already be open and you should be opening file A, then file C, probably this is why some data is being deleted in from where it should not
    though i am surprised some error is not occurring, or at least a dialog to warn about re-opening an open workbook
    Last edited by westconn1; Sep 14th, 2017 at 04:09 AM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  19. #19

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    108

    Re: Save data from one workbook to another book

    Quote Originally Posted by westconn1 View Post
    in which workbook
    In workbook A

  20. #20

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    108

    Re: Save data from one workbook to another book

    Can you attach the file with macros which you tested.

  21. #21
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save data from one workbook to another book

    they are as pasted in the posts above, but here it is all in one place
    Code:
    Sub bclick()
    Dim p As String, targrange As Range, wbb As Workbook, fnd As Range
    p = ThisWorkbook.Path & "\"  ' assumes that all 3 workbooks in same folder
    
    Set wbb = Workbooks.Open(p & "a.xlsm")   ' change to suit
    Set targrange = ThisWorkbook.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1)
    targrange.Resize(, 6).Value = Application.Transpose(wbb.Sheets(1).Range("c5:c10").Value)
    targrange.Offset(, 6).Resize(, 6).Value = Application.Transpose(wbb.Sheets(1).Range("e5:e10").Value)
    targrange.Offset(, 12).Value = wbb.Sheets(1).Range("c11").Value
    wbb.Close False     ' do not save changes
    
    Set wbb = Workbooks.Open(p & "c.xlsm")   ' change to suit
    Set fnd = wbb.Sheets(1).Range("b:b").Find(targrange)
    fnd.Resize(, 13).Value = targrange.Resize(, 13).Value
    wbb.Close True   ' save changes
    End Sub
    you will still need to change the paths of the file on the desktop, there is code you can use to find the desktop folder path, but if this is only for use on your own computer, just type in the path for your desktop
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  22. #22

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    108

    Re: Save data from one workbook to another book

    Hi westconn
    Your code partially working what i am expected. Thanks.
    But need some changes on this, your code will work only on Workbook B.when i run the macro on Workbook B it was getting data from workbook A and paste to workbook B, but i want run the macro on workbook A (for copy paste data from workbook A to B only) so that i can send and update data from workbook A to B.(This Macro should be in workbook A only)

    Another one Macro need to run Workbook B (this will be update the selected row from file B to C) ( this Macro should be in Workbook B only.)
    This macro should be done the following steps
    1.Select a row (by manually) and the click the button
    2.Copy the row
    3.open the workbook C
    4.Find the Network No.
    5.If the Network number is there then Paste copied data on that row.(row selected by find method)
    and also current code will not update the data from workbook B to C.
    I got a debug error while running the code as below.

    Name:  Capture1.jpg
Views: 178
Size:  48.5 KB



    Thank you so much for your Time.
    Last edited by gmmdinesh; Sep 14th, 2017 at 01:14 PM.

  23. #23
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save data from one workbook to another book

    I got a debug error while running the code as below.
    that error indicates the network number searched was not found

    but i want run the macro on workbook A (for copy paste data from workbook A to B only) so that i can send and update data from workbook A to B.(This Macro should be in workbook A only)
    use the code as in post #6, put in a procedure, declare variables and change path of workbook B, assign correct procedure to commandbutton

    Another one Macro need to run Workbook B (this will be update the selected row from file B to C) ( this Macro should be in Workbook B only.)
    Code:
    Set wbb = Workbooks.Open(p & "c.xlsm")   ' change to suit
    Set fnd = wbb.Sheets(1).Range("b:b").Find(activecell)
    if fnd is nothing then    ' if network number not found
         msgbox activecell & " not found, do manually": exit sub
    end if
    fnd.Resize(, 13).Value = activecell.Resize(, 13).Value
    wbb.Close True   ' save changes
    again paste into a procedure, declare variables, change path to workbook C, assign to commandbutton
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  24. #24

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    108

    Re: Save data from one workbook to another book

    I have tried the code which you posted in Post#6 it's not working for me in work book A. it's only open and close the work book B.
    but it was working perfectly from workbook B. when i run the code from Book B, it was getting data from Book A and update in Book B. but i want update the data from workbook A to B only.

    also WorkBook B to C macro not find anything even same Network number is there ( workbook B and C).
    I have attached the files please see that. i don't know why its not working for me.

    Please don't mistake me for again and again the question to you. i'm not a Expert in VBA, i'm newbie for VBA. better you can attach the file with Macros please.

    Thanks.
    Last edited by gmmdinesh; Oct 19th, 2017 at 06:00 AM.

  25. #25
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save data from one workbook to another book

    i tested the code in your workbook A, in the post procedure, it worked without error and filled the next row in workbook B

    if you were watching what was happening in workbook B, the code would fail as it copies from the activesheet, as you can see from this, it is bad practice to rely using the activesheet (or active anything), a minor change will resolve this

    Code:
    Sub post()
    Dim p As String, targrange As Range, wbb As Workbook, fnd As Range
    Set wbb = Workbooks.Open("C:\Users\gmmdinesha\Desktop\B.xls")   ' change to suit
    
    Set targrange = wbb.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1) ' targrange would be B and first empty row on the first worksheet
    targrange.Resize(, 6).Value = Application.Transpose(thisworkbook.sheets(1).Range("c5:c10").Value)
    targrange.Offset(, 6).Resize(, 6).Value = Application.Transpose(thisworkbook.sheets(1).Range("e5:e10").Value)
    targrange.Offset(, 12).Value = thisworkbook.sheets(1).Range("c11").Value
    
    wbb.Close True     ' save changes
    End Sub
    as you can see the ranges in workbook A (thisworkbook, the workbook where the code is written) are now fully qualified and no confusion can occur
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  26. #26

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    108

    Re: Save data from one workbook to another book

    Hi westconn
    Thank you so much...Hats off for your work. its working perfect.
    also please provide the macro for workbook B
    This macro should be done the following steps
    1.Select a row (by manually) and the click the button
    2.Copy the row
    3.open the workbook C
    4.Find the Network No. (which is located in copied row)
    5.If the Network number is there then Paste copied data on that row on workbook C.(row selected by find method).

    The code which you given on post no. 23 was not working , it was show the message only.


    Thanks

  27. #27
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save data from one workbook to another book

    The code which you given on post no. 23 was not working
    it worked when i tested, i will look again later
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  28. #28

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    108

    Re: Save data from one workbook to another book

    Okay..Thanks.
    I'll wait for your Reply.

  29. #29
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save data from one workbook to another book

    it has occurred to me that it is the same problem you had with the other code, relying on an active object, generally avoid using active anything, but in this case it is required, to get the selected cell

    when the workbook opens the sheet in that workbook becomes the active one, now containing the activecell, not the one selected, change like

    Code:
    set src = activecell     'get the activecell range first
    Set wbb = Workbooks.Open(p & "c.xlsm")   ' change to suit
    Set fnd = wbb.Sheets(1).Range("b:b").Find(src)
    if fnd is nothing then    ' if network number not found
         msgbox src & " not found, do manually": exit sub
    end if
    fnd.Resize(, 13).Value = src.Resize(, 13).Value
    wbb.Close True   ' save changes
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  30. #30

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    108

    Re: Save data from one workbook to another book

    Thank you so much.
    This is awesome, working perfectly.

    Can you add one more step on it.
    Once update the detail on work book C. the selected row on workbook B automatically should be removed.

    and also i want to change the process steps on macro in workbook B
    1. Select a row (by manually) and the click the button
    2.Copy the each cells seperately
    3.open the workbook C
    4.Find the Network No. (which is located in copied row)
    5.If the Network number is there then Paste copied data on different cells on that same row on workbook C.(row selected by find method).

    please let me know how to change the code.

    Thanks again.

  31. #31
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save data from one workbook to another book

    the selected row on workbook B automatically should be removed.
    add one line at the end
    Code:
    src.EntireRow.Delete xlShiftUp
    ThisWorkbook.Save    ' if required or can leave until close workbook
    please let me know how to change the code.
    remove the line
    fnd.Resize(, 13).Value = src.Resize(, 13).Value
    then copy the cells like
    Code:
    fnd.offset(, 1) = src.offset(, 3)  ' copy column D to column B
    fnd.offset(,5) = src.offset(, 4)   ' copy column E to column F
    add a line for each cell to be copied, using the appropriate offsets, if there are some consecutive cells you can combine offset and resize, to assign several cells in one line, but you need to use the value property of the range (as in the line to remove) when the resized range contains multiple cells
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  32. #32

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    108

    Re: Save data from one workbook to another book

    src.EntireRow.Delete xlShiftUp
    ThisWorkbook.Save ' if required or can leave until close workbook
    This is working good. thank you so much.

    fnd.offset(, 1) = src.offset(, 3) ' copy column D to column B
    fnd.offset(,5) = src.offset(, 4) ' copy column E to column F
    I will try this code and get back to you.

  33. #33

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    108

    Re: Save data from one workbook to another book

    Hi Westconn

    This code also working good. Thanks a lot.
    Code:
    fnd.offset(, 1) = src.offset(, 3)  ' copy column D to column B
    fnd.offset(,5) = src.offset(, 4)   ' copy column E to column F
    I want to add more lines, but i don't know how to select offset range, because in this code you mentioned
    Code:
    fnd.offset(, 1) = src.offset(, 3)  ' copy column D to column B
    offset(, 1) = offset(, 3) ' copy column D to column B, column D means 4th column, B means 2nd Column, but here you are used 1 and 3.
    Please clarify this.

    Thanks.

  34. #34
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save data from one workbook to another book

    the actual cell is offset 0
    so from column A offset(, 1) is column B, 2 is C 3 is D etc

    in this case, src is in column A and fnd is column B
    so the columns of copy from were ok, but copy to columns, in my example in post #31 were incorrect, should have been columns C and G
    anyway a bit of trial and error should soon clarify
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  35. #35

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    108

    Resolved [SOLVED] Save data from one workbook to another book

    Hi westconn
    Thank you so much for your effort. your code working fine.
    also thanks for the clarification regarding offset. now i got it .

    Thanks again.

  36. #36

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    108

    Lightbulb Re: Save data from one workbook to another book

    Hi Westconn
    I want to update selected cells to a single row in next sheet.(in same Workbook)
    This is the code you provided. This is working good.
    Code:
    Sub Update()
    Dim p As String, targrange As Range, wbb As Workbook, fnd As Range
    Set wbb = Workbooks.Open("C:\\Users\Dinesh\dashboard\B.xlsm")   ' change to suit
    
    Set targrange = wbb.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1) ' targrange would be B and first empty row on the first worksheet
    targrange.Resize(, 10).Value = Application.Transpose(ThisWorkbook.Sheets(1).Range("c5:c14").Value)
    targrange.Offset(, 10).Resize(, 10).Value = Application.Transpose(ThisWorkbook.Sheets(1).Range("e5:e14").Value)
    'targrange.Offset(, 12).Value = ThisWorkbook.Sheets(1).Range("c11").Value
    
    wbb.Close True     ' save changes
       
          
    End Sub
    This code was update the data from one work book to another workbook.
    I need to update next sheet also.
    so this code will be find the network number in Sheet2 (in same workbook) and paste as single row.
    Could you please made change on this code.

  37. #37
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save data from one workbook to another book

    add this at the end of the existing code
    Code:
    Set sht2 = thisworkbook.sheets("sheet2") 
    set netnum = thisworkbook.sheets("Portal").range("c5")     ' change if i got this wrong
    Set fnd = sht2.Range("b:b").Find(netnum)
    if fnd is nothing then    ' if network number not found
         msgbox netnum & " not found, do manually": exit sub
    end if
    fnd.Resize(, 20).Value = targrange.Resize(, 20).Value
    try this, i am not sure if it copies the range as desired, but at least it should be in the right place

    if i was doing from scratch, i would copy to the same workbook first, but as the other code is already written...........
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  38. #38

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    108

    Re: Save data from one workbook to another book

    Thanks westconn,
    But this is not working..
    I got Error like variable not fond on first line..
    Code:
    .   Set sht2 = thisworkbook.sheets("sheet2")

  39. #39
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save data from one workbook to another book

    I got Error like variable not fond on first line..
    i presume this should be variable not defined?

    define like
    Code:
    dim sht2 as worksheet
    or add to an existing Dim line
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  40. #40

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    108

    Re: Save data from one workbook to another book

    I have tried.... I got same error again...i have attached the file here. could you please check the attached file.

    Thanks.Attachment 152153
    Last edited by gmmdinesh; Oct 19th, 2017 at 06:01 AM.

Page 1 of 2 12 LastLast

Posting Permissions

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



Click Here to Expand Forum to Full Width