|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!
|
|
#1
May 6th, 2008, 09:40 AM
|
|||
|
|||
|
Help Splitting Data
I was wondering if anyone can help.
I currently have report i run and export into excel. This data show alot of information. What i am wanting to do is automate a time consuming job and beeing a bit of a noob to vba struggling a bit. Currently from this information is sort by a colum (eg Product number) which of course groups these together. Then i have to manually create a new worksheet for each product number (rename the sheeet as the product number) and copy/paste information across. Is there anyway of having a macro to automatically split the data for me into individual worksheets (and rename them) Any help will be appreciated
|
|
#2
May 6th, 2008, 12:35 PM
|
||||
|
||||
|
Sure. One thing you can do to start with is record a macro and do the steps manually then clean it up as reusable code after.
As for someone volunteering to write this for you from scratch I don't see that happening but its a fairly basic routine to write.
__________________
medialint.com "Energy has the opportunity to change the climate if it's done right." - Sen. John Ensign, R-Nev. (quoted out of context)
|
|
#3
May 6th, 2008, 04:07 PM
|
|||
|
|||
|
Quote:
Thank you for a response, i have tried that but the problem is as the data is use is not the same amounts ie one day there could be 1 of an order number and the next 50. I unsure of how to do it, so that would be the reason im seeking some help or at least a push in the right direction. Thank you
|
|
#4
May 6th, 2008, 10:08 PM
|
||||
|
||||
|
What you're going to want to do is to make sure your data is sorted right and find the range to copy, then copy the range to a new worksheet. I'm afraid I don't have any practical examples on hand of this I can share but I'm sure there's some out there. There's probably a dozen ways you can implement to find the data range. A simple loop would be the easiest to implement but there's other ways using excel's built in functions.
The way I normally handle data splitting like this is to pipe it all into access then export queries back to excel which may seem like a lot of trouble but it has a lot of advantages depending on how much data you're dealing with and your ultimate goal.
|
|
#5
May 7th, 2008, 03:10 AM
|
|||
|
|||
|
Thank you,
to be honest if i hadn't sorted this i was going to ditch excel and move it all across to access. Just some colleagues who use the spreadsheet once i have updated are quite as comfortable with using Access compared with Excel. Thank you
|
|
#6
May 7th, 2008, 06:01 AM
|
|||
|
|||
|
Hi, thank you for those who showed some interest in helping but i managed to crack it in the end.
Here is code just incase someone else is looking for the same. This code is taking into the fact that Headers are in Row 1 Thank you Code:
Sub Test()
Dim Sh As Worksheet
Dim Rng As Range
Dim c As Range
Dim List As New Collection
Dim Item As Variant
Dim ShNew As Worksheet
Application.ScreenUpdating = False
' *** Change Sheet name to suit ***
Set Sh = Worksheets("Transactions")
Set Rng = Sh.Range("B2:B" & Sh.Range("B65536").End(xlUp).Row)
On Error Resume Next
For Each c In Rng
List.Add c.Value, CStr(c.Value)
Next c
On Error GoTo 0
Set Rng = Sh.Range("B1:L" & Sh.Range("B65536").End(xlUp).Row)
For Each Item In List
Set ShNew = Worksheets.Add
ShNew.Name = Item
Rng.AutoFilter Field:=1, Criteria1:=Item
Rng.SpecialCells(xlCellTypeVisible).Copy ShNew.Range("A2")
Rng.AutoFilter
Next Item
Sh.Activate
Application.ScreenUpdating = True
End Sub
|
|
| Viewing: Dev Shed Forums > Programming Languages - More > Visual Basic Programming > Help Splitting Data |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
