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!

Sounds like VBA is your most likely solution. I'd move this post to that forum. Also, it will be easier to get your desired outcome if you upload a sample file.

Thanks for the suggestion. How do I go about moving the thread?

Richie

If a moderator can't move it, you can just close this thread and start a new one in VBA. Moderators are listed at the bottom of the home page for General.