extract data change and update changes back to data sheet

dbnfl

Board Regular
Joined
Aug 11, 2019
Messages
59
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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"
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([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.

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([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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
clip_image002.jpg


 
Upvote 0
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.
 
Upvote 0
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

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

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top