This is my macro here: 

Sub SplitSheets() 
' 
' split Macro 
' Macro recorded 15/11/2007 by Administrator 
' 

'Code goes here instead of 
Application.DisplayAlerts = False 
Dim LMainSheet As String 
Dim LRow As Integer 
Dim LContinue As Boolean 

Dim LColAMaster As String 
Dim LColATest As String 

'Retrieve name of sheet that contains the data 
LMainSheet = ActiveSheet.Name 

'Initialize variables 
LContinue = True 
LRow = 2 

'Start comparing with cell A2 
LColAMaster = "A2" 

'Loop through all column A values until a blank cell is found 
While LContinue = True 

LRow = LRow + 1 
LColATest = "A" & CStr(LRow) 

'Found a blank cell, do not continue 
If Len(Range(LColATest).Value) = 0 Then 
LContinue = False 
End If 

'Found occurrence that did not match, copy data to new sheet 
If Range(LColAMaster).Value <> Range(LColATest).Value Then 

'Copy headings 
Range("A1:Z1").Select 
Selection.Copy 

'Add new sheet and paste headings into new sheet 
Sheets.Add.Name = Range(LColAMaster).Value 
ActiveSheet.Paste 
Range("A1").Select 

'Copy data from columns A - Z 
Sheets(LMainSheet).Select 
Range(LColAMaster & ":Z" & CStr(LRow - 1)).Select 
Selection.Copy 

'Paste results 
Sheets(Range(LColAMaster).Value).Select 
Range("A2").Select 
ActiveSheet.Paste 
Range("A1").Select 

'Align All Cells 
Cells.Select 
Cells.EntireColumn.AutoFit 
Range("A2").Select 

'delete column A from all sheets before going back to main. 
    Columns("A:A").Select 
    Selection.delete Shift:=xlToLeft 


'Go back to Main sheet and continue where left off 
Sheets(LMainSheet).Select 
LColAMaster = "A" & CStr(LRow) 


End If 

Wend 

Range("A1").Select 
Application.CutCopyMode = False 

MsgBox "Backups Complete." 
Application.DisplayAlerts = True 

Application.ScreenUpdating = True 

Columns("A:A").Select 
Selection.delete Shift:=xlToLeft 


End Sub

It basically splits a worksheet using recurring data via (column A) into new worksheets. For example (as simple as poss) A list of data called "01 backup", "02 backup" etc and puts the data into a new worksheet so it is split by column A, then it deletes all column A's in the new worksheet because they are the worksheet name. That was the easy part of the macro, and it does its job perfectly. What i need to add is the ability to add a new worksheet left of the one that was made, using data from a different spreadsheet in the same column as before.

Ask me questions and stuff to get a better view.

=============================

Some examples as follows:

http://www.freewebs.com/davie1982/Book1.xls
^
The original spreadsheet

=================

http://www.freewebs.com/davie1982/Book2.xml
^
The spreadsheet after the macro is run

=================

http://www.freewebs.com/davie1982/to...added%20up.xml
^
The spreadsheet with data i want the original spreadsheet to contain as workbook names from the Invoice Number column

=================
http://www.freewebs.com/davie1982/Book2%20final.xml
^
The finalised spreadsheet (which i want in after or during the macro)

If this is any extra help i hope you can help me find a solution.

Also posted this problem at http://www.mrexcel.com/board2/viewtopic.php?t=302268

It's Excel 2003