Create Macro with variable search feature
 

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

Create Macro with variable search feature
 

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

Create Macro with variable search feature
 

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

Create Macro with variable search feature
 

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

Create Macro with variable search feature
 

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

Create Macro with variable search feature
 

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

Create Macro with variable search feature
 

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

Create Macro with variable search feature
 

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

Create Macro with variable search feature
 

The code assumes all 3 files are already opened. try opening up the files
before running macro. If that doesn't work then try to step tr\hrough the
code to find problem.

In VBA window, click on any line of the code. Then Press F8. You can step
through the code to find where the hyperlink is coming from.

"punkyfire" wrote:

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

Create Macro with variable search feature
 

Hi Joel. I did run the macro with the 3 files already opened. When I
stepped through the code and pressed F8, I got the message "compile error
variable not defined" in the 2nd row of the code

lastrow = Cells(Rows.Count, ActiveCell.Column). _

What am I missing? I truly appreciate your help.
--
Mary


"Joel" wrote:

The code assumes all 3 files are already opened. try opening up the files
before running macro. If that doesn't work then try to step tr\hrough the
code to find problem.

In VBA window, click on any line of the code. Then Press F8. You can step
through the code to find where the hyperlink is coming from.

"punkyfire" wrote:

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

Create Macro with variable search feature
 

It maybe the line wrapped. The full line look like the code below. Make
sure the continuation character is at the end of each line (_ underscore) and
there are no blank line between the contuation lines. You can also remove
the continuation character a put the entire statement on one line. This
website has problems with long lines of code.

lastrow = Cells(Rows.Count, ActiveCell.Column). _
End(xlUp).Row


"punkyfire" wrote:

Hi Joel. I did run the macro with the 3 files already opened. When I
stepped through the code and pressed F8, I got the message "compile error
variable not defined" in the 2nd row of the code

lastrow = Cells(Rows.Count, ActiveCell.Column). _

What am I missing? I truly appreciate your help.
--
Mary


"Joel" wrote:

The code assumes all 3 files are already opened. try opening up the files
before running macro. If that doesn't work then try to step tr\hrough the
code to find problem.

In VBA window, click on any line of the code. Then Press F8. You can step
through the code to find where the hyperlink is coming from.

"punkyfire" wrote:

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