Page 1 of 2 12 LastLast
Results 1 to 10 of 17

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

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

    Default extract data change and update changes back to data sheet

    Hi all,

    I have a code o extract data (works perfect) I now am trying to paste that data back to data sheet if changed.

    I have a command button I have attached below code, when I click I get msg "Compile error: Wrong number of arguments or invalid property assignment"

    Sub Pre_Alert_Update()
    Dim i As Long
    Dim j As Long
    Dim lastrow1 As Long
    Dim lastrow2 As Long
    Dim NFLJobNo As String

    lastrow1 = Sheets("sheet8").Range("B" & Rows.Count).End(xlUp).Row
    For i = 2 To lastrow1
    NFLJobNo = Sheets("sheet8").Cells(i, "B").Value
    Sheets("sheet1").Activate
    lastrow2 = Sheets("sheet1").Range("B" & Rows.Count).End(xlUp).Row
    For j = 2 To lastrow2
    If Sheets("sheets1").Cells(j, "A").Value = NFLJobNo Then
    Sheets("sheet8").Activate
    Sheets("sheet8").Range(Cells(i, 3, 21)).Copy
    Sheets("sheet1").Activate
    Sheets("sheet1").Range(Cells(j, 3, 33)).Select
    ActiveSheet.Paste

    End If
    Next j
    Application.CutCopyMode = faluse
    nexti
    Sheets("sheet8").Activate
    Sheets("sheet8").Range("G3").Select

    End Sub


    Regards,

    Dale

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

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

    I mark some errors in your code.

    Quote Originally Posted by dbnfl View Post
    Hi all,
    I have a code o extract data (works perfect) I now am trying to paste that data back to data sheet if changed.
    I have a command button I have attached below code, when I click I get msg "Compile error: Wrong number of arguments or invalid property assignment"
    Code:
    Sub Pre_Alert_Update()
      Dim i As Long
      Dim j As Long
      Dim lastrow1 As Long
      Dim lastrow2 As Long
      Dim NFLJobNo As String
      lastrow1 = Sheets("sheet8").Range("B" & Rows.Count).End(xlUp).Row
      For i = 2 To lastrow1
        NFLJobNo = Sheets("sheet8").Cells(i, "B").Value
        Sheets("sheet1").Activate
        lastrow2 = Sheets("sheet1").Range("B" & Rows.Count).End(xlUp).Row
        For j = 2 To lastrow2
          If Sheets("sheets1").Cells(j, "A").Value = NFLJobNo Then
          Sheets("sheet8").Activate
          Sheets("sheet8").Range(Cells(i, 3, 21)).Copy 'Only one column should go. ie. Cells(i, 21)
          Sheets("sheet1").Activate
          Sheets("sheet1").Range(Cells(j, 3, 33)).Select ''Only one column should go.
          'must be
           Range(Cells(j, 3)),select
          ActiveSheet.Paste
          End If
        Next j
        Application.CutCopyMode = faluse 'Must be False
      nexti 'Must be Next i
      Sheets("sheet8").Activate
      Sheets("sheet8").Range("G3").Select
    End Sub
    I simplified your code this way:

    What I understood is the following. Take each of the data in column B of the sheet8. Search for them on sheet1 in column A and if find it, then copy the value from column C of sheet8 and paste it into column C of sheet1.

    Code:
    Sub Pre_Alert_Update_1()
      Dim c As Range, f As Range, r As Range, cell As String
      Set r = Sheets("sheet1").Range("A:A")
      For Each c In Sheets("sheet8").Range("B2", Sheets("sheet8").Range("B" & Rows.Count).End(xlUp))
        If c.Value <> "" Then
          Set f = r.Find(c.Value, , xlValues, xlWhole)
          If Not f Is Nothing Then
            cell = f.Address
            Do
              Sheets("sheet1").Range("C" & f.Row) = Sheets("sheet8").Range("C" & c.Row)
              Set f = r.FindNext(f)
            Loop While Not f Is Nothing And f.Address <> cell
          End If
        End If
      Next
    End Sub
    Let me know if you have any doubt.
    Regards Dante Amor

  3. #3
    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,

    Than you so much for your help.

    I extract the data from Sheet1 to Sheet8, I then want to change cells in columns "C" & "U". Once changed I want to update that info back to sheet1 matching rows on column "B". When it goes back to sheet1 I need it to go in to columns "C" & "AG" matching to column "B"

    I have header in row 6, data extracted gets pasted on sheet8 from sheet1 into row 7 and down.

    I tried your code, getting run time error '9': Subscript out of range

    debug highlights 'For Each c In Sheets("sheet8").Range("B2", Sheets("sheet8").Range("B" & Rows.Count).End(xlUp))

    Dale

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,260
    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
    I tried your code, getting run time error '9': Subscript out of range
    debug highlights 'For Each c In Sheets("sheet8").Range("B2", Sheets("sheet8").Range("B" & Rows.Count).End(xlUp))
    Then I did not understand your code.
    Let's forget the code a bit.


    You can explain what you need to do with examples.


    Also tell me what version of Excel you have.
    Regards Dante Amor

  5. #5
    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

    Microsoft 360 – Excel

    The code I have now toextract data from Sheet1(Data)
    Option Explicit

    Sub Extract_Data()

    Dim datasheet As Worksheet
    Dim reportsheet As Worksheet
    Dim jobstatus As String
    Dim agent As String
    Dim jobtype As String

    Dim finalrow As Integer
    Dim i As Integer
    Dim Ary As Variant

    Set datasheet = Sheet1
    Set reportsheet = Sheet8

    jobstatus = LCase(reportsheet.Range("C3").Value)
    agent =LCase(reportsheet.Range("E3").Value)
    jobtype =LCase(reportsheet.Range("G3").Value)


    reportsheet.Range("B7:W200").ClearContents

    datasheet.Select
    finalrow =Cells(Rows.Count, 2).End(xlUp).Row
    For i = 2 Tofinalrow

    IfLCase(Cells(i, 3)) = jobstatus And LCase(Cells(i, 5)) = agent AndLCase(Cells(i, 4)) = jobtype Then
    Ary =Application.Index(Rows(i), 1, Array(2, 3, 4, 5, 6, 8, 9, 10, 16, 18, 19, 27,41, 42, 44, 46, 47, 49, 51))
    reportsheet.Range("B200").End(xlUp).Offset(1, 0).Resize(,19).Value = Ary
    End If
    Next i

    reportsheet.Select

    Range("C3").Select

    End Sub

    This works perfect.

    Then below code sorts its
    Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)

    Range("B6").CurrentRegion.Sortkey1:=Range("L6"), order1:=xlAscending, Header:=xlYes
    Range("B6").CurrentRegion.Sortkey1:=Range("p6"), order1:=xlAscending, Header:=xlYes
    Range("B6").CurrentRegion.Sortkey1:=Range("o6"), order1:=xlAscending, Header:=xlYes

    End Sub

    On Sheet8(Pre Alert) I want to change data in columns C& U.
    Once data is changed, I want to click on UPDATE (Button) andit paste this updated data back to Sheet1(Data) overriding data in cells usingcolumn B as reference to match.
    [IMG]file:///C:/Users/Dale/AppData/Local/Temp/msohtmlclip1/01/clip_image002.jpg[/IMG]


  6. #6
    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


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

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

    As I mentioned in post #4 . Explain without code.


    By the way, I think you put another code. This last code is not the same as the first code.
    Regards Dante Amor

  8. #8
    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,
    Thank you for your time.Sorry the last code was showing how the data extracts.



    What I'm trying to do I,


    Sheet8(Pre Alert)
    On this sheet I want to editthe data in columns C & U.
    I have a button on this sheetcalled Update, I want to click the button and I want to transfer the updateddata back to Sheet1(Data) in columns C & AG only if Column B is a matchwith Colum B on Sheet8(Pre Alert)
    Column “B” on both sheets hasthe same data NFL Job No. each row is a job. I want to use this as the Lookup& Match

    Sheet1(Data) is my main datasheet.
    I’ve also pasted the belowlink to a Screen shot of Sheet8(Pre Alert) Green are the two columns I want toedit.
    https://1drv.ms/w/s!Ah_y3HoNCprtbbZUTeL28kWvmnE


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

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

    sorry but I'm confused, do you want to update sheet8 or sheet1?
    Regards Dante Amor

  10. #10
    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

    Sheet1 from sheet 8

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
  •