Thread: Export worksheets from a workbook to the current workbook location path

Hi All,



I have a macro to export worksheets from the workbook to the specified path( The location path is to be set in the macros).



Problem - when I moved the macro enabled workbook to another location, i usally want to change the location path in the vba editor manually. Is there a function to export the sheets to the path where the current workbook is placed?



My macro looks like




'Creates an individual workbook for each worksheet in the active workbook.
Dim wbDest As Workbook
Dim wbSource As Workbook
Dim sht As Object 'Could be chart, worksheet, Excel 4.0 macro,etc.
Dim strSavePath As String



Application.ScreenUpdating = False 'Don't show any screen movement

strSavePath = "C:\Documents and Settings\185568\Desktop\Transact Segregation - Final\" 'Change this to suit your needs -> I dont want this to be changed manually each time whenever i move this workbook

Set wbSource = ActiveWorkbook

For Each sht In wbSource.Sheets
sht.Copy
Set wbDest = ActiveWorkbook
wbDest.SaveAs strSavePath & sht.Name
wbDest.Close 'Remove this if you don't want each book closed after saving.
Next

' deletes a sheet named strSheetName in the active workbook
Application.DisplayAlerts = False
Sheets("1").Delete
Sheets("2").Delete
Sheets("3").Delete
Sheets("4").Delete
Sheets("5").Delete
Sheets("6").Delete
Sheets("7").Delete
Sheets("8").Delete
Sheets("9").Delete
Sheets("10").Delete
Application.DisplayAlerts = True


Application.ScreenUpdating = True
MsgBox "Sheets are exported"

End Sub


"



Can anyone help on this