Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please Help!
Dear Users, If anyone can help with this programming issue I'd be hugely grateful I am on work experience and have been asked if it is possible to do th following in Excel: Using a pre-existing worksheet of approx. 1500 rows of data, each wit a 2 or 3 letter "shortname" (e.g. NDB) in column one, is it possible t extract required rows of data into a new worksheet, merely by writin the "shortname" into column 1 of the new worksheet. Each month there will be the need for a few of these new worksheets an it would be infinitely less time-consuming if I were able to use som sort of macro to aid me in this process. For example: Row 1: NDB 23444 223444 112445 223555 22234 558890 To move all the data into a new worksheet without searching an highlighting each row could I just type in the shortname (e.g. NDB into the new worksheet and the programme automatically transfer all th data across? I look forward to hearing from anyone that thinks they may be able t help! Thank you very much, Jame -- JamesMantl ----------------------------------------------------------------------- JamesMantle's Profile: http://www.excelforum.com/member.php...fo&userid=2663 View this thread: http://www.excelforum.com/showthread.php?threadid=39904 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please Help!
Hi, James. I'm not a big expert by any means, but I've stumbled my way
through a few projects. One question that immediately comes to my mind is: where is the "master" file located? and associated question: who is going to do this? If the "master" file and all the created "child" files will be resident on your computer only, and only you are going to run the macro to extract data and populate the new workbooks, that's one scenario. If the master is on a network and you are creating these on your machine, you may have some issues to deal with. If the master is on a network and anyone may need the ability to run a macro and do this, it can get a bit hairy. (Well, for me, any way. There are guys here who do this without losing any sleep!" Reading your scenario, it kind of sounds like you're wanting to run this from a blank workbook template that would reach out and grab data from the master. My approach to this would probably be to run a macro from the master file, or from your Personal.xls with the master file open (assuming you are the only user to create the new workbooks). When the short name is typed into an input box, the list is filtered, the filtered data copied, a new workbook created, the data pasted, and the workbook saved. HTH Ed "JamesMantle" wrote in message ... Dear Users, If anyone can help with this programming issue I'd be hugely grateful! I am on work experience and have been asked if it is possible to do the following in Excel: Using a pre-existing worksheet of approx. 1500 rows of data, each with a 2 or 3 letter "shortname" (e.g. NDB) in column one, is it possible to extract required rows of data into a new worksheet, merely by writing the "shortname" into column 1 of the new worksheet. Each month there will be the need for a few of these new worksheets and it would be infinitely less time-consuming if I were able to use some sort of macro to aid me in this process. For example: Row 1: NDB 23444 223444 112445 223555 22234 558890 To move all the data into a new worksheet without searching and highlighting each row could I just type in the shortname (e.g. NDB) into the new worksheet and the programme automatically transfer all the data across? I look forward to hearing from anyone that thinks they may be able to help! Thank you very much, James -- JamesMantle ------------------------------------------------------------------------ JamesMantle's Profile: http://www.excelforum.com/member.php...o&userid=26632 View this thread: http://www.excelforum.com/showthread...hreadid=399046 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please Help!
Have you though of sorting all the data by Column 1?
You can then either use a filter or just copy the data to a new sheet. "JamesMantle" wrote: Dear Users, If anyone can help with this programming issue I'd be hugely grateful! I am on work experience and have been asked if it is possible to do the following in Excel: Using a pre-existing worksheet of approx. 1500 rows of data, each with a 2 or 3 letter "shortname" (e.g. NDB) in column one, is it possible to extract required rows of data into a new worksheet, merely by writing the "shortname" into column 1 of the new worksheet. Each month there will be the need for a few of these new worksheets and it would be infinitely less time-consuming if I were able to use some sort of macro to aid me in this process. For example: Row 1: NDB 23444 223444 112445 223555 22234 558890 To move all the data into a new worksheet without searching and highlighting each row could I just type in the shortname (e.g. NDB) into the new worksheet and the programme automatically transfer all the data across? I look forward to hearing from anyone that thinks they may be able to help! Thank you very much, James -- JamesMantle ------------------------------------------------------------------------ JamesMantle's Profile: http://www.excelforum.com/member.php...o&userid=26632 View this thread: http://www.excelforum.com/showthread...hreadid=399046 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please Help!
Make a pivot table.
Make short name the only row field... make the data part a count of shortname (or whatever. In a pivot table, if you double click ANY data point (ie in the middle) it will automatically create a new worksheet and place the entire set of records that made up that data point on a new worksheet. "JamesMantle" wrote: Dear Users, If anyone can help with this programming issue I'd be hugely grateful! I am on work experience and have been asked if it is possible to do the following in Excel: Using a pre-existing worksheet of approx. 1500 rows of data, each with a 2 or 3 letter "shortname" (e.g. NDB) in column one, is it possible to extract required rows of data into a new worksheet, merely by writing the "shortname" into column 1 of the new worksheet. Each month there will be the need for a few of these new worksheets and it would be infinitely less time-consuming if I were able to use some sort of macro to aid me in this process. For example: Row 1: NDB 23444 223444 112445 223555 22234 558890 To move all the data into a new worksheet without searching and highlighting each row could I just type in the shortname (e.g. NDB) into the new worksheet and the programme automatically transfer all the data across? I look forward to hearing from anyone that thinks they may be able to help! Thank you very much, James -- JamesMantle ------------------------------------------------------------------------ JamesMantle's Profile: http://www.excelforum.com/member.php...o&userid=26632 View this thread: http://www.excelforum.com/showthread...hreadid=399046 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please Help!
Ed, Jason, Tom - many thanks. Having a problem with the pivot table approach - that option is greyed out in the Data window whenever I select anything - any way I could rectify that? Ed, if I took your approach, in setting up a macro with the worksheet open (presuming I'm only going to run the macro to extract data on my computer), where is the "input box" I can type the shortnames into? Any chance you could explain the last paragraph of what you wrote as I'm none too wiser at the minute. Many thanks guys. James -- JamesMantle ------------------------------------------------------------------------ JamesMantle's Profile: http://www.excelforum.com/member.php...o&userid=26632 View this thread: http://www.excelforum.com/showthread...hreadid=399046 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please Help!
James:
Here is a macro I use to sort a worksheet based on an InputBox. I have added code to copy the filtered range, open a new workbook and paste. Change "Sheet1" to your sheet name. Change the SaveAs "yourFilePath" and "YourFileName". It is a mixture of a couple of existing macros, so I won't be surprised if there's a speed bump or two. Sub SortMe() Dim MyTarget As String Dim wb1 As Workbook Dim wb3 As Workbook Set wb1 = ActiveWorkbook ' Clear previous sort Sheets("Sheet1").AutoFilterMode = False With Cells .EntireColumn.Hidden = False .EntireRow.Hidden = False End With Range("A1").Select ' Choose series MyTarget = Application.InputBox("Which series?") If MyTarget = "" Then GoTo Bye Application.ScreenUpdating = False ' Filter for vehicle Selection.AutoFilter ' This uses Field 3, which is Column C. Adjust to suit. Selection.AutoFilter Field:=3, Criteria1:= MyTarget ' Copy used range wb1.Sheets("Sheet1").UsedRange.Copy ' Turn off alerts Application.DisplayAlerts = False ' Creates a new workbook Set wb3 = Workbooks.Add ' Ensures 3 sheets in new book Do While wb3.Worksheets.Count < 3 wb3.Worksheets.Add Loop ' Copies the current file and pastes into the new workbook wb3.Sheets("Sheet2").Range("a1").PasteSpecial xlPasteValues wb3.SaveAs Filename:= "C:\YourFilePath\YourFileName.xls" ' Reset worksheet ' Unhide everything wb1.Activate Sheets("Sheet1").Activate Sheets("Sheet1").AutoFilterMode = False With Cells .EntireColumn.Hidden = False .EntireRow.Hidden = False End With Application.CutCopyMode = False Range("A1").Select Application.ScreenUpdating = True Application.DisplayAlerts = True MsgBox "I'm done!" Bye: End Sub "JamesMantle" wrote in message ... Ed, Jason, Tom - many thanks. Having a problem with the pivot table approach - that option is greyed out in the Data window whenever I select anything - any way I could rectify that? Ed, if I took your approach, in setting up a macro with the worksheet open (presuming I'm only going to run the macro to extract data on my computer), where is the "input box" I can type the shortnames into? Any chance you could explain the last paragraph of what you wrote as I'm none too wiser at the minute. Many thanks guys. James -- JamesMantle ------------------------------------------------------------------------ JamesMantle's Profile: http://www.excelforum.com/member.php...o&userid=26632 View this thread: http://www.excelforum.com/showthread...hreadid=399046 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|