Importing and manipulating data between workbooks automatically

I am trying to get an automatic sync running between some vendors and am having difficulties due to what data I can extract from company A vs. what data I need to import to company B. I would love to be able to have an automatic process run nightly to effectively do the following:
- Open the extracted file
- Manipulate the data into a new workbook
- Save the new workbook in readiness for upload via scheduled task.

Some examples of the things I need to adjust between documents:
- One field in the source document to be mapped to two fields in the destination document (Or have that one field automatically duplicated in the destination)
- Some additional information added to a field - Basically just adding the same string to each cell in a column. (A1&"@WorkEmail.com")
- An IF statement to determine if a field is empty, and if so, input another field in its place.

I have been successful in simply writing IF statements between the source file and a new spreadsheet, however I became stuck when trying to "fill down" a formula on the new spreadsheet. I couldn't figure out how to tell the new document how many lines of information were in the source file.

I assume there will need to be some kind of macro/VBA magic, although I will certainly need some hand holding with this.

Thanks for providing the forum!

BUMP

So far I have managed to work out some of the manipulation piece from within the worksheet such as adding columns and copying information. I am still in need of assistance getting the automatic processes of opening and saving the adjusted file to work.