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?
--
Hope your having a good day!

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" wrote in message
...
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?
--
Hope your having a good day!

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.
--
Have a great day!


"Patrick Molloy" wrote:

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" wrote in message
...
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?
--
Hope your having a good day!

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" wrote in message
...
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.
--
Have a great day!


"Patrick Molloy" wrote:

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" wrote in message
...
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?
--
Hope your having a good day!

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).Valu e =
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" wrote:

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" wrote in message
...
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.
--
Have a great day!


"Patrick Molloy" wrote:

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" wrote in message
...
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?
--
Hope your having a good day!

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").Cel ls

similarly
Doug 's Research.xlsm.cells(cell.row,"A").resize(1,7).Valu e =
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).Valu e =
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


"Doug" wrote in message
...
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).Valu e =
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" wrote:

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" wrote in message
...
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.
--
Have a great day!


"Patrick Molloy" wrote:

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" wrote in message
...
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?
--
Hope your having a good day!

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

--



"Patrick Molloy" wrote:

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").Cel ls

similarly
Doug 's Research.xlsm.cells(cell.row,"A").resize(1,7).Valu e =
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).Valu e =
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


"Doug" wrote in message
...
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).Valu e =
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" wrote:

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" wrote in message
...
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.
--
Have a great day!


"Patrick Molloy" wrote:

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" wrote in message
...
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?
--
Hope your having a good day!

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 wrote:

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

--

"Patrick Molloy" wrote:

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").Cel ls

similarly
Doug 's Research.xlsm.cells(cell.row,"A").resize(1,7).Valu e =
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).Valu e =
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


"Doug" wrote in message
...
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).Valu e =
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" wrote:

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" wrote in message
...
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.
--
Have a great day!


"Patrick Molloy" wrote:

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" wrote in message
...
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?
--
Hope your having a good day!




--

Dave Peterson

Thanks so much. You guys made my day.
--
Have a great day!


"Dave Peterson" wrote:

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 wrote:

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

--

"Patrick Molloy" wrote:

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").Cel ls

similarly
Doug 's Research.xlsm.cells(cell.row,"A").resize(1,7).Valu e =
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).Valu e =
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


"Doug" wrote in message
...
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).Valu e =
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" wrote:

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" wrote in message
...
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.
--
Have a great day!


"Patrick Molloy" wrote:

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" wrote in message
...
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?
--
Hope your having a good day!




--

Dave Peterson