Repeat a macro

I have a client using Excel 2000. They have a data sheet with over 9000 
lines of information. She basically wants to find, say from column D, every 
cell that contains the word "bearing" and copy that cell to another 
worksheet. She has a macro that does the copying, but what she wants to do 
and I don't know how, is to make it repeat until the job is finished.

Any ideas?

Thanks!
Dino 


0
6/13/2007 6:33:50 PM
excel 39879 articles. 2 followers. Follow

4 Replies
580 Views

Similar Articles

[PageSpeed] 39

First way involves no macro at all.  Click on the top of column D and:

Data > Filter > AutoFilter > Custom... > Contains > bearing

This will hide the cells in Column D without" bearing".  Just copy the 
visible cells and paste eslewhere.
-- 
Gary''s Student - gsnu200729


"Diane K" wrote:

> 
> I have a client using Excel 2000. They have a data sheet with over 9000 
> lines of information. She basically wants to find, say from column D, every 
> cell that contains the word "bearing" and copy that cell to another 
> worksheet. She has a macro that does the copying, but what she wants to do 
> and I don't know how, is to make it repeat until the job is finished.
> 
> Any ideas?
> 
> Thanks!
> Dino 
> 
> 
> 
0
GarysStudent (1572)
6/13/2007 6:49:00 PM
See this page Diane
http://www.rondebruin.nl/copy5.htm

-- 

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Diane K" <dkoers_nospam_@comcast.net> wrote in message news:ujD$clerHHA.1296@TK2MSFTNGP06.phx.gbl...
> 
> I have a client using Excel 2000. They have a data sheet with over 9000 
> lines of information. She basically wants to find, say from column D, every 
> cell that contains the word "bearing" and copy that cell to another 
> worksheet. She has a macro that does the copying, but what she wants to do 
> and I don't know how, is to make it repeat until the job is finished.
> 
> Any ideas?
> 
> Thanks!
> Dino 
> 
>
0
rondebruin (3790)
6/13/2007 6:51:40 PM
Second way is with a macro:

Sub diane()
Set r1 = Intersect(ActiveSheet.UsedRange, Range("D:D"))
Set r2 = Sheets("Sheet2").Range("D1")
i = 0
For Each r In r1
    If InStr(r.Value, "bearing") > 0 Then
        r1.Copy r2.Offset(i, 0)
        i = i + 1
    End If
Next
End Sub

This will search & copy till the job is done.
-- 
Gary''s Student - gsnu200729


"Diane K" wrote:

> 
> I have a client using Excel 2000. They have a data sheet with over 9000 
> lines of information. She basically wants to find, say from column D, every 
> cell that contains the word "bearing" and copy that cell to another 
> worksheet. She has a macro that does the copying, but what she wants to do 
> and I don't know how, is to make it repeat until the job is finished.
> 
> Any ideas?
> 
> Thanks!
> Dino 
> 
> 
> 
0
GarysStudent (1572)
6/13/2007 6:59:07 PM
Thanks everyone! The easiest route by far was using the AutoFilter. Duh....I 
should have thought of it!!!

-- 

"Diane K" <dkoers_nospam_@comcast.net> wrote in message 
news:ujD$clerHHA.1296@TK2MSFTNGP06.phx.gbl...
>
> I have a client using Excel 2000. They have a data sheet with over 9000 
> lines of information. She basically wants to find, say from column D, 
> every cell that contains the word "bearing" and copy that cell to another 
> worksheet. She has a macro that does the copying, but what she wants to do 
> and I don't know how, is to make it repeat until the job is finished.
>
> Any ideas?
>
> Thanks!
> Dino
> 


0
6/13/2007 7:34:13 PM
Reply:

Similar Artilces:

How to copy a workbook so the buttons run the macros?
a.xcl has lots of macros and lots of buttons. If I copy a.xcl as b.xcl and move a.xcl to a different folder and run eg. macro1 from the button in b.xcl I get an error message: a.xcl/macro1 not found. How can I copy a workbook so the buttons will run the macros in that workbook rather than looking for the macros in the original workbook? You obviously know how to assign a macro to a button. Just do that again and select the macro that is in that workbook. HTH Otto "-" <unknown@unknown.com> wrote in message news:MPG.1cedbac140e53b9a989680@msnews.microsoft.com... > ...

Fill in fields with parameters values from macro?!
Hello, I have three letters that I need to send out on a regular basis. I want to have an Office application (Excel) call the word document and fill in the Company name and address. I need to have a Word VBA macro that can receive parameters and then substitute them in a document. I know Excel VBA very well, but not sure how to do this in Word. Question ====== How do you write a Word VBA Macro that can receive parameters and then subtitute fields in a document with the parameter values? For example: (Word VBA) Sub ChangeAddress(byRef CompanyName as string, Address...

Macro to automatically save document with cell reference as filena
I have tried to create a workbook that automatically grabs the filename and saves the document with some problems Both methods appear to work, but Excel always crashes. There should be a way to accomplish this without a crash. Method 1: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim objFolders As Object Set objFolders = CreateObject("WScript.Shell").SpecialFolders ActiveWorkbook.SaveAs objFolders("mydocuments") & "\Workflow Moves\" & Sheets("Sheet1").Range("F1").Value &...

copy paste Macro
I was wondering if it is possible to make a macro do the following. copy selected cell, move to another cell (same row) and copy change worksheets seach a column for the first copied value moe 4 cells from the cell it had found in the search then paste the second value Is it possible?? --- Message posted from http://www.ExcelForum.com/ Olark, Yes. Put this in a regular module: (paste it from here, but watch for line feeds). Dim Data1, Data2 Sub Copy1() Data1 = Selection.Value Application.StatusBar = "Data1: " & Data1 End Sub Sub Copy2() Data2 = Selection.Value Applicat...

how to record macro to roll over monthly data
hi , I need to create /record a macro to roll over a monthly data ,each month the 3 month ago figures must be added to the YTD.The remining data must be then moved one month ahead allowing space to include the new figures Thanks Anna Your description gives no idea of the overall structure and you are unlikely to get an answer better than Tools>Macro>Record new macro an record yourself taking the steps, but this is rarely repeatable each month as data is in different cells, etc. It sound like you may need an application written, but without stating yourself and trying to compl...

Protecting macros
I have a protected sheet with buttons assigned to macros. Howver the buttons are not protected and can be reassigned by a user using the R mouse menu. Any ideas to help would be gratefully received when you protect the sheet, ensure edit objects is not selected "Alec Mclintock" <Alec Mclintock@discussions.microsoft.com> wrote in message news:9D22B5E4-21A2-4477-9762-645C0A2DD504@microsoft.com... > I have a protected sheet with buttons assigned to macros. Howver the > buttons > are not protected and can be reassigned by a user using the R mouse menu. ...

excel 4.0 macro remover tool
Dear all, i am looking for excel 4.0 macro removal tool from excel 97 file.Actually we are facing such problem from two months and it gives pain.some excel files contain macro of excel 4.0 with "very hidden and very protective" attribute so that we cannot remove it.i have searched on net and found that there are some tools called macro remover which can remove all types of macros from excel file including excel 4.0 macro from excel 97 file,but it charges which i cannot afford.so if there any tool that can remove particular excel 4.0 macro from excel 97 file please let me know...

How to undo macro and check if xl is working or hanged? #2
Dear All: How to undo macro and check if xl is working or hanged? I have run som FOR loop that looks like these: For i = 1 to 127 For j = 1 to 168 (some autofiltering here based on i and j criteria) Next j Next i However, once i run it, the excel seems to be not responding. So, will the excel back to respond? and normally what is the acceptabl time for this kind of job? Thank -- changeabl ----------------------------------------------------------------------- changeable's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1571 View this thread: http://www.excelfo...

Macros and deleting a column
I've got a "contents" sheet which has numerous buttons which take users to a cell on a separate worksheet. I'd like to delete some of the columns in the worksheet as they are no longer needed. When I do this however the macro doesn't amend to the new cell reference. There's a lot of coulmns to delete and a lot of macros that won't work. Any ideas gratefully received. Hi you may post the relevant part of your macro -- Regards Frank Kabel Frankfurt, Germany "Blackcat" <Blackcat@discussions.microsoft.com> schrieb im Newsbeitrag news:F757F808...

Macro security message pop up
Is there any way I can have the security message not pop up that asks t disable macros? This usually comes up if the security level is high. also have the excel cells password protected, but not sure if tha impacts the warning or not. Thanks -- sgods ----------------------------------------------------------------------- sgodse's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1457 View this thread: http://www.excelforum.com/showthread.php?threadid=27253 Hi if you set the security level to 'Low' you won't get these warning message. BUT I wouldn'...

Recorded macros not working using the shortcut keys
Hello, I have recorded two macros that work fine except they do not run using the shortcut keys I defined. I've stepped through the macros and the shortcut keys are in the scripts, but I can only run them by pulling up the list of macros and selecting the macro and clicking run. I'm using Office 2007 in XP. TIA, Robert When you pull up the list of Macros and select your macro, go to the options box and create a shortcut key. On Fri, 13 Feb 2009 06:11:40 -0800 (PST), CurlyDave <davesexcel@gmail.com> wrote: >When you pull up the list of Macros and select your macro, go to...

Not Repeating An Entry
I have 13 columns of information and column A is Order Number. What is the best way to not enter the same order number twice, short of doing a Find prior to entering it? I was trying to do a conditional format that if it equalled and other cell in column A, turn the cell background Red. Thanking you in advance your help. John You could use Format|Conditional formatting to highlight the duplicates: (with a custom formula of) =countif(a:a,a1)>1 Or you could use Data|Validation and a custom formula of: =COUNTIF($A$1:A1,A1)<2 (with A1 the active cell) John13 wrote: > > I have ...

Toggle Font Styles Macro
I'm trying to write a Macro that toggles through background color and font styles. No one's been able to help me out from my previous posts, so I'm still searching. I need a function that returns the current selection's font style (red, italic, bold, times roman, etc.) so that I can store it in a variable before my toggle begins and so that I can restore the style at the end of my toggle. Can anyone please help me out with this? Here's my code: ' Toggles the background color ' Keyboard Shortcut: Ctrl+Shift+O Sub BackgroundToggle() If Selection.Interior.ColorInde...

Macro help?
Hi all, I am looking for some code that will allow me to enter the current time into a cell by using a shortcut key...(Ctrl+T for example). Or possibly by using a button. I've never used a button before but I'm sure I can figure it out. Any insight? Thanks in advance. ctrl+shift+semi-colon for time ctrl+semi-colon for date -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "Ken" <kpaul1@prexar.com> wrote in message news:0ab601c34bdf$ccd5a6d0$a001280a@phx.gbl... > Hi all, > > I am looking for some code that will allow me to enter the ...

Macro help #10
Hi! I have a table with customers that have made multiple purchases of different products. I would like to have a macro that creates a purchase number based on the number of times they have purchased individual items. For example, the first time they purchase Product A a 1 would go in this new field, the second time they purchase ProductA a 2 would go in this field. I understand this would work through a date loop but I am lost as to how to accomplish this. I am not really familiar with writing macro's! Another key thing is that in order for it to be a purchase it must not have been a sam...

Close Adobe Acrobat Opened by Macro
Hello, I have an excel workbook and in it is a macro that takes one of the sheets and creates a pdf file from it. Once the pdf is created, it is displayed on screen in the Adobe Acrobat program. Is there a way to close the Adobe Acrobat program within the macro so when it is finished I'm back at the worksheet I started in? (the one I ran the macro to create the pdf in the first place) Any help would be greatly appreciated. Thanks!!!! Michelle ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly fr...

Excel Macro Question about Conditional Formatting
I have a cell that is colored according to a conditional formatting statement. I want to copy the color to another cell using a macro but I can't find the property. In particular, Range.Interior.ColorIndex is NOT set. I can examine the colors using Range.FormatConditions(index) ..Interior.ColorIndex but I can't tell what value to use for "index" (a conditional format has up to 3 conditions and thus index is a number between 1 and 3 but I don't know which one is currently active). -- Message posted via http://www.officekb.com hi, you are coping the conditional format t...

Macro Help #19
I'm sure this will be a Macro: I have several sheets with a workbook that contain part numbers. If a part number = X, then place an employee number in the next column directly beside the part number column. NOTE: Part numbers vary from column to column throughout the work book. Thanks for your help!! Have a look in VBA help index for FINDNEXT. There is a good example you can modify. -- Don Guillett SalesAid Software donaldb@281.com "DNA" <dnoel@fsgbank.com> wrote in message news:54a55c91224f8f183cb297aa2bef0723@localhost.talkaboutsoftware.com... > I'm sure...

Word 2007 Macros
Hello! I am trying to learn about programming Word 2007 macros. (Not recording, actually programming.) I have some experience with VB.NET, but not VBA. Does anyone know of a good resource to help me get started? Thanks! Kaiti Perhaps I should explain the task at hand: I have a Perl script that dumps data from a number of database tables into formatted .pm files. What I need to do, is to take the data from these files, format it (mostly in tables) and add it to a .docx file created from a template I have. I am hoping to use a macro in Word to do this. As I have no experien...

enter data in cell then run macro automatically
Hi, Is it possible that I can enter data into a cell and then the pressing of the enter key will cause a macro to run? Rick Yes. That type of macro is run from Event code. For more on events see Chip Pearson's site at http://www.cpearson.com/excel/events.htm Or David McRitchie's site at http://www.mvps.org/dmcritchie/excel/event.htm Gord Dibben Excel MVP On Tue, 25 Jan 2005 21:04:42 -0000, "R D S" <RDS@members.v21.co.uk> wrote: >Hi, > >Is it possible that I can enter data into a cell and then the pressing of >the enter key will cause a macro to...

Solution to repeat downloading of e-mail
PROBLEM: Each day I open Outlook 2003. Messages are downloaded appropiately. I delete, move, etc. as required. >>When I close Outlook and reopen (same day same session), messages are >>downloaded all over again. How can I reconfigure to download new messages once, only. >>I leave messages on the servers (comcast, gmail, yahoo, etc.) for 3 >>days, in order to download on a Peek e-mail device. >>I do not have AVG set to check e-mail downloads nor to I have >>Spysweeper perform a check for e-mail downloads. I upgraded to Office 2007, running Win7: s...

Fixing a macro
I've had a macro in place, that permitted me to print a group of columns. Over a period of time, comments were added to my spread sheet (the little red triangles in the corner) Since then the macro will not work. I've tried to remove all the macros. Maybe I haven't because I can't get the macro to work. Is a way that I can copy the macro and the error message for you to see so it may be repaired. As you can probably tell, I don't know how to make a macro. Thanks, Tim fm CT If you want to remove the code, make a...

entering cell text from a macro
What is the format for entering text in a cell from a macro? Range("A1").Value="HERMAN" <hjg57@bellsouth.net> wrote in message news:nIGHg.2893$C6.134@bignews1.bellsouth.net... > What is the format for entering text in a cell from a macro? > > ...

Writing a Macro
I am creating a form in Microsoft Word 2003. There is an area that requests a physical company information, including; Address: City, State, Zip: Phone: Fax: Primary Contact: Email: I would like to make it so that when a box labeled 'same as physical address' is checked that the information that has been entered will autofill to corresponding fields labeled Billing Information, with the following fields: Address: City, State, Zip: Phone: Fax: Primary Contact: Email: Can anyone help me with this? See the Repeating Data page of Greg Maxey's website ...

Macro
I have 7 identical files which I routinely save to another drive and remove the links. I've recorded a macro that will move the files, but I have to edit it each time to adjust the name of the file. Can the macro be adjusted to move the active file instead of specifying the name of the file? Here's the macro as I currently have it recorded: (This is MPA Attorney, but I would save MPA Agency next, then MPA Internal, etc.. ActiveWorkbook.SaveAs Filename:= "I:\EXLDATA\Loan Recovery MIS\2004_03\Outstandings\MPA Attorney.xls", FileFormat:=xlNormal, Password:=...