Transferring data between worksheets?

[Doug]

After making a backup copy and importing new web data, I need a way to
transfer my old notes to the new imported web data. Some of the rows get
dropped from the new imported data but I would like a formula that can figure
out the rows that still match the old data and put the comments back into the
appropriate cells.

In VBA I make a copy of a worksheet to another workbook as a backup. Then it
imports new web data to update my old. Much of this new data remains the same
and I would like to be able to transfer comments that I have made from the
old data that matches the rows of the new. I tried using vlookup, but it
leaves the formula in the cell that I will later need to type in, and it also
says that vlookup looks for a lookup value in the leftmost column of the
table. Unfortunately the lookup value that I am using is to the right of the
columns with the data I would like to have transferred over and could never
get it to work.

Here is an example.

I am needing column M2-M200 in the backup that matches the rows that are
still listed in the updated original (M2-M200) to transfer the columns A-G
over without leaving behind formulas in the cells of my updated sheet. This
is just a once a day,one time transfer of data and I close the backup when I
am done.
Is there any way to have this data that matches the rows of previous data to
be transferred without me doing it manually?

 

[Patrick Molloy]

so spreadsheet newsheet is the updated sheet, spreadsheet oldsheet is the
backup

compare each item in newsheet column M and if it exists in oldsheet column M
copy what? the entire row ?

sub copydata
dim cell as range
dim rw as long
for each cell in newsheet.Range("M:M").Cells
if not isempty(cell) then
rw = found(cell)
if rw <> 0 then
'copy something fro row rw in oldsheet to the row
cell.row of newsheet
end if
end if
next

end sub
function found(cell as range) as long
on error resume next
found = worksheetfunction.match(cell.value, oldsheet.Range("M:M"),false
on error goto 0
end function

 

[Doug]

Yes, New sheet is updated and the old one is now a backup record.

If it is found in the old backup record in column M then I would like it to
automatically transfer over data for that row from columns A-G.

 

[Patrick Molloy]

we know the row (from the found function) of the old data, rw
and cell gives us the row for where to copy the old data to

so the copy line is

newsheet.cells(cell.row,"A").resize(1,7).Value =
oldsheet.cells(rw,"A").resize(1,7).Value

 

[Doug]

This is what it looks like once I have placed the actual name of the sheets
in place of old sheet and new sheet. I tried it and had a compilation error.
I must be doing something wrong. Very sorry. Is there something wrong with
the format here?

Sub copydata()
Dim cell As Range
Dim rw As Long
For Each cell In Doug 's Research.xlsm.Range("M:M").Cells
If Not IsEmpty(cell) Then
rw = found(cell)
If rw <> 0 Then
Doug 's Research.xlsm.cells(cell.row,"A").resize(1,7).Value =
Prior Screen.xlsm.Cells(rw, "A").Resize(1, 7).Value
cell.row of Doug's Research.xlsm
End If
End If
Next

End Sub
Function found(cell As Range) As Long
On Error Resume Next
found = worksheetfunction.match(cell.value,
Prior Screen.xlsm.Range("M:M"), False
On Error GoTo 0
End Function

 

[Patrick Molloy]

this line

For Each cell In Doug 's Research.xlsm.Range("M:M").Cells


is wrong

For Each cell In workbooks("Doug 's
Research.xlsm").worksheets("???").Range("M:M").Cells

similarly

Doug 's Research.xlsm.cells(cell.row,"A").resize(1,7).Value =
Prior Screen.xlsm.Cells(rw, "A").Resize(1, 7).Value

easiest is
set newSheet = workbooks("Doug 's Research.xlsm").worksheets("???")
set oldsheet = workbooks("Prior Screen.xlsm").worksheets("???")

then your code is easier to read and control
eg
Doug 's Research.xlsm.cells(cell.row,"A").resize(1,7).Value =

Prior Screen.xlsm.Cells(rw, "A").Resize(1, 7).Value

might become
newsheet.cells(cell.row,"A").resize(1,7).Value = oldsheet.cells(rw,
"A").Resize(1, 7).Value

This is what it looks like once I have placed the actual name of the
sheets
in place of old sheet and new sheet. I tried it and had a compilation
error.
I must be doing something wrong. Very sorry. Is there something wrong with
the format here?

Sub copydata()
Dim cell As Range
Dim rw As Long
For Each cell In Doug 's Research.xlsm.Range("M:M").Cells
If Not IsEmpty(cell) Then
rw = found(cell)
If rw <> 0 Then
Doug 's Research.xlsm.cells(cell.row,"A").resize(1,7).Value =
Prior Screen.xlsm.Cells(rw, "A").Resize(1, 7).Value
cell.row of Doug's Research.xlsm
End If
End If
Next

End Sub
Function found(cell As Range) As Long
On Error Resume Next
found = worksheetfunction.match(cell.value,
Prior Screen.xlsm.Range("M:M"), False
On Error GoTo 0
End Function

 

[Doug]

Here is what I have now. It shows an error for:
newsheet.cells(cell.row,"A").resize(1,7).Value =
&
cell.row of newsheet
&
found = worksheetfunction.match(cell.value,

I think we are getting closer?

Sub copydata()
Set NewSheet = Workbooks("Doug's Research.xlsm").Worksheets("Screener")
Set oldsheet = Workbooks("Prior Screen.xlsm").Worksheets("Screener")
Dim cell As Range
Dim rw As Long
For Each cell In NewSheet.Range("M:M").Cells
If Not IsEmpty(cell) Then
rw = found(cell)
If rw <> 0 Then
newsheet.cells(cell.row,"A").resize(1,7).Value =
oldsheet.Cells(rw, "A").Resize(1, 7).Value
cell.row of newsheet
End If
End If
Next

End Sub
Function found(cell As Range) As Long

End Function
On Error Resume Next
found = worksheetfunction.match(cell.value,
oldsheet.Range ("M:M"), False
On Error GoTo 0
End Function

 

[Dave Peterson]

You have a couple of typos because of line wrap in the newsgroup post.

This compiled, but I didn't test it:

Option Explicit
Sub copydata()

Dim myCell As Range
Dim myNewRng As Range
Dim NewSheet As Worksheet
Dim OldSheet As Worksheet
Dim res As Variant

Set NewSheet = Workbooks("Doug's Research.xlsm").Worksheets("Screener")
Set OldSheet = Workbooks("Prior Screen.xlsm").Worksheets("Screener")

With NewSheet
Set myNewRng = .Range("M1", .Cells(.Rows.Count, "M").End(xlUp))

For Each myCell In myNewRng.Cells
If Not IsEmpty(myCell.Value) Then
res = Application.Match(myCell.Value, OldSheet.Range("M:M"), 0)
If IsError(res) Then
'not match, what should happen?
Else
.Cells(myCell.Row, "A").Resize(1, 7).Value _
= OldSheet.Cells(res, "A").Resize(1, 7).Value
End If
End If
Next myCell
End With

End Sub

 

[Doug]

Thanks so much. You guys made my day.