Collating data from multiple spreadsheets

[Legacy 96851]

So I'm pretty much brand new to VBA (I'm learning though, with the help of this forum). I'm trying to write a macro to do the following, but I don't want to overkill it or write a block of code that ends up completely non-functional, so I wanted to see if anyone would comment on my ideas.

If I could get HTML maker to work, I'd post examples, but I can't, so a text description hopefully will do. I have a directory full of spreadsheets in a relatively raw format. The first three columns are all text, the next four are numbers. I need to get all of this data from each sheet, and arrange it in a summary sheet. Unforunately, the first column of each file also contains the labels for categories of data that I want to be separate worksheets. That is to say, the first column might have the entries: People, Jeff, Steve, Tom, Cars, Ford, Volvo, etc., and I want 2 worksheets, one for "People" which lists the data about Jeff, Tom, and Steve, and one for "Cars" which lists the data for Ford and Volvo.
Furthermore, each spreadsheet has specific name like "electrical" or "mechanical." On a single worksheet in my summary, I want to list all the data for Electrical People, then skip a few lines, then all the data for Mechanical People, and on the next sheet, all the data for Electrical Cars, and under that for Mechanical Cars.

Here is my plan for a macro, I haven't done any coding yet because I need to look up a ton of stuff (like how to read from other sheets).

'''''''''''''''
'Macro will probably have to be given spreadsheet's name, store as string
'Start by making 6 ranges; adv program, uas, test, training, other, sw
'Check the name of the spreadsheet to see what type of engineering
'Search through column 1
'   When a category is hit, switch to a while loop
'   Continue moving through
'   Column bound should be constant, set row bound to high constant initially, then shrink by using a counter each time a new row is recorded
'   Make a range from the corresponding... range
'   Stop when another category is hit
'   Repeat this for each category
'At this point, there should be 6 full ranges, (perhaps one or two empty if a category is left out)
'Copy in the proper heading in the summary sheet leaving a blank row beforehand
'   find place to paste either by saved index or "lastcell" check
'Given the proper indices (to be found by way of heading), copy the range into the spreadsheet
'Move through the worksheets, pasting ranges. Only go to a particular worksheet if the range is non-empty
'Move to next spreadsheet (engineering type)
'Repeat

I'm sure that was very confusing and extremely long, so I won't be shocked if I don't get any replies

 

[VoG]

You will appreciate that you are asking a lot here - not just a question about how to do one thing but a question about a complete project (and a pretty complex one at that). You are far more likely to get help if you post 'smaller' questions and build on the solutions to solve your overall problem.

This should get you started with splitting data in different categories into different sheets. It assumes that the categories are in column A and are in blocks - e.g. 10 rows of category A followed by 8 categories of category B and so on. This was originally coded for Cities as categories hence the name of the macro.

Sub CityToSheet()
Dim lastrow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long
Dim ws As Worksheet
Application.ScreenUpdating = False
With Sheets("Master")
    lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
    LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
    iStart = 2
    For i = 2 To lastrow
        If .Range("A" & i).Value <> .Range("A" & i + 1).Value Then
            iEnd = i
            Sheets.Add after:=Sheets(Sheets.Count)
            Set ws = ActiveSheet
            On Error Resume Next
            ws.Name = .Range("A" & 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

 

[Legacy 96851]

I am indeed aware that it's easier for people to help if questions are smaller, but then again, I'm not asking anyone to do my project for me. It's just helpful in the process of learning to have people say "yes, that is a good idea," or "you should think that over a little more," which is really all I'm looking for. The "help" button and google can't really do that for you, so a forum would seem like a good place. Sorry, definitely not trying to sound ungrateful, I appreciate any and all assistance.

That being said, thanks a lot for the code. I'll look it over and perhaps model my own after it.