MsOf13 Save data from one workbook to another book - Page 2-VBForums
Page 2 of 2 FirstFirst 12
Results 41 to 50 of 50

Thread: Save data from one workbook to another book

  1. #41
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,133

    Re: Save data from one workbook to another book

    from post #37
    add this at the end of the existing code
    as part of the previously existing code is no longer there targrange is now invalid
    I need to update next sheet also.
    so it appeared you still needed all the previous code


    also you need dim on line 4
    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

  2. #42

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    76

    Re: Save data from one workbook to another book

    Still I'm confused... can you please give the code..
    I need to update the data to next sheet in same workbook at the same time it should be update to workbook B.
    You already give code to update on workbook B that's working fine...
    Un that same code i want add this steps..so that when i click the button code to find the Network no. For next sheet and paste as well as paste to workbook B.

  3. #43
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,133

    Re: Save data from one workbook to another book

    try like
    Code:
        Sub Update()
        Dim p As String, targrange As Range, wbb As Workbook, fnd As Range, sht2 as worksheet
        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)
           
         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  ' change the resize it these are incorrect
         wbb.Close True     ' save changes
            
        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

  4. #44

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    76

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

    Thank you so much Westconn
    This is working Perfect.

    Thank you

  5. #45

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    76

    Re: Save data from one workbook to another book

    Hi westconn
    Sorry for disturb again.
    I have make small changes on this code,as per changes it should be find the network no. on backups sheet if Network no is there it should be replace the data if the network no is not there this should be paste in last blank row.
    Code:
    Sub Update()
    Dim p As String, targrange As Range, wbb As Workbook, fnd As Range, sht2 As Worksheet
    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
    Set sht2 = ThisWorkbook.Sheets("Backups")
        Set netnum = ThisWorkbook.Sheets("Portal").Range("c5")    ' change if i got this wrong
        Set fnd = sht2.Range("a:a").Find(netnum)
          If fnd Is Nothing Then    ' if network number not found
             sht2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues: Exit Sub
                    End If
        fnd.Resize(, 20).Value = targrange.Resize(, 20).Value  ' change the resize it these are incorrect
    wbb.Close True     ' save changes
    
    End Sub

    But when i try to run this code getting error like..
    Pastespecial Method of Range class failed

    Please help me how to solve this.

    Thanks

  6. #46
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,133

    Re: Save data from one workbook to another book

    the paste failed as the data had never been "copied"

    just assign the values to the resized range

    i would change like
    Code:
      If fnd Is Nothing Then    ' if network number not found
             set  fnd = sht2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
                    End If
    the following line should then copy the data to the correct location regardless
    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. #47

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    76

    Re: Save data from one workbook to another book

    Thank you so much ...It was working perfect

  8. #48

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    76

    Re: Save data from one workbook to another book

    Hi Westconn
    I need help again.....
    Actually this working good...but i want add one more option on it.. current code copy the data and find the Network number on sheet 2(Backups) if Network number already there it will replace the data or Network number not found it will paste in new row.. What i want to add in this code is before paste the sheet 2 it will find the duplicate network number if duplicate number is there then it will show a message, if duplicate number is not there then it will paste as per current code..( because in sheet 2 some duplicate network number is there)
    Code:
    Sub Update()
    Dim p As String, targrange As Range, wbb As Workbook, fnd As Range, sht2 As Worksheet
    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
    Set sht2 = ThisWorkbook.Sheets("Backups")
        Set netnum = ThisWorkbook.Sheets("Portal").Range("c5")    ' change if i got this wrong
        Set fnd = sht2.Range("a:a").Find(netnum)
          If fnd Is Nothing Then    ' if network number not found
             sht2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues: Exit Sub
                    End If
        fnd.Resize(, 20).Value = targrange.Resize(, 20).Value  ' change the resize it these are incorrect
    wbb.Close True     ' save changes
    
    End Sub
    Thanks

  9. #49
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,133

    Re: Save data from one workbook to another book

    try like

    Code:
    If Application.WorksheetFunction.CountIf(sht2.Range("a:a"), netnum) > 1 Then MsgBox "duplicates exist"
    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

  10. #50

    Thread Starter
    Lively Member
    Join Date
    Sep 2017
    Posts
    76

    Re: Save data from one workbook to another book

    Excellent , thanks for your Quick reply....its working fine....

    Thank you so much

Page 2 of 2 FirstFirst 12

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.