New to the forum, have a basic knowledge of VBA. The task I am trying to perform with VBA is to

1. Copy from sheet1 A1 to sheet2 E9
2. Copy from sheet1 B1 to sheet2 E11
3. Save sheet2 in a new workbook file with the file name cell data from sheet1 A1 (511894.xls)
4. Copy from sheet1 A2 to sheet2 E9
5. Copy from sheet1 B2 to sheet2 E11
6. Save sheet2 in a new workbook file with the file name cell data from sheet1 A2 (097219.xls)
7. Repeat the process down columns A and B to the end of the columns.
8. Columns A and B will end at the same time but the data and the last cell with change with each scan

This code with accomplish this task with a single column of data but not two columns. Also column B will have a mixture of numbers and letters in its cells, Column A is only numbers.

Here are some images of the spreadsheets

Sheet1.jpgSheet2.jpg

Sub MoveData()
sName = "temp"
Const csPath As String = "C:\Documents and Settings\mcgaulc\Desktop\TestFile\"
'MyName = ActiveWorkbook.Name
Dim wsSource As Excel.Worksheet
'Allows for dynamic file naming as data changes in col A temp sheet
Set wsSource = ThisWorkbook.Worksheets("Sheet1")
Sheets("Sheet1").Select
'find the last row with data in the spreadsheet
FinalRow = Range("A999").End(xlUp).Row
For i = 1 To FinalRow
Sheets("Sheet1").Select
'Copy the SN from temp sheet in a loop thru col A
Range("A" & i).Copy
'Select the temp sheet to paste data into
Sheets("temp").Select
Range("E9").Select
ActiveSheet.Paste
ThisWorkbook.Sheets("temp").Copy
'Save the new document(Copy of) with a Sequential file name
'& ".xls" allows to save copy of workbook as .xls file extention
ActiveWorkbook.SaveCopyAs FileName:=csPath & wsSource.Cells(i, 1).Value & ".xls"
'Close this new word document
ActiveWorkbook.Close False
Next i
Sheets("temp").Select
'Range("B1").Select
'Seletion.ClearContents
End Sub

Thanks in advance for any response or ideas.