moving data to seperate tabs based on one condition

[dontgetit]

Hi Guys,

I have a huge data base with info in several columns. Each row of data has one variable that is the same - the province. So I have thousands of rows and columns of data. I want to put all the data for the provice AB on to a tab, for province BC onto a tab and so on.

I cannot change the downloaded info so I have to work with this. The province is in column T and there is data in columns and rows all down the spreadsheet. So basically instead of sorting, then cutting and pasting into seperate tabs, I would like each tab to automatically pull in the data for each province. Hope I am making sense.

Thanks

 

[VoG]

See if this helps http://www.mrexcel.com/forum/showthread.php?t=396069

 

[dontgetit]

Thanks, but I don't really know a lot about VBA. Is there a line formula that will work on each spreadsheet to bring the data in. Maybe an IF and a Vlookup? I am much more into formulas.

 

[VoG]

That would require an awful lot of formulas. Did you look at Excel Explosion in the link that I posted?

 

[dontgetit]

Yes I did, but we aren't allowed to download programs either

 

[VoG]

Press ALT + F11 to open the Visual Basic Editor, select Module from the Insert menu, and paste into the white space on the right

Sub Lapta()
Dim lastrow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long
Dim ws As Worksheet
Application.ScreenUpdating = False
With ActiveSheet
    lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
    LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
    .Range(.Cells(2, 1), Cells(lastrow, LastCol)).Sort Key1:=Range("T2"), Order1:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    iStart = 2
    For i = 2 To lastrow
        If .Range("T" & i).Value <> .Range("T" & i + 1).Value Then
            iEnd = i
            Sheets.Add after:=Sheets(Sheets.Count)
            Set ws = ActiveSheet
            On Error Resume Next
            ws.Name = .Range("T" & iStart).Value
            On Error GoTo 0
            ws.Range(Cells(1, 1), Cells(1, LastCol)).Value = .Range(.Cells(1, 1), .Cells(1, LastCol)).Value
            .Range(.Cells(iStart, 1), .Cells(iEnd, LastCol)).Copy Destination:=ws.Range("A2")
            iStart = iEnd + 1
        End If
    Next i
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Press ALT + Q to close the code window, press ALT + F8, click on Lapta then click the Run button.

 

[dontgetit]

Wholly crap ! That is amazing. So it looks like what the VBA is doing is simply pulling anything that changes and puts it on it's own tab. Is this correct?

Now we download new data everyday, so how will this work. Can we reuse the same workbook? I'm not really sure how that will work.

 

[VoG]

If it is basically the the same data updated each day then you would need to delete the 'child' sheets before running the macro again.

Otherwise, you could download to new workbooks and put the code in your personal.xls workbook so that it is available in any workbook. See http://www.rondebruin.nl/personal.htm

 

[dontgetit]

Great. Your awesome !!

Can you explain to me what the code is doing - in point form. I have to explain this to someone else. I have to send this to someone else to use. So if I understand this, the person I am sending this too should make it a personal workbook. Is the a way to put a macro button for this person to push just to make it work at the push of the button icon?

 

[VoG]

Basically it does this:

• sorts the whole sheet based on column T
• starts at row 2 and scans down column T looking for a change in value
• adds a new sheet
• copies the block of data and pastes it into the new sheet

• continues scanning down column T from where it left off
• keeps going to the end of the data

You could add a button from the Forms toolbar and when prompted assign the macro Lapta.