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]

Sheet1 from sheet 8

Try this

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

Sub Update_Sheet()
  Dim c As Range, f As Range, sh1 As Worksheet, sh2 As Worksheet
  Set sh1 = Sheets("[COLOR=#ff0000]sheet1[/COLOR]")
  Set sh2 = Sheets("[COLOR=#ff0000]sheet8[/COLOR]")
  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

 

[dbnfl]

hello,

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

Set sh2 = sheets("sheet8")

 

[dbnfl]

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

 

[dbnfl]

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

 

[DanteAmor]

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

 

[dbnfl]

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

 

[DanteAmor]

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!