L
Legacy 96851
Guest
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).
I'm sure that was very confusing and extremely long, so I won't be shocked if I don't get any replies
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).
Code:
'''''''''''''''
'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