Big an complicated project

Little_Ghost

New Member
Joined
Dec 13, 2010
Messages
48
Hi eveybody,

I'm working on a big and complicated project. This type of project will return every so many months and I don't want to have to do this whole thing by hande every time. let me go into a little more detail:

I get a txt file supplied full of data (I think it's outputted from a database). I can get the data into excel in 2 neat columns but then the the problems starts.

The first column will be a code ranging from AA-0000 to ZZ-ZZZZ and everything in between (currently it's 104,085 entrys so it's spread out over 2 worksheets).
In the first column some blank lines, lines with '---' and lines with a header as well but those are irrelevant.
The second column will be just be some description.

How it eventually needs to be is that every row starting with an 'A' in column A has to go on a new sheet titled 'A'. Then every row starting with 'B' should be placed on a sheet titled 'B' etc etc. In the end you would have 26 sheets; one for every letter.

I tried so many options I don't even remember what I tried (or what not for that matter :S) and I'm getting realy frustrated with this whole project. I'm at my wits end on how to proceed (or start over) so all help is MORE then welcome!

Hope one of you people know what I could do or can help me out.

Kind regards,

- Little_Ghost
 
Last edited:

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Thanks hiker95.

It does part of what I'm looking for except for two things:
1. it puts the first line on every sheet.
2. it makes a new sheet for every item, this is not wanted :)
now I end up with 104,085 sheets (tested it with a smaller file tho).

The string in column A is unique om every line so it will put:
AC-00C1
AC-00C0
AC-00BF
on 2 different sheet:
AC-00C0
AC-00BF
It does not make a sheet for AC-00C1 but instead treats it like some kind of header and put it on every sheet. In stead it should make a sheet called 'A' with all three items on it.

What it would need to do this (I think) is something (formula, VBA) that checks the first letter of the contents of each cell column A and put that letter in the cell next to it so that it has a reference column so to say (I hope I'm still making sense). How to do this other then by hand, I'm not sure.

I don't have a clue at how to solve the first problem tho...

- Little_Ghost
 
Last edited:
Upvote 0
Okay... I could use
Code:
=left(a1,1)
in column C to get what I need but is it possible to put that into the the macro as well so that it would first do that and then use column C as a reference?

That would sort out the second problem and would leave me with the problem that row 1 is on every sheet but does not have a sheet of its own.

- LG
 
Upvote 0
Still not solved.
I have 2 macro's:
Code:
Sub First_Letter()
[INDENT]Dim wsAll Worksheet
Dim LastRow As Long

Set wsAll= Worksheets("Sheet2")

LastRow = wsSelect.Range("A" & Rows.Count).End(xlUp).Row
 
wsAll.Range("C1:C" & LastRowIs).FormulaLocal = "=LEFT(A1;1)"
 
[/INDENT]End Sub

and

Code:
Sub Seperate_Sheets()
Dim wsAll As Worksheet
Dim LastRow As Long
Dim wsCrit As Worksheet
Dim wsNew As Worksheet
Dim LastRowCrit As Long
Dim I As Long


Set wsAll = Worksheets("Sheet2")

LastRow = wsAll.Range("A" & Rows.Count).End(xlUp).Row

Set wsCrit = Worksheets.Add
      
wsAll.Range("C1:C" & LastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsCrit.Range("A1"), Unique:=True
    
    LastRowCrit = wsCrit.Range("A" & Rows.Count).End(xlUp).Row
    For I = 2 To LastRowCrit
    
        Set wsNew = Worksheets.Add
        wsNew.Name = wsCrit.Range("A2")
        wsAll.Rows("1:" & LastRow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsCrit.Range("A1:A2"), _
         CopyToRange:=wsNew.Range("A1"), Unique:=False
        wsCrit.Rows(2).Delete
        
    Next I
    
    Application.DisplayAlerts = False
    wsCrit.Delete
    Application.DisplayAlerts = True


End Sub

Trying to combine these through
Code:
Sub DoBoth()

   First_Letter
   Seperate_Sheets

End Sub
doesn't work.

Can someone help me out with this? I've got the feeling I'm really close to solving it but I'm just overlooking the last bits and/or pieces.

- LG
 
Upvote 0
Dear all,

I just got back to the project (have been busy with other stuff) and figured out that it doesn't work, and I think I know why.

The value that is generated by first macro is displayed correctly BUT,
in the formulabar it will display the formula and not the value.

So when the second macro wants to run it will not find a value in the column it is looking at.

I guess it somehow needs to be written as a value instead of as a formula for the second macro to work, but how?!

What I noticed with the second macro (when I run it separately after if I fill in the value myself) is that the first line is on every sheet it creates. Is there a way to change that? (I couldn't find any)

Sorry for being such a pain :(

Kind Regards,

- Little_Ghost
 
Upvote 0
Little_Ghost,

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net, and provide us with a link to your workbook.
 
Upvote 0
Did you try Excel Explosion 3.0?

http://datapigtechnologies.com/freeware.htm

"This Utility will split your Excel dataset into separate Excel workbooks or separate tabs based on criteria you define! So you can create a separate workbook for each Employee in your dataset or each City, or State...etc. All this with one click!"
 
Upvote 0
Did you try Excel Explosion 3.0?

http://datapigtechnologies.com/freeware.htm

"This Utility will split your Excel dataset into separate Excel workbooks or separate tabs based on criteria you define! So you can create a separate workbook for each Employee in your dataset or each City, or State...etc. All this with one click!"

it will have to be excel only and needs to run on every computer without installing any other 3rd party application.

- Little_Ghost
 
Upvote 0

Forum statistics

Threads
1,224,586
Messages
6,179,730
Members
452,939
Latest member
WCrawford

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top