Move a particular worksheet to a path

[Mr.Karr]

Hello,

Can anyone please provide a code snippet to move/save a worksheet in a specific path?
Please improve the below code.

Requirement:
Before saving/after the save process, macro should move a particular sheet named "Data" to a specific path. Say... C://

Thanks in advance,
Karthik

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
        Cancel as Boolean)
   

End Sub

 

[Deepak]

You might looking for this..

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim myPath As String, MyFileName As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

myPath = "C:\Users\dEEPAK\Desktop\"
MyFileName = "CopySheet_Data.xlsx"

Sheets("Data").Copy
    ActiveWorkbook.SaveAs myPath & MyFileName ', FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close
   
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 

[Mr.Karr]

Hi @Deepak :
Sorry, but this is not moving data sheet into the filepath. Pls advise.

THanks,
Karthik

 

[Deepak]

Hi @Deepak :
Sorry, but this is not moving data sheet into the filepath. Pls advise.

THanks,
Karthik

Pls explain the same as the posted macro will create a another wb of the said worksheet.

 

[Mr.Karr]

how about this?

Sub sb_Copy_Save_Worksheet_As_Workbook()
Dim wb As Workbook
Set wb = Workbooks.Add
ThisWorkbook.Sheets("Data").Copy Before:=wb.Sheets(1)
wb.SaveAs "C:\Users\v-kart\Desktop\test\test1.xlsx"
End Sub

But I'm struck here how to rename the moved worksheet one by one with new names. There would be multiple users with different country names.

Is there a way to save as with a string in "A1" data tab please ? Many thanks

 

[Mr.Karr]

Pls explain the same as the posted macro will create a another wb of the said worksheet.

yes, but that has to be moved to a specific folder. Sorry if i missed to include that

 

[Deepak]

how about this?

Sub sb_Copy_Save_Worksheet_As_Workbook()
Dim wb As Workbook
Set wb = Workbooks.Add
ThisWorkbook.Sheets("Data").Copy Before:=wb.Sheets(1)
wb.SaveAs "C:\Users\v-kart\Desktop\test\test1.xlsx"
End Sub

But I'm struck here how to rename the moved worksheet one by one with new names. There would be multiple users with different country names.

Is there a way to save as with a string in "A1" data tab please ? Many thanks

Change these & check..

myPath = "C:\Users\v-kart\Desktop\test\" 'change as needed

MyFileName = Sheets("Data").[A1].Value & ".xlsx"

 

[Mr.Karr]

is there a way we concatenate myfilename with date & time ?

 

[Mr.Karr]

got it.

& Format(Now, "yyyymmddHhNnSs")

thanks @Deepak