extract data change and update changes back to data sheet

[dbnfl]

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

 

[DanteAmor]

I mark some errors in your code.

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([B][COLOR=#ff0000]Cells(i, 3, 21)[/COLOR][/B]).Copy [COLOR=#ff0000]'Only one column should go. ie. [/COLOR][B][COLOR=#FF0000]Cells(i, 21)[/COLOR][/B]
      Sheets("sheet1").Activate
      [COLOR=#ff0000]Sheets("sheet1")[/COLOR].Range(Cells(j, 3, 33)).Select '[COLOR=#FF0000]'Only one column should go.
      'must be
       [/COLOR]Range(Cells(j, 3)),select
      ActiveSheet.Paste
      End If
    Next j
    Application.CutCopyMode = [COLOR=#ff0000][B]faluse [/B]'Must be False[/COLOR]
[COLOR=#ff0000]  [B]nexti [/B]'Must be Next i[/COLOR]
  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.

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([COLOR=#0000ff]"C"[/COLOR] & f.Row) = Sheets("sheet8").Range([COLOR=#0000ff]"C"[/COLOR] & 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.

 

[dbnfl]

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

 

[DanteAmor]

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.

 

[dbnfl]

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.

 

[dbnfl]

https://1drv.ms/w/s!Ah_y3HoNCprtbbZUTeL28kWvmnE

 

[DanteAmor]

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.

 

[dbnfl]

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

 

[DanteAmor]

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

 

[dbnfl]

Sheet1 from sheet 8