Macros - Export worksheets from a workbook to the current workbook location path

legendkiller420

New Member
Joined
Jul 9, 2010
Messages
22
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
Like so:
Code:
strSavePath = ThisWorkbook.Path & "\"


Since you're deleting the copied sheets, why not just MOVE them instead to skip having to delete them later?
Code:
sht.Move
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,982
Messages
5,834,680
Members
430,309
Latest member
Deepjuillet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top