copy rows to another workbook

Hi,
   I'm stuck with this problem: I would speed my daily work if I could 
automatically copy some rows of my current worksheet (a table with 20 
columns and 100 rows) in another workbook.

I thought to create a VBA macro that
1 - triggers when a button is pressed
2 - ask user for the name and location of the new workbook (a dialog box)?
3 - create such workbook
4 - copy there the rows with a "S" on the first cell of every row (column A)
5 - automatically reset the column A to blank cells.

Any hints or link to some VBA code to look at?

Thanks

Monica
0
12/16/2005 8:00:34 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
219 Views

Similar Articles

[PageSpeed] 4

How about this:

Record a macro when you copy the worksheet (Edit|move or copy sheet)
to a new workbook.

Then apply data|filter|autofilter to that new worksheet in the new workbook.

Filter column A to show the rows "not equal to" S in column A.

Delete those visible rows.

Reset the filter.

Clean up column A.

Save the new workbook as a new name.

I bet your code would be very close to what you need.  

If you have trouble tweaking the code, post back with your questions.



sortilege wrote:
> 
> Hi,
>    I'm stuck with this problem: I would speed my daily work if I could
> automatically copy some rows of my current worksheet (a table with 20
> columns and 100 rows) in another workbook.
> 
> I thought to create a VBA macro that
> 1 - triggers when a button is pressed
> 2 - ask user for the name and location of the new workbook (a dialog box)?
> 3 - create such workbook
> 4 - copy there the rows with a "S" on the first cell of every row (column A)
> 5 - automatically reset the column A to blank cells.
> 
> Any hints or link to some VBA code to look at?
> 
> Thanks
> 
> Monica

-- 

Dave Peterson
0
petersod (12005)
12/16/2005 3:41:35 PM
Reply:

Similar Artilces:

Row Not Found at the Subscriber
http://sanssql.blogspot.com/2010/05/row-not-found-at-subscriber-replication.html Regards, Sandesh Segu http://sanssql.blogspot.com/ ...

Automatcally Hiding Rows in Excel 2000 #2
Is there a way of automatically hiding rows in Excel 2000 based on a rule (ie if there is no data in a row, we want to hide that row without doing it manually)? We have a couple of ideas which involve VB code which reacts to data within that row but if anyone has done this before or knows of a way to do it, that would be great. Thanks in advance! Tom Starr http://groups.google.com/groups?threadm=3F625B17.4BF93224%40msn.com Was a reply you got to your first post. Tom Starr wrote: > > Is there a way of automatically hiding rows in Excel 2000 based on a rule > (ie if there is no...

Messages not delivered to another Routing Group HELP NEEDED
I have three routing groups ( exchange in the 3 of them are Exch 2k sp3 ). Mail from one server ( in my headquarter routing group ) to another ( in one branch office routing group ) is getting stucked in MESSAGES WITH UNREACHABLE DESTINATION and not routed to that server. Connectors seems to be working just fine. After add my other exchange server from my headqurter group to the connector to be albe to send mail it shows the queue now in the connector but does not sends anything. Otherwise, mail from my branch office to my headquarter correctly sends mail. Any help would be gre...

conditional formatting of row based on cell
I want to apply conditional formatting to a whole row based on the contents of one cell in that row. For example, if cell L2 contains the word 'sport', i want the whole of row 2 to be shaded. I'm guessing this is pretty simple, but seem to be having a bit of a mental blockage. Any suggestions would be appreciated. Thanks :) formula is =$l$2="sport" "Katherine" <katherine@katherine.com> wrote in message news:107b01c372ef$4ec59e30$a101280a@phx.gbl... > > I want to apply conditional formatting to a whole row > based on the contents of one...

Selecting rows from various sheets #4
Cheers Fran -- sha ----------------------------------------------------------------------- shav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1198 View this thread: http://www.excelforum.com/showthread.php?threadid=26665 ...

How to add another user's Contacts folder to my Address Book?
Background: Exchange 2003 Server w/ Outlook 2000/2003 clients. I can add any Contacts folders in my mailbox to my Address Book by going into the Properties of the folder and checking "Show this folder as an e-mail Address Book" on the "Outlook Address Book" tab. However, if I have another user's mailbox (to which I have full permission) in my Folder List, I do not have this option in any of their Contacts folders. How do I add another user's Contacts folder, displayed in my Folder List, to my Address Book? TIA, roshan After a bit of research on SlipStick.com...

Updating 20k+ rows from Excel to access. vbscript? VBA? SQL?
Any suggestions? Some of these rows only need a few columns updated, and others need all of them updated. If possible if say row 800 column 'S' is filled in the access DB but is blank on the excel spreadsheet, if possible i like to keep the existing data in access and over right it with a blank cell. The spreadsheet is a export of the access DB, changes were made/merged from other spreadsheets so the Unique ID is the same and in column A. A script suggestions to complete this? Thanks :) dim dbe dim db dim ssql set dbe = createobj("dao.dbengine.36") set db = dbe.openda...

Cannot enable Blind copy option (BCC)
Have this option on my computer at work- use it all of the time. I have outlook 2002 and no matter what I do I cannot see the BCC button- it is not in the view section- it is not in the options section- it is not anywhere. It is almost annoying enough to make me switch to eudora. Why make it so i can't find or use it? Please help When in Outlook XP (2002) I open a new mail message... In the menu of that new message click VIEW There is an option to check "BCC Field" - check it. Enjoy, Terry "kentg@earthlink.net" <anonymous@discussions.microsoft.com>...

limking workbooks
I have 4 workbooks in seperate folders that I need to combine into one monthly summary. The workbooks are different but each contain a columns named, B/L,Amount,Billed. These are the 3 columns I want merged into one spreadsheet. What would be the best way to do this? Thanks KJH copy the column(s)(or cells) that you want to summarize and paste special>paste links to the destination column(s) in your summary worksheet.each time there is a change in your workbooks the summary sheet updates too.Once you have the cells on your sheets you can organise them how you like,ie you can have...

Inserting rows slow on large report
I have a large report I maintain with 3 data tabs and 2 report tabs, 1 for top 10 customers, about 400 rows, and another for all other customers, about 4000 rows. Every month I have to update it for any new customer/product combinations. I have optimized the formulas to the best of my abilities and gotten a full recalc time down from 3 min to 30 sec, which is acceptable. My biggest problem now is that every time I need to add a row (or delete one for that matter) it takes about 15 seconds (on the 4k row report, adding to the top 10 customers report takes 3-5 seconds), per row....

Linked cells in Multiple Workbooks
I am tracking dates and have linked cells from another workbook so as t not have to duplicate the data entry of dates. The dates are linked t projects that are listed in rows and there is information in m workbook that is additional information within the row. So if someon changes the order of the other workbook, the information that is in m workbook does not get sorted with the order. Is there a way to link m cells to the linked cells? ie. Project ID Start Date Milestone EndDate Person Assigned 1234 (linked) 11/15/04 11/20/04(lin...

Copying Data From SQL Into Excel
When I copy a range of data from SQL and paste it into Excel, the data doesn't appear to be available for formulas - in this case a VLOOKUP formula. However, once I click in the formula bar (as if to edit the data) then hit "return" the data is "magically" available for the VLOOKUP formula. It seems to me to be a format problem, but changing the format of the data doesn't help. Could this be related to similar issues when copying data from Access into Excel? Are there any workarounds? It sounds similar to the Access problem. Instead of pasting, you can ...

Insert Row Under current row (with formulas/formatting)
I've got a sheet that gets appended to regularly but have to do a lot of fiddling to make sure the formatting is correct, calculated fields get added and chart series ranges collect all the data every time I add a new row. The insert (row) command seems to take care of all of this but it inserts the empty row above the current row. As I'm always appending data to the sheet, I would prefer this to be below the current row. I've tried adding a dummy row under all the data containing the formulas and formatting I need but unfortunately I get formula errors and it screws up my chart....

How to refresh all Cells in an Excel Workbook automatically?
I have an Excel Workbook that is Shared and it uses the Vlookup function to look up values in another Excel Workbook. Both files are stored in a Network drive and I use \\server_name\shared_folder\filename.xls format to reference the file in the first Workbook. Problem: Upon opening the Shared Workbook, it does not always display the correct Cells values until I manually press F2 and Enter on each of the wrong-value Cell. Is there a way to refresh all the Cells' value without manually pressing F2 and Enter on the affected Cells? Maybe Edit|Links|Update values Or even selecting all...

How to open workbook without hardcoding path
Hi Could anyone tell me how to open a workbook from another workbook, where both files are in the same folder, without hardcoding the path. I have tried code like this without success. Workbooks.open (path & "\somefile.xls") Thanks in advance Mick Excel automatically looks in the current folder, so :- Workbooks.open "somefile.xls ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ...

copy and match data from one worksheet into another via script?
Hi all I have two worksheets 2 columns each The first sheet has ID and Name Column for example: Code ------------------- ID[/B] NAM 193948 Michael 684588 John 535279 Luke 098734 Matthe ------------------- my 2nd sheet Code ------------------- ID [B]NAM Michael John Luke Matthe ------------------- How do i use the first sheet to match up the names on the 2nd sheet and ultimately copy the ID numbers onto the 2nd sheet? This is just a simplified example. There are thousands of entries. Thank you if you have ...

One link breaks every time I open a workbook...
I have 3 workbooks tied together with links. One is a source only, the other two push and pull data. One of the sources, Payroll Master, on opening updates all links fine to the source only workbook but not to the third workbook (which is in the parent directory). Every time I open it it prompts me to show it where the Payroll Entry is. Doesn't matter if I hit cancel and do Edit Links, I can change it there, but will still lose it upon closing (the link appears in the box and looks ok, just won't update...) Try going Edit->Links. Break the link and re-establish it? That m...

copy and paste into excel
I am trying to set up a sheet into which i can copy and paste various items into a group of cells? and upon being pasted into the group the item will be automatically shrunk to fit the size of the cells it was pasted into, rather than expanding them to fits its size. Any ideas anyone?????? Hi, try copy data and paste them like: Edit/Paste Special/Values. It does not change the size of the columns. Marian Hi, What about 'format cells' 'Alignment' and check "shrink to fit"? That is under Text Control - is that any use??? Cheers, Mark copying+pasting into cell...

2 existing spreadsheets show 1st line rather than header row to sort ...
(XL2003) This is odd, I haven't seen this ever before. I was working on spreadsheets yesterday for a mail merge. Two had headers rows. I cleaned up both documents, put print area and then tried to sort. In both cases, what came up in the sort was the first row below the header where one always sees the actual titles of the header row when one selects the Header Row radio button under "My List Has" in the "Sort" box. I fiddled and fiddled a bit and managed to get one of the workbooks to display the sort properly by the header row titles - without figuring out what ...

Restoring another PST?
Hi everyone, I use the MS Outlook Backup tool, and it backs up daily. Early this week I imported the backed-up PST file ( over 1GB ) into my new PC and after a bit of fiddling to show the folders it did open up all the ones I wanted, yet seemed to be an old backup - maybe 12 weeks out of date. I had to use the machine so kept it like this, and have of course received many mails since then. I see another copy of the backup[ PST file and the date says it was created on Feb 19th, which would be right. My ( evantual! ) question is: how do I, in idiot-proof steps, go about restoring this other...

Copying sorksheet formatting
How do I copy worksheet formatting, inc. custom headers, footers, column & row formatting across all the worksheets in a workbook? Hi one way: formating them all at the same time: - group the sheets (hold down the SHIFT key while selecting the sheets) - apply the format to a cell, column, row -- Regards Frank Kabel Frankfurt, Germany driverdriver wrote: > How do I copy worksheet formatting, inc. custom headers, > footers, column & row formatting across all the worksheets > in a workbook? You can also select the entire sheet with CTRL+A and do Edit / Copy, then group yo...

Copy and Paste question
I have a macro that copies and pastes a large amount of data. After it has run I get the question: " There is a large amount of information on the clipboard. Do you want to [keep it]?" Could someone please tell me how I can avoid this question being asked as I never wish to keep the data? Many thanks Insert this line after the pasting is done, it will clear the clipboard: Application.CutCopyMode = False hth knut egil "Richard" <rgarwell@jaguar.invalid> skrev i melding news:bpi1ee$cf71@eccws12.dearborn.ford.com... > I have a macro that copies and pastes a l...

how to deal with duplicate data rows
I need to interface with a program that generates a .csv file with row of data. It generates 2 or more rows for each instance uniquely identified by the docket/page combination Example: last first bank trustee docket page smith john ny bank (blank field) 12235 8907 (blank field) (blank field) cal western 12235 8907 smith janet ny bank (blank field) 12235 8907 I need to consolidate all three lines into one row by concating field that contain different data (first name column) and columns with blan spaces to look like the ...

more than 65, 500 rows
Is there a product that allows more than 65k rows? ...

Workbook there but not visible! Help!
I was working on some VBA code for an excel application I am writing. I switched between the VBE and the spreadsheet and all of a sudden my workbook is no longer visible! In the VBE project explorer window it shows my project and my code but in the excel window...no workbook! Nothing! Ran a test procedure calling IsAddin to see if somehow I accidentally clicked a button/box telling Excel to make this file an addin but it returned false. I did close the file and re-open it. Any ideas? Maybe it's just off the visible screen: Window|Arrange|tiled (and resize manually) or maybe y...