Automation/batching of month-based worksheets

[AndrewWarhol]

I have a worksheet X that I want to replicate over the course of a number of months, for at least two separate people. As in, I want to do something like this:

 (WORKSHEET X) http://s1124.photobucket.com/user/Vernancular/media/help.png.html     (WORKSHEET X)

Worksheet A represents an attendance sheet and I want to fill as many relevant rows for easy user usage. Right now, the green highlighted cells are fine (can simply be left as is) but rows A, B, C and E need to be altered.

A&B need to be altered based on user and C&E need to be altered based on month.

Since there are very few people for this to be replicated for, I will be creating a different Feb file for each user (i.e 1 Rachel, 1 Vanessa) as this will take care of A&B, and then running the same month-based script for each person to alter columns C&E.

Something like this: http://i1124.photobucket.com/albums/l573/Vernancular/Methods.png

This is not as easy as first appears, as there are filters applied to the worksheet, making autofill date impossible. As such I have setup a separate worksheet for months March-December illustrating the number of dates for each month. Let's call it sheet Y, it looks like this:

(WORKSHEET Y) http://s1124.photobucket.com/user/Vernancular/media/how.xlsx.png.html    (WORKSHEET Y)

I will be doing the following: 

1. batch the transfer of the relevant column from sheet Y to columns C&E of sheet X, and then 

2. Delete all items in row 32 (Entry for the 31st day of the month) for month files with only 30 days

3. Save the file with the corresponding name (i.e March column -> Rachel ATTENDANCE Mar 2016.xlsx, April column -> Rachel ATTENDANCE Apr 2016.xlsx, and so on)

4. Cycle the script until ended at Rachel ATTENDANCE Dec 2016.xlsx

How can this be done within excel? What macros should I be using? What examples can I refer to?

[AndrewWarhol]

What autoit and excel tools should I be using?* 

 

I can't seem to edit the main post. Sorry for the double post.

[junkew]

you should write this completely in vba macro. No reason to use AutoIT for handling that.

you need filesystemobject to iterate thru your folder with xls files

you need workbooks.open to read thru each specific workbook and workbook("name").saveas ...

you need range object or cells(iRow,iCol).value to read data out of a cell

[water]

AndrewWarhol,

welcome to AutoIt and the forum!
You need to have at least 5 or 10 posts to be able to edit your OP.
To get a list of files I suggest function _FileListToArrayRec.
If you want to do it with AutoIt you could use Excel UDF that comes with AutoIt.