How can I automate this series of actions in excel?

I just had one idea. There are two main problems here:
1) How to get the thing to identify successive 12-line sections of cells. Maybe if I use the cut function instead of the copy function it will remove the top 12 lines…then every time, it’s the (new) top 12 that it needs to cut?
2) How to give them successive names? Maybe I tell it to save them as, say, fluther.xlsx, but then there’s already a file called that…so if I tell it not to replace it will end up saving fluther.xlsx(1), fluther.xlsx(2), etc.
Both of these are inelegant, but might work. As you can tell, I’m a newbie at automation. Any better ideas?

Create a column in the file that calculates the file name. You could copy the 12th line out to a new spreadsheet, or alternatively you could have a spreadsheet that points to a line (using relative position) according to a variable on the new sheet. That way you can cycle through the original and export the new one with the appropriate name.

Absolutely anything you can do in Excel can be automated in VBA.

I have a VBA macro which creates a new worksheet based on one column, I’ll mess with it to create new files instead.

As @6rant6 suggests, this will depend on a column with the file name. When the column value changes, a new file is created. Having the trigger in the original data makes it a lot easier to test & tweak later.

Anybody have a quick and easy place to post VBA code? Rather than Fluther I’d rather post the VBA to a VBA-centric site.

I agree with others, you can definitely do this with VBA. The way I started doing VBA was to record a macro of the basic functionality I was trying to create and then look at the code. All you have to do from there is a little research online to find out how to input variables where you need them and how to do the functionality that isn’t quite picked up with the macros.

[Quick tip] you’ll probably use the “offset” function quite a bit to manipulate the cell location

@kfingerman I will be able to post some code later this evening.

Thanks for all the help people, the things I’m suspecting will be toughest are:

1) Getting it to recognize every 12 lines as a separate file – it won’t always be the same 12, just successive ones, so i can’t ask it to do the same set of actions over and over.

2) Actions that take place not in the spreadsheet but instead in a dialogue box (e.g. save as). I need to give each file a separate name (numerical would be fine), and I need to save them as .txt files.

If you think it through, it’s probably not that difficult. You just need to find markers in the file. Let’s say for example, that the word “Elephant” in the first column begins the sequence of twelve. So you turn on macro capture, find “elephant, do 12 down arrows, do whatever you need to capture all the columns, export this (supplying the name). Now if you run this macro again, you will start at the bottom of the first set, skip down to the next elephant, etc.

In the macro, you can replace the literal name you used with a prompt. I’m not certain, but I think you should be able to use a variable (from a named range) to set the file name.