Automating a creation of a formula for each row of info

[saitoa]

I am new to the world of VBA so please forgive me if this is very simple.

I am currently importing information from another sheet into the active one so I can manupulate the data using the following:

Sub CopyTemplate()

Dim lastRow As Long
lastRow = Range("A65536").End(xlUp).Row
If lastRow > 11 Then
Range("A11:AA" & lastRow).Clear
Else
MsgBox "There is no information to clear", vbInformation, "ERROR"
End If
Windows("order_temp.xls").Activate
Range("A2").Activate
Set startcell = ActiveCell
Set lastcell = ActiveCell.End(xlDown)
Range(startcell, lastcell.Offset(0, 15)).Copy
Windows("order_sheet.xls").Activate
Range("A10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


End Sub

For each entry in the D column that is imported, I want the VBA to also create another entry in the AA column using a formula. So if the data in the D column is "ABC" I want the data in the AA Column to be "ABC Corp"

Is there a way to do this? Also, can someone tell me:

1. How to select everything EXCEPT rows 1-10? I'm currently clearing the sheet before importing using

Range("A11:AA" & lastRow).Clear

but there has to be a better way

2. Is there a way to import information from an Excel sheet that is NOT open in Excel? Since the file being read in is a template, I should not want to have to open it every time in order to copy over the information.

Thank you VERY much!

 

[hatemachine]

Did you ever figure this out b/c i need help with it as well. I need to import from another spreadsheet and replace one of the cells with an equation.