MS Excel / VBA help thread

This was a horrible clunking bit of code that does the loop through a list of sheetnames you define and saves them in a path you define as new workbooks.

Only seems to work in Excel 2003 as it is 10 years old now

Sub ExportWorkbookSheets()
'
' Generic export Macro
' Macro recorded 12/05/03 by SMCCOLL
'
'This macro will allow individual sheets from a set of budget reports
'or any other multisheet workbook to be saved as separate files

'Macro start
Dim wsh As Worksheet 'sets a variable for later formulas,

'This section sets up the sheets to be exported by putting a list of worksheets in a new sheet in the workbook called "sheetlist"
'in column A and folder path to be saved in Column B. You can add a prefix to the filename by putting it in the path as it will
'just concatenate the sheetname

Sheets("Sheetlist").Select
FinalRow = Range("A15000").End(xlUp).Row
For i = 1 To FinalRow
    Sheets("SheetList").Select
    sheet_name = Range("A" & i).Value
    Sheet_path = Range("B" & i).Value

Set wsh = Worksheets(sheet_name)
Workbooks.Add xlWBATWorksheet 'Create a new blank excel workbook
wsh.Cells.Copy 'Copies source worksheet
ActiveSheet.Cells.Select
Application.DisplayAlerts = False
ActiveSheet.Paste 'pastes worksheet
ActiveSheet.Cells.PasteSpecial xlPasteValues 'range values it
ActiveSheet.Cells.PasteSpecial xlPasteFormats 'pastes formats
ActiveSheet.Select
With ActiveSheet.PageSetup 'formats new worksheet to print and page correctly

    .PrintTitleRows = False
    .Orientation = xlLandscape
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 1
End With 'Paste values

'this next bit saves the workbook to the path in arg1 with filename arg2

Application.ActiveWorkbook.SaveAs (Sheet_path & sheet_name)
ActiveWorkbook.Close False
'this loops the procedure so it runs through all the rows of the distribution file
'until it gets to a blank cell and stops.
Next i
Application.DisplayAlerts = True
End Sub