PDA

View Full Version : Copy sheet to new workbook



DavidWaldo
02-15-2017, 01:39 PM
So, during past days I had several questions and recieved much help here. THANKS :). I'm finally getting to the end of my project(automatization of giving contacts to sales reps every morning.) My excel file now downloads SQL data, filters it and spreads it among the sale reps. Each Sale rep has his own sheet. But I dont want them to acces the main file, I'll rather create new excel file for each sale rep. I have sheet names stored in an array, and I would like the new workbooks to have a same name, save the new workbook and close it.
I know it should be something like this


For i = lbound(Wnames) to Ubound(Wnames)
Set NewBook = Workbooks.Add
NewBook.title = wnames(i)
NewBook.saveas = wnames(i) & ".xlxs"
Activeworkbooks.Sheets(Wnames(i)).move workbooks(Wnames(i))next i

but I cant get it working...I'l also would like to implement an safe feature, when one of the sheets doesnt exist - but i do not know how.
Thanks

Paul_Hossler
02-15-2017, 02:11 PM
1. you has ".xlxs" and it should be ".xlsx"

2. This loops through the worksheets and creates a new workbook for each worksheet, save the WB as the sheet name

3. I added a automatic delete if the WB exists

4. I added a date stamp to the WB name (just because I like to) but you can delete that if you don't want

You'll have to integrate this, changing some things, into your over all project




Option Explicit
Sub Macro1()
Dim ws As Worksheet
Dim wb1 As Workbook, wb2 As Workbook
Dim sName As String

Application.ScreenUpdating = False

Set wb1 = ThisWorkbook
For Each ws In wb1.Worksheets
ws.Copy
Set wb2 = ActiveWorkbook
sName = wb1.Path & Application.PathSeparator & ActiveSheet.Name & Format(Date, "-yyyy-mm-dd") & ".xlsx"

On Error Resume Next
Kill sName
On Error GoTo 0

wb2.SaveAs sName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
wb2.Close
Next
Application.ScreenUpdating = True
End Sub

DavidWaldo
02-16-2017, 06:15 AM
Works like a charm, what if I would like to overwrite existing files?

GTO
02-16-2017, 06:43 AM
Unless I misunderstand, Paul already included a Kill to delete any existing file.

Paul_Hossler
02-16-2017, 06:44 AM
See #3 in Post #2