Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose and separating worksheet based on Input box name
Hi, this are my codes. It allows me to return a multiple row of the selected "Region" i entered in the InputBox eg, Europe in another worksheet named Region. However i would like them to be separated in different worksheet. for eg, when the user type in the region -Europe in the input box, a new worksheet Europe will be created with the information taken from the rows of all Europe information in raw. I would also like it to be transpose. Is it possible? Thanks! Sub getRegion() Dim fnd As String Dim fndRng As Range Dim eRow As Long Dim ff As String fnd = InputBox("Enter a Region") If fnd < "" Then With Sheets("raw").Cells Set fndRng = .Find(fnd) If Not fndRng Is Nothing Then ff = fndRng.Address Do eRow = Sheets("Region").Cells(Rows.Count, 1). _ End(xlUp).Row + 1 fndRng.EntireRow.Copy Sheets("Region").Cells(eRow, 1) Set fndRng = .FindNext(fndRng) Loop Until ff = fndRng.Address End If End With End If End Sub -- ryll ------------------------------------------------------------------------ ryll's Profile: http://www.excelforum.com/member.php...o&userid=28605 View this thread: http://www.excelforum.com/showthread...hreadid=483057 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose and separating worksheet based on Input box name
Assuming the code you posted copies what you want, then these additions
should get you started Sub getRegion() Dim fnd As String Dim fndRng As Range Dim eRow As Long Dim ff As String Dim sh as Worksheet fnd = InputBox("Enter a Region") If fnd < "" Then set sh = worksheets.Add(after:=worksheets(worksheets.count) ) sh.Name = fnd & "_Data" col = 1 With Sheets("raw").Cells Set fndRng = .Find(fnd) If Not fndRng Is Nothing Then ff = fndRng.Address Do eRow = Sheets("Region").Cells(Rows.Count, 1). _ End(xlUp).Row + 1 fndRng.EntireRow.Copy sh.cells(1,col).PasteSpecial Transpose:=True col = col + 1 Set fndRng = .FindNext(fndRng) Loop Until ff = fndRng.Address End If End With End If End Sub -- Regards, Tom Ogilvy "ryll" wrote in message ... Hi, this are my codes. It allows me to return a multiple row of the selected "Region" i entered in the InputBox eg, Europe in another worksheet named Region. However i would like them to be separated in different worksheet. for eg, when the user type in the region -Europe in the input box, a new worksheet Europe will be created with the information taken from the rows of all Europe information in raw. I would also like it to be transpose. Is it possible? Thanks! Sub getRegion() Dim fnd As String Dim fndRng As Range Dim eRow As Long Dim ff As String fnd = InputBox("Enter a Region") If fnd < "" Then With Sheets("raw").Cells Set fndRng = .Find(fnd) If Not fndRng Is Nothing Then ff = fndRng.Address Do eRow = Sheets("Region").Cells(Rows.Count, 1). _ End(xlUp).Row + 1 fndRng.EntireRow.Copy Sheets("Region").Cells(eRow, 1) Set fndRng = .FindNext(fndRng) Loop Until ff = fndRng.Address End If End With End If End Sub -- ryll ------------------------------------------------------------------------ ryll's Profile: http://www.excelforum.com/member.php...o&userid=28605 View this thread: http://www.excelforum.com/showthread...hreadid=483057 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to input pictures automatically based on cell input? | Excel Worksheet Functions | |||
Extracting Data from another worksheet based on user input | Excel Worksheet Functions | |||
run macro with input msg based on cell input | Excel Discussion (Misc queries) | |||
Separating a List onto multiple worksheets in the same workbook based off 1 criteria | Excel Worksheet Functions | |||
CODE to select range based on User Input or Value of Input Field | Excel Programming |