I have 3 excel documents.
1 has single column with 20 rows containing unique numbers.
2nd has 8 columns with 936 rows
3rd is blank

I want to write a macro that will copy an individual number in the first
document, search for it in the 2nd document, copy the entire row holding that
number from the 2nd and paste it into the 3rd blank document and cursor down
one row. I then want it to go back to the 1st document cursor down one row
and end.

When I try to write the macro, it records the first number from the first
document within the macro, so it won't automatically search for the document
within the row I am in.

I think I'm almost there and I'm probably missing something pretty obvious.
Any help would be appreciated.
--
Mary

Post the macro. It will be easy to modify the macro.

"punkyfire" wrote:

I have 3 excel documents.
1 has single column with 20 rows containing unique numbers.
2nd has 8 columns with 936 rows
3rd is blank

I want to write a macro that will copy an individual number in the first
document, search for it in the 2nd document, copy the entire row holding that
number from the 2nd and paste it into the 3rd blank document and cursor down
one row. I then want it to go back to the 1st document cursor down one row
and end.

When I try to write the macro, it records the first number from the first
document within the macro, so it won't automatically search for the document
within the row I am in.

I think I'm almost there and I'm probably missing something pretty obvious.
Any help would be appreciated.
--
Mary

Here's the macro

Sub Cull()
'
' Cull Macro
' Macro recorded 6/14/2007 by
'
' Keyboard Shortcut: Ctrl+k
'
Selection.Copy
Windows("C02_ORIG_LOST_52307.xls").Activate
Cells.Find(What:="5010045", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, -6).Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("Culled_ICNs.xls").Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1").Select
Windows("C02_Extracts.xls").Activate
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub



***********

I'm more familiar with word macros than excel. How do I record keystrokes,
rather than Cells, or is that even possible?
--
Mary


"Joel" wrote:

Post the macro. It will be easy to modify the macro.

"punkyfire" wrote:

I have 3 excel documents.
1 has single column with 20 rows containing unique numbers.
2nd has 8 columns with 936 rows
3rd is blank

I want to write a macro that will copy an individual number in the first
document, search for it in the 2nd document, copy the entire row holding that
number from the 2nd and paste it into the 3rd blank document and cursor down
one row. I then want it to go back to the 1st document cursor down one row
and end.

When I try to write the macro, it records the first number from the first
document within the macro, so it won't automatically search for the document
within the row I am in.

I think I'm almost there and I'm probably missing something pretty obvious.
Any help would be appreciated.
--
Mary

Hi Joel. Here is the macro

Sub Cull()
'
' Cull Macro
' Macro recorded 6/14/2007 by
'
' Keyboard Shortcut: Ctrl+k
'
Selection.Copy
Windows("C02_ORIG_LOST_52307.xls").Activate
Cells.Find(What:="5010045", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, -6).Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("Culled_ICNs.xls").Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1").Select
Windows("C02_Extracts.xls").Activate
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

--
Mary


"punkyfire" wrote:

I have 3 excel documents.
1 has single column with 20 rows containing unique numbers.
2nd has 8 columns with 936 rows
3rd is blank

I want to write a macro that will copy an individual number in the first
document, search for it in the 2nd document, copy the entire row holding that
number from the 2nd and paste it into the 3rd blank document and cursor down
one row. I then want it to go back to the 1st document cursor down one row
and end.

When I try to write the macro, it records the first number from the first
document within the macro, so it won't automatically search for the document
within the row I am in.

I think I'm almost there and I'm probably missing something pretty obvious.
Any help would be appreciated.
--
Mary

Sub Cull()
'
' Cull Macro
' Macro recorded 6/14/2007 by
'
' Keyboard Shortcut: Ctrl+k
'

Windows("C02_Extracts.xls").Activate
lastrow = Cells(Rows.Count, ActiveCell.Column). _
End(xlUp).Row
Set NumberRange = Range(ActiveCell, _
Cells(lastrow, ActiveCell.Column))

For Each cell In NumberRange

Windows("C02_ORIG_LOST_52307.xls").Activate
Set c = Cells.Find(What:=cell, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If Not c Is Nothing Then

c.EntireRow.Copy

destlastrow = Windows("Culled_ICNs.xls"). _
ActiveSheet. _
Cells(Rows.Count, "A").End(xlUp).Row

Windows("Culled_ICNs.xls").Activate
Rows(CStr(destlastrow) & ":" & _
CStr(destlastrow)).Select
ActiveSheet.Paste
End If
Next cell
End Sub


"punkyfire" wrote:

Hi Joel. Here is the macro

Sub Cull()
'
' Cull Macro
' Macro recorded 6/14/2007 by
'
' Keyboard Shortcut: Ctrl+k
'
Selection.Copy
Windows("C02_ORIG_LOST_52307.xls").Activate
Cells.Find(What:="5010045", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, -6).Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("Culled_ICNs.xls").Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1").Select
Windows("C02_Extracts.xls").Activate
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

--
Mary


"punkyfire" wrote:

I have 3 excel documents.
1 has single column with 20 rows containing unique numbers.
2nd has 8 columns with 936 rows
3rd is blank

I want to write a macro that will copy an individual number in the first
document, search for it in the 2nd document, copy the entire row holding that
number from the 2nd and paste it into the 3rd blank document and cursor down
one row. I then want it to go back to the 1st document cursor down one row
and end.

When I try to write the macro, it records the first number from the first
document within the macro, so it won't automatically search for the document
within the row I am in.

I think I'm almost there and I'm probably missing something pretty obvious.
Any help would be appreciated.
--
Mary

Hi Joel. I hope this works! I'm mary's husband and need this macro to work
for me. Thanks for being awsome!!
Jerry
--
Mary


"Joel" wrote:

Sub Cull()
'
' Cull Macro
' Macro recorded 6/14/2007 by
'
' Keyboard Shortcut: Ctrl+k
'

Windows("C02_Extracts.xls").Activate
lastrow = Cells(Rows.Count, ActiveCell.Column). _
End(xlUp).Row
Set NumberRange = Range(ActiveCell, _
Cells(lastrow, ActiveCell.Column))

For Each cell In NumberRange

Windows("C02_ORIG_LOST_52307.xls").Activate
Set c = Cells.Find(What:=cell, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If Not c Is Nothing Then

c.EntireRow.Copy

destlastrow = Windows("Culled_ICNs.xls"). _
ActiveSheet. _
Cells(Rows.Count, "A").End(xlUp).Row

Windows("Culled_ICNs.xls").Activate
Rows(CStr(destlastrow) & ":" & _
CStr(destlastrow)).Select
ActiveSheet.Paste
End If
Next cell
End Sub


"punkyfire" wrote:

Hi Joel. Here is the macro

Sub Cull()
'
' Cull Macro
' Macro recorded 6/14/2007 by
'
' Keyboard Shortcut: Ctrl+k
'
Selection.Copy
Windows("C02_ORIG_LOST_52307.xls").Activate
Cells.Find(What:="5010045", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, -6).Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("Culled_ICNs.xls").Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1").Select
Windows("C02_Extracts.xls").Activate
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

--
Mary


"punkyfire" wrote:

I have 3 excel documents.
1 has single column with 20 rows containing unique numbers.
2nd has 8 columns with 936 rows
3rd is blank

I want to write a macro that will copy an individual number in the first
document, search for it in the 2nd document, copy the entire row holding that
number from the 2nd and paste it into the 3rd blank document and cursor down
one row. I then want it to go back to the 1st document cursor down one row
and end.

When I try to write the macro, it records the first number from the first
document within the macro, so it won't automatically search for the document
within the row I am in.

I think I'm almost there and I'm probably missing something pretty obvious.
Any help would be appreciated.
--
Mary

It need one small change. I forgot to add one to the row where the data gets
pasted. Everything will pasted on the same line. try this change

from:
Windows("Culled_ICNs.xls").Activate
Rows(CStr(destlastrow) & ":" & _
CStr(destlastrow)).Select
to:
Windows("Culled_ICNs.xls").Activate
Rows(CStr(destlastrow + 1) & ":" & _
CStr(destlastrow + 1)).Select

"punkyfire" wrote:

Hi Joel. I hope this works! I'm mary's husband and need this macro to work
for me. Thanks for being awsome!!
Jerry
--
Mary


"Joel" wrote:

Sub Cull()
'
' Cull Macro
' Macro recorded 6/14/2007 by
'
' Keyboard Shortcut: Ctrl+k
'

Windows("C02_Extracts.xls").Activate
lastrow = Cells(Rows.Count, ActiveCell.Column). _
End(xlUp).Row
Set NumberRange = Range(ActiveCell, _
Cells(lastrow, ActiveCell.Column))

For Each cell In NumberRange

Windows("C02_ORIG_LOST_52307.xls").Activate
Set c = Cells.Find(What:=cell, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If Not c Is Nothing Then

c.EntireRow.Copy

destlastrow = Windows("Culled_ICNs.xls"). _
ActiveSheet. _
Cells(Rows.Count, "A").End(xlUp).Row

Windows("Culled_ICNs.xls").Activate
Rows(CStr(destlastrow) & ":" & _
CStr(destlastrow)).Select
ActiveSheet.Paste
End If
Next cell
End Sub


"punkyfire" wrote:

Hi Joel. Here is the macro

Sub Cull()
'
' Cull Macro
' Macro recorded 6/14/2007 by
'
' Keyboard Shortcut: Ctrl+k
'
Selection.Copy
Windows("C02_ORIG_LOST_52307.xls").Activate
Cells.Find(What:="5010045", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, -6).Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("Culled_ICNs.xls").Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1").Select
Windows("C02_Extracts.xls").Activate
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

--
Mary


"punkyfire" wrote:

I have 3 excel documents.
1 has single column with 20 rows containing unique numbers.
2nd has 8 columns with 936 rows
3rd is blank

I want to write a macro that will copy an individual number in the first
document, search for it in the 2nd document, copy the entire row holding that
number from the 2nd and paste it into the 3rd blank document and cursor down
one row. I then want it to go back to the 1st document cursor down one row
and end.

When I try to write the macro, it records the first number from the first
document within the macro, so it won't automatically search for the document
within the row I am in.

I think I'm almost there and I'm probably missing something pretty obvious.
Any help would be appreciated.
--
Mary

Hi Joel. Thank you so much for your help. I'm still having problems and
I'm very new to Excel Macros.

I cut and pasted your macro into the vb macro editor and saved it. Then,
when I ran it, it brought up an "insert hyperlink" screen and nothing else.
Do I have to delete spaces in the vb editor? Is there anything in the code
you gave me that is an instruction for me that should not be in the saved
"cull" macro?

Thanks for your assistance. I look forward to hearing from you.
--
Mary


"Joel" wrote:

It need one small change. I forgot to add one to the row where the data gets
pasted. Everything will pasted on the same line. try this change

from:
Windows("Culled_ICNs.xls").Activate
Rows(CStr(destlastrow) & ":" & _
CStr(destlastrow)).Select
to:
Windows("Culled_ICNs.xls").Activate
Rows(CStr(destlastrow + 1) & ":" & _
CStr(destlastrow + 1)).Select

"punkyfire" wrote:

Hi Joel. I hope this works! I'm mary's husband and need this macro to work
for me. Thanks for being awsome!!
Jerry
--
Mary


"Joel" wrote:

Sub Cull()
'
' Cull Macro
' Macro recorded 6/14/2007 by
'
' Keyboard Shortcut: Ctrl+k
'

Windows("C02_Extracts.xls").Activate
lastrow = Cells(Rows.Count, ActiveCell.Column). _
End(xlUp).Row
Set NumberRange = Range(ActiveCell, _
Cells(lastrow, ActiveCell.Column))

For Each cell In NumberRange

Windows("C02_ORIG_LOST_52307.xls").Activate
Set c = Cells.Find(What:=cell, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If Not c Is Nothing Then

c.EntireRow.Copy

destlastrow = Windows("Culled_ICNs.xls"). _
ActiveSheet. _
Cells(Rows.Count, "A").End(xlUp).Row

Windows("Culled_ICNs.xls").Activate
Rows(CStr(destlastrow) & ":" & _
CStr(destlastrow)).Select
ActiveSheet.Paste
End If
Next cell
End Sub


"punkyfire" wrote:

Hi Joel. Here is the macro

Sub Cull()
'
' Cull Macro
' Macro recorded 6/14/2007 by
'
' Keyboard Shortcut: Ctrl+k
'
Selection.Copy
Windows("C02_ORIG_LOST_52307.xls").Activate
Cells.Find(What:="5010045", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, -6).Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("Culled_ICNs.xls").Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1").Select
Windows("C02_Extracts.xls").Activate
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

--
Mary


"punkyfire" wrote:

I have 3 excel documents.
1 has single column with 20 rows containing unique numbers.
2nd has 8 columns with 936 rows
3rd is blank

I want to write a macro that will copy an individual number in the first
document, search for it in the 2nd document, copy the entire row holding that
number from the 2nd and paste it into the 3rd blank document and cursor down
one row. I then want it to go back to the 1st document cursor down one row
and end.

When I try to write the macro, it records the first number from the first
document within the macro, so it won't automatically search for the document
within the row I am in.

I think I'm almost there and I'm probably missing something pretty obvious.
Any help would be appreciated.
--
Mary