breaking down sheet data into blocks (vba)...

[RobbieC]

Hi there, I have a worksheet which contains imported data. The data is essentially in 18 blocks of differing widths and heights, but essentially have the same 4 categories: GROUP, HEADING, UNIT & TYPE

After these headings follow the DATA. So column A looks a bit like:

GROUP
HEADING
UNIT
TYPE
DATA

GROUP
HEADING
UNIT
TYPE
DATA
DATA
DATA

GROUP
HEADING
UNIT
TYPE
DATA
DATA etc...

I'm trying to come up with a way to take each 'block' from GROUP to the "SPACE before the next GROUP" and copy the entire set of rows to a specified sheet. Obviously, the data will be different every time a new file is imported, so there are no set amount of rows per GROUP.

I don't really know where to start with this, but if you could point me in the right direction, I'd be very happy

Thanks

 

[Fluff]

You could use find, coupled with CurrentRegion & then copy to wherever.
I'll happily create a macro for you, but I'll need the following info.
1) What is the name of the sheet containing the data?
2) Is it always in column A, starting in A1?
3) What is the sheet name to copy to & where in that sheet should it go?
4) Should the data remain "as is", or do you want it in separate columns (eg Col A=Group, Col B=Heading etc)?

 

[RobbieC]

Thanks Fluff, that sounds awesome mate.

The Sheet containing the data is DATA_FULL

The heading titles GROUP, HEADING, UNIT, TYPE & DATA are all in column A starting in A1

There are 18 of the 'group blocks' so I was gonna make a sheet for each - data_1 data_2 data_3.... data_18

The data in each block needs to stay the same

I have made the framework for the workbook which I shall attach

There is a single module at the moment which imports the .txt file into the sheet

If you can show me your code to copy the first block, I think I should be able to replicate that for the rest...

edit.... I couldn't make an attachment for some reason... I've posted the workbook to my webserver: http://www.bluecustard.co.uk/Book1.xlsm

Cheers for your help Fluff

 

[Fluff]

Decided on a different approach, which will do all blocks.
If you have already created your new sheets then remove the line in red

Sub MoveData()
' RobbieC

    Dim Cnt As Long
    Dim Cl As Range
    
Application.ScreenUpdating = False

    Set Cl = Sheets("DATA_FULL").Range("A1")
    For Cnt = 1 To 18
        [COLOR=#ff0000]Sheets.Add(after:=Sheets("DATA_FULL")).Name = "Data_" & Cnt[/COLOR]
        Cl.CurrentRegion.Copy Sheets("Data_" & Cnt).Range("A1")
        Set Cl = Cl.End(xlDown).Offset(2)
    Next Cnt
    
End Sub

 

[Fluff]

Having seen your sheet, the above wont work.

 

[RobbieC]

yeah, I just tried it and it came up with an error. At least you can now see the full extent and layout of the data on sheet1.

As you can see, they form blocks in rows GROUP to GROUP. Each one of those needs to copy to its own individual page

Cheers mate

 

[Fluff]

Try this instead

Sub MoveData()
' RobbieC

    Dim Cnt As Long
    Dim Cl As Range
    
Application.ScreenUpdating = False

    For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
        Cl = Trim(Cl)
    Next Cl
    
    Set Cl = Sheets("DATA_FULL").Range("A1")
    For Cnt = 1 To 18
'        Sheets.Add(after:=Sheets("DATA_FULL")).Name = "Data_" & Cnt
        Cl.CurrentRegion.Copy Sheets("Data_" & Cnt).Range("A1")
        Set Cl = Cl.End(xlDown).Offset(2)
    Next Cnt
    
End Sub

 

[RobbieC]

Perfect Fluff! Thanks very much!

This has saved me so much time - I thought it would be far more complicated code than it actually turned out

Thanks again mate

 

[Fluff]

Glad to help & thanks for the feedback