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)

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

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying


Jun 19, 2002
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
Upvote 0

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.
Upvote 0

Forum statistics

Latest member

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
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 "".
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