Please Help!

[JamesMantle]

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

 

[Ed]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[Jason Hanson]

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.

 

[TomHinkle]

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]

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

 

[Ed]

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" <[email protected]>
wrote in message
news:[email protected]...