Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

Thread: extract data change and update changes back to data sheet

  1. #11
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,261
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: extract data change and update changes back to data sheet

    Quote Originally Posted by dbnfl View Post
    Sheet1 from sheet 8
    Try this

    Update cells C and U on sheet1 from cells C and AG of sheet8

    Code:
    Sub Update_Sheet()
      Dim c As Range, f As Range, sh1 As Worksheet, sh2 As Worksheet
      Set sh1 = Sheets("sheet1")
      Set sh2 = Sheets("sheet8")
      For Each c In sh1.Range("B2", sh1.Range("B" & Rows.Count).End(xlUp))
        If c.Value <> "" Then
          Set f = sh2.Range("B:B").Find(c.Value, , xlValues, xlWhole)
          If Not f Is Nothing Then
            sh1.Range("C" & c.Row) = sh2.Range("C" & f.Row)
            sh1.Range("U" & c.Row) = sh2.Range("AG" & f.Row)
          End If
        End If
      Next
      MsgBox "End"
    End Sub
    Regards Dante Amor

  2. #12
    New Member
    Join Date
    Aug 2019
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: extract data change and update changes back to data sheet

    hello,

    I get run-time error '9':
    Subscript out of range
    when debug highlights bel part of code

    Set sh2 = sheets("sheet8")

  3. #13
    New Member
    Join Date
    Aug 2019
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: extract data change and update changes back to data sheet

    sorry it should update other way round

    'Update cells C and U on sheet1 from cells C and AG of sheet8'

    I want to update

    Update cells C and AG on sheet1 from cells C and U of sheet8
    Sub Update_Sheet()
    Dim c As Range, f As Range, sh1 As Worksheet, sh2 As Worksheet
    Set sh1 = Sheets("sheet1")
    Set sh2 = Sheets("sheet8")
    For Each c In sh1.Range("B2", sh1.Range("B" & Rows.Count).End(xlUp))
    If c.Value <> "" Then
    Set f = sh2.Range("B:B").Find(c.Value, , xlValues, xlWhole)
    If Not f Is Nothing Then
    sh1.Range("C" & c.Row) = sh2.Range("C" & f.Row)
    sh1.Range("AG" & c.Row) = sh2.Range("U" & f.Row)
    End If
    End If
    Next
    MsgBox "End"
    End Sub

  4. #14
    New Member
    Join Date
    Aug 2019
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: extract data change and update changes back to data sheet

    I change the sheet names and it all works. Thank you so much.

  5. #15
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,261
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: extract data change and update changes back to data sheet

    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

  6. #16
    New Member
    Join Date
    Aug 2019
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: extract data change and update changes back to data sheet

    Hello,

    With below code can I add in another range to get my copy paste based of wo columns

    ub Import_Vessel_ETA_Update()

    Dim c As Range, f As Range, sh1 As Worksheet, sh2 As Worksheet
    Set sh1 = Sheets("Data")
    Set sh2 = Sheets("Wharf Schedules")
    For Each c In sh1.Range("AR2", sh1.Range("AR" & Rows.Count).End(xlUp))
    If c.Value <> "" Then
    Set f = sh2.Range("C:C").Find(c.Value, , xlValues, xlWhole)
    If Not f Is Nothing Then
    sh1.Range("AO" & c.Row) = sh2.Range("B" & f.Row)
    sh1.Range("AP" & c.Row) = sh2.Range("G" & f.Row)
    sh1.Range("AQ" & c.Row) = sh2.Range("I" & f.Row)
    End If
    End If
    Next
    MsgBox "Vessel details have been updated in main Data Sheet"
    End Sub

  7. #17
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,261
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: extract data change and update changes back to data sheet

    Quote Originally Posted by dbnfl View Post
    With below code can I add in another range to get my copy paste based of wo columns
    That is the idea, that you can modify the code to your needs. Well done!
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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